SQL Compact Insert Performance

One of the performance bottlenecks of SQL Compact databases is data insertion, especially when large amounts of data must be inserted. Lots of people have struggled with this, especially when coming from SQL Server development, where everything is easier and so much faster. Using the same coding approaches as in the desktop SQL Server, SQL Compact will run very slowly when inserting large volumes of data. There are two main reasons for this:

1. On heavily indexed tables, all data changes will be slower (this means inserting, updating and deleting data). In such cases you may get better performance by dropping all indexes and constraints before adding the data and recreating them at the end. Please note that SQL Compact uses indexes internally for PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints.
2. The query processor is the worst mechanism for inserting large volumes of data. Executing a SQL INSERT comand (even if it is prepared and has command parameters created by explicitly stating data type and size) is always slower than the best alternative: using a base table cursor. When your application uses the SQL Compact Query Processor to execute a command it always pays a performance price because the QP must parse, compile and optimize the command execution path. If you can, do this work by yourself.
   

I have recently talked about this on two presentations (TechEd EMEA 2008 and DevDays09) and used a simple demo to illustrate this. The sample solution (InsertDemo, see download link below) uses four different approaches for bulk data insertion:

• Brute force - Each row is inserted by executing a SQL command created on the fly, using a string concatenation. If you look at the code you will see that I'm reusing both the SqlCeCommand and the StringBuilder objects so that I take the GC out of the performance measurement. This approach has the worst performance of the four and you can explain it through the heavy use of the query processor.
• Prepared - Instead of executing a new command every time, you can create a command with parameters and prepare it (note that the .NET CF SqlCeCommand.Prepare does not prepare the command like the OLE DB version does, it merely marks the command for preparation upon first execution - this is a pitty because the OLE DB version allows you to parse the command and check for syntax errors before executing it). Note that here we have a tighter insertion loop because we only need to set the parameter's values and execute the command.
• SqlCeResultSet - This is the best managed approach for inserting data in a table. By using the SqlCeResultSet class your code circumvents the query processor completely and inserts the data in the table by using a lower-level object: a base table cursor. Note that contrary to the OLE DB interfaces, there is no notion of a current row for insertionpurposes so you must create a SqlCeUpdatableRecord object to store the data for insertion. You can create just one instance of this object and reuse it to insert all the required rows in the table (see how I did it in the sample code).
• OLE DB - This is the fastest approach, but it's not managed. Interestingly the relative performance difference to the SqlCeResultSet seems to be constant with the inserted sample size, which seems to point to the .NET CF runtime. Although the difference seems small for small data sets, it may become significant for larger data sets.
  

Have a go with the sample project (you need both in the same solution in order for the whole demo to work). There are a few things that you might be interested in testing with this demo:

• Sample size - Inserting more rows will take longer, but does the total time grow linearly?
• PRIMARY KEY - What's the impact of adding a PRIMARY KEY to the sample table? How does performance behave when the sample size increases?
• Random keys - When using a PRIMARY KEY, what's the impact of inseting a sorted data set vs. a random data set?
• Delete - What happens when you delete large amounts of data under the previous circumstances?
  

I hope you have some fun gathering data with this demo. It uses a very simple table (one integer column) so it's not real. But you can change it to model your real table and see how it behaves under the pressure of thousands of inserts.

Enjoy!
Sample code: InsertDemo.zip, OleDbInsert.zip