Frank_Vr Frank_Vr - 1 year ago 66 Question

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

I have a table

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 (
) and loop through each ID to fetch the description from the table:

Dim Stop_Watch As New Stopwatch

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

Dim Result_Time As Long = Stop_Watch.ElapsedMilliseconds

My result is:

  • Result_Time = 36283 ms (36 seconds)

Is this normal?

Answer Source

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.