SQL Mobile, but Not Agile

On Saturday I sat down to spend an our creating a SQL Mobile version of a SQL Express database for use in some performance tests. Oh, how I should have known better. An hour ? What was I thinking ?

This is part of the Ontempo Store POS product I'm the development lead for at work. We want to create a Windows CE based version that runs on low-spec hardware, and we're planning on using SQL Mobile as the database engine. Since our current target device has less memory and a slower CPU than we'd originally planned, and we have no idea how SQL Mobile performs in general anyway, we're going to try some performance tests first. To do this, we need a representative SQL Mobile database.

Since our sample device hadn't arrived I figured I'd build the database on my desktop and deploy it to the device later. This is possible since SQL Mobile is cool enough that it can be installed and used on a desktop PC, and managed with the SQL Management Studio that comes as part of SQL Server and SQL Express SP1. I was able to create the database itself easily enough, although I found the engine's first limitation when I tried to manually set the maximum database size property - you can't have a database larger than four gigabytes. Fair enough though, and this should be plenty for our POS.

The next job was to add some tables to the fresh, new database full of wondrous opportunities. Now I already knew SQL Mobile doesn't support all the data types it's bigger relatives have. Most notably the varchar and char brothers are sadly missing although their cousins nvarchar and nchar are in residence. Despite this I figured the easiest thing to do was script our existing database schema (just the tables) and edit the script by hand to make it compatible. After all, search and replace should take care of a lot of the script editing.

Ah, to be young, green and stoopid (stoopid = a particularly moronic kind of stupidity).

The create table statements for the two engines are similar, but the syntax for creating primary keys and constraints is not exactly the same. In fact I still haven't worked out the correct syntax for creating a primary key with multiple columns, but since I only had a handful of tables that needed this I simply created those keys by hand via the GUI. Many of the set options (which Sql Express helpfully scripted for me) also aren't supported, along with keywords relating to file groups and other similar features. The 'with' keyword now available in 2005, for including additional column values in indexes to improve lookup times, is also not available. Eventually I got my script edited and my tables created. I was now ready to insert some data.

I tried to use the import/export wizard in SQL Management Studio but couldn't figure out how to create a connection to the database from inside the wizard. It appears the SQL Mobile provider isn't included in the list of providers when setting up a connection. I tried creating an ODBC connection, but had the same problem - no provider for SQL Mobile databases in the ODBC dialogs. At this point I decided I'd use my trusty Script Table Data application which I built recently. This application creates a script file full of insert statements (or stored procedure calls) to insert data into a table, based on an existing database.

Since SQL Mobile doesn't support stored procedures I created scripts with plain insert statements and started running them against the database using the query window in SQL Management Studio. This worked fine and the inserts were quite fast given the number of rows being inserted, and that the database was probably being resized quite often. All was fine until I got to the customers table, which is the largest table in our sample database. It contains approximately 288 thousand rows. This is perhaps overkill, although very large retailers or those with poor de-duping systems may end up with this many customer records. In any case it's a good quantity of data for testing with, if we run fine with that many rows we'll run great with less.

My problem was I couldn't open the script in SQL Management Studio, instead I got a short wait followed by an error saying the 180mb script file couldn't be opened. If I wanted to wait long enough I could open the script file in notepad, cut a group of lines out and then paste them into SQL Management Studio, execute them and repeat. Each copy/paste/execute operation took quite a while though and it was clear this wasn't the best way to go.

I decided I'd write a simple console application that opened the script file, read one line at a time and executed it against the database. I wrote the app fairly quickly and ran it, only to find I got an error saying the SqlServerCE.dll could not be loaded because it's version was mismatched or the right version could not be found. I couldn't quite figure out why I was getting this error, and since I was only trying to get some data into my database, rather than write a proper application, I decided not to mess around figuring it out. Instead I changed the application to be a SmartDevice console project so I could run it in an emulator.

Having changed the project type and selected the Pocket PC 2003 emulator I started the application in debug mode. The emulator appeared and Visual Studio started installing the compact framework and my application. While VS was busy I opened the emulator configuration and shared the folder on my desktop PC containing my SQL Mobile database with the emulated device. This meant the shared folder was treated as a storage card.

Whoops ! What I discovered when I searched the web an hour later is; SQL Mobile won't properly deal with a database hosted on 'emulated storage cards'. Instead you get an error saying SQL Mobile made an unsupported request to the host operating system.

In order to prevent myself pulling out any more of my already thinning hair I grabbed hold of the desk. You can still see the impressions of my fingers.

I moved the database from the shared folder into the "My Device" folder in the emulator. Even though this folder is part of the emulator, it's not emulated as a 'storage card' so you can use the database fine from this location. I changed my application so it pointed to the new file location.

At last, I had achieved success, if you can call it that. My application ran and started inserting rows without exceptions. I hadn't placed any timing/performance checking code in my application at this point since I was primarily concerned with testing select and update query performance. I don't know how long the application actually ran or how many rows it inserted per minute, but it was at least eight hours before it completed.

Maybe this is because it was running in the emulator (and I was busy working on other things while it ran in the background, sucking the life from my CPU). Maybe the customer table is too heavily/badly indexed, I'm not sure yet.

I doubt the problem is SQL Mobile itself though, since the database happily inserted a lot of rows at once when the queries were run from SQL Management Studio. Of course, my application is making at least one call into the database engine for each line in my script, so my code's not particularly efficient either and that could be the problem. On the other hand, it shouldn't take eight hours either.

Sigh.

At least now I have a database I can put on our actual device and test with. Here's hoping the performance tests go smoother.