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

Question

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 1

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.