(Update: I have been noticing a lot of google traffic, please check out the follow up article for a better picture.

In all the mobile applications I work on, the performance of SQL CE 2 is a big source of pain. I've been hearing for some time that the replacement (SQL Mobile) is going to pay more attention to performance. Specifically, query performance on SQL CE 2 isn't that bad, but INSERT performance is abysmal and UPDATE performance not far behind. Yesterday I had a minute to look into some performance testing with Beta 2.

The first thing I discovered was that you cannot just connect a device to visual studio to quickly deploy. VS 2005 reports "The current version of active sync is not supported. Download the latest from www.microsoft.com." A quick groups search indicated that a Deploy from visual studio will require Active Sync 4, not yet released. So, I dug through the Visual Studio 8 directory to find the 3 cab files I needed to install Compact Framework 2 and Sql Mobile. I installed these, noticing that it appears you can run CF 1 and SQLCE2 along side CF 2 and Sql Mobile on the device. I was a little worried that I didn't have a way of knowing which version of the CF was loaded when I ran my command line tests. Right now I still need CF 1 on the device, but I may uninstall CF 1 later and redo one of my performance tests later to be sure I wasn't running uder CF 1 and just loading the Sql Mobile assemblies. I created a Smart Device CAB project and installed my performance test. On my device. I did a visual studio deploy from VS 2003 to a different directory to obtain CF 1 performace numbers.

My fist testing involved doing 4 different database operations, stolen from one of my mobile apps involving car data.

1. Insert 5000 records in a simple table representing colors.
2. Insert 10,000 records in a table involving VINs.
3. Do an Update of 1,000 randomly chosen color records.
4. Do a like% query 100 times against the table the VIN table.

In this test, the database connections are opened inside the loops, and the commands/command paramters are created inside the loops. No transactions or prepared commands can be used under this method. Obviously this is a horrible practice, but the goal was to exagerate the cost of opening connections to the different databases and such. Here are the numbers:

These tests were done on a Windows CE.Net 4.2 Device, 400mhz xScale processor, with 128 megs of ram. The data store is on a CF card. I should note that I had thought there would be an xScale optimized build of the compact framework, but I found none so I installed the ARMV4 build.

The first observation I made is that for pure DB operations, Release builds were slightly slower on average than debug builds. This was alarming, previous tests I've done have shown that a Release build is 5-7% faster over all for a Winforms app doing web services etc. The second, very disheartening observation, is that Inserts and Updates under Compact Framework 2 are twice as slow as the same code running against CF 1.0 Service Pack 3. The selects are twice as fast on the new platform, but selects weren't my complaint to begin with.

Note that I did not time every piece of each operation yet, and obviously this code is purposefully slow. Since Sql Mobile supports multiple concurrent connections, it may be that the longer times are related to an increased cost of getting a connection and the implicit transactions. The next test will involve preparing commands, re-using the same connection object, and testing UPDATE statements against a large (100,000 - 1,000,000 records) Sql Mobile table. Stay tuned.