SQL Compact Performance

Last November I delivered a presentation at TechED EMEA 2008 about SQL Compact performance tuning. I had some requests for the presentation source code but could not find the time to post the sources online and, because this was a managed code presentation it did not feel right to post the code on my native code blog. After receiving one more request for the presentation materials I thought that this a good time to start writing about my presentation's topic and share the presentation code.

Like every aspect of software development for Windows Mobile or Windows CE, performance is one of the top priorities for developers. Devices are inherently limited in memory, storage and processing power. This also limits the amount of horsepower you can house in such a device and SQL Compact is a great example of that: it's a small-sized relational database engine with a simple but effective security model that also runs on desktop computers and serves multiple clients. Due to its small size, SQL Compact does not include all the bells and whistles that his desktop cousin has to offer, so you should expect to do some of the optimization work that SQL Server does behind the scenes. You just cannot expect to bring your desktop development experience to a mobile device and expect it to perform in the same way. You have to adopt a device development mindset: prepare to work more.

Here are two quick examples of how things work so differently on SQL Compact:

• There is no connection pooling mechanism so it is considered a bad practice (performance-wise) to quickly create, open and close a database connection. In the worst-case scenario you will be loading and unloading the database engine everytime you do this!
• Don't use the INSERT command to insert data on a table. Even if you use command parameters on an INSERT command, you will still get less than optimal performance. To get the very best performance when inserting data, you need to use a base table cursor which is exposed throught the SqlCeResultSet class.
  

Interested? Stay tuned for more in the next post.