SqlCeCommand ExecuteNonQuery performance issue
- by Michael
I've been asked to resolve an issue with a .Net/SqlServerCe application. Specifically, after repeated inserts against the db, performance becomes increasingly degraded. In one instance at ~200 rows, in another at ~1000 rows. In the latter case the code being used looks like this:
Dim cm1 As System.Data.SqlServerCe.SqlCeCommand = cn1.CreateCommand
cm1.CommandText = "INSERT INTO Table1 Values(?,?,?,?,?,?,?,?,?,?,?,?,?)"
For j = 0 To ds.Tables(0).Rows.Count - 1 'this is 3110
For i = 0 To 12
cm1.Parameters(tbl(i, 0)).Value = Vals(j,i) 'values taken from a different db
Next
cm1.ExecuteNonQuery()
Next
The specifics aren't super important (like what 'tbl' is, etc) but rather whether or not this code should be expected to handle this number of inserts, or if the crawl I'm witnessing is to be expected.
