SqlCeCommand ExecuteNonQuery performance issue

Question

UPDATE: I've modified the code to drop the indexes before the inserting, but it makes not difference.

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.

Answer 1

I would take a look at the indexes on Table1. If it's heavily indexed, queries could get more costly as pages in the indexes fill up. Make sure you aren't overly indexed, and that your indexes are properly set up.

Also check around your query to make sure you're not leaking memory somewhere by holding on to a reference. In my experience with CE, you should dispose every IDisposable object you get as soon as posible, as you don't have the luxury of gigs and gigs of RAM like you do on modern PCs... Memory can become precious fast.

Answer 2

Are the inserts being performed within a single transaction? Or, what is the isolation_level used by the connection? Extensive locking and blocking might slow things down. Otherwise, the query looks simple enough.