Frank_Vr Frank_Vr - 5 months ago 16
Vb.net Question

SQL Server CE slow at reading (36 seconds for 1000 rows?)

I have a table

PartNumbers
with 10 columns with mixed data types and just over 100,000 rows.

Here is my test:

I've put an index on the ID column:

CREATE UNIQUE INDEX IndexName ON [PartNumbers] (ID)


I get a list of 1000 randomly selected ID's (
Random_ID_List
) and loop through each ID to fetch the description from the table:

Dim Stop_Watch As New Stopwatch
Stop_Watch.Start()

Dim Results As New List(Of String)
LocalDB.SQLCe_Connection.Open()
For Each ID As Integer In Random_ID_List
Dim sqlcmd = LocalDB.SQLCe_Connection.CreateCommand()
sqlcmd.CommandText = "SELECT Description FROM PartNumbers WHERE ID = " & ID
Results.Add(sqlcmd.ExecuteScalar)
Next
LocalDB.SQLCe_Connection.Close()

Stop_Watch.Stop()
Dim Result_Time As Long = Stop_Watch.ElapsedMilliseconds


My result is:


  • Result_Time = 36283 ms (36 seconds)



Is this normal?

Answer

Use a parameterized query.

sqlcmd.CommandText = "SELECT Description FROM PartNumbers WHERE ID = @Param";
sqlcmd.Parameters.AddWithValue("@Param", ID); 

I don't know the details for this database, but for other databases you can spend more time parsing the SQL and generating the execution plan than actually running the query.