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?

36ms per query. Not ridiculous. Try using prepared statements, and/or reducing number of queries via `in` instead of `=`. — Blorgbeard, Jul 08 '16 at 02:59
Try `SELECT Id, Description FRO PartNumbers WHERE ID IN (... list of IDs here ...)` You are currently doing an index seek 1000 times. — Eric J., Jul 08 '16 at 03:01
@EricJ. this brings it down to 2377 ms, but this still seems slow to me... or is this normal? — Frank_Vr, Jul 08 '16 at 03:12
Just out of curiosity, what happens if you use a parameterized query? `sqlcmd.CommandText = "SELECT Description FROM PartNumbers WHERE ID = @Param";` `sqlcmd.Parameters.AddWithValue("@Param", ID);` I know that in SQL Server that can make a big difference. — Jonathan Allen, Jul 08 '16 at 03:48
@JonathanAllen that made a massive difference, from 36 seconds to 4059ms (4 seconds) thanks — Frank_Vr, Jul 08 '16 at 03:55
Wow. I was expecting it to improve it a little, but nothing like that. — Jonathan Allen, Jul 08 '16 at 04:17
My 2 cents. Create command and open connection *outside* the `for` loop. In the loop only change parameter value. — Alex Kudryashev, Jul 08 '16 at 04:33

score 3 · Accepted Answer · answered Jul 08 '16 at 04:16

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.

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

1 Answers1