I've Got The CE Blues

I've got a bad case of the CE blues, which is a bit like having a bad case of the Mondays.

You might have read my previous three posts about our attempts to find a Windows CE compatible device to use with our new POS product, Ontempo Store.

Well, over the last two days I've managed to scrape a grand total of one hour together to do some testing with the SQL Mobile database engine, running on an HP T5520. I've also spent most of one morning and a little bit of an afternoon trying to figure out how to permanently install the compact framework 2.0 and SQL Mobile on the device. So far, the results are not promising.

Starting with the install issues, it looks like we're up a brown smelly river without a large wooden thing with which to make progress. There isn't an image available from HP with the compact framework included, and we've have no luck with creating or finding a Altiris script to install/keep it installed. We've also failed to find any form of BSP for the device, so building our own CE platform isn't looking good either. The compact framework 2.0 SP1 will install the GAC to the storage card/hard disk, but the registry settings and core files are still lost on the HP unit since they are installed to RAM. While we will spend more time looking for a solution, we're not exactly brimming with enthusiasm at this point.

Then there's the database engine. I guess the good news is the limited amount of RAM in the device isn't a problem - SQL Mobile doesn't appear to do any data caching, and probably no execution plan caching either. I haven't been able to find any thing on the 'net that conclusively states this is the case, but having run the same query twice consecutively and had it take approximately 24 seconds each time, it's unlikely there's caching involved.

As you might have guessed by my last statement, the bad news is the performance. My first test had the database loaded in RAM, since the device wouldn't detect my USB hard disk, I didn't have a USB thumb and there wasn't enough space left on the inbuilt (only) 64mb compact flash chip. The results were quite astounding, every single query I ran on my 277000+ row table was well sub-second, except for;

SELECT COUNT(*) FROM Customer

which took 4 seconds - not great, but a figure I could live with it. Since keeping the database in RAM isn't an option for us long term I eventually found a couple of USB 2.0 thumbs. I tried the database on each of them, and the database engine went from running like a cheetah the morning after a hot beef vindaloo to running like a gold fish in the desert.

Starting with some simple queries, I tried;

SELECT * FROM Customer WHERE Surname='willmot'

this still sub-second, with an index on Surname, and returning three rows.

SELECT * FROM Customer WHERE Postcode='1008' AND Surname='willmot'

Went from sub-second to over a minute without an index on the postcode field, and returning only one row. After adding an index to postcode (which appeared to crash CE the first time I tried), the time went back to sub-second. This is pretty bad, but possibly not yet fatal. However I then tried;

SELECT * FROM Customer WHERE Surname like 'will%'

This took anywhere from 1-2 minutes depending on how bored I got waiting and what else I tried to do with the unit while the query was running - if I was able to get it to do anything. I got several results, and I don't remember any of them being under a minute even when I completely left the device alone until it finished. I checked the query plan SQL Mobile was using, and it was indeed using the index on the Surname column so it didn't appear the query optimizer was at fault.

I figured maybe the IO performance of the USB thumb sucked, so I tried running the query again immediately after the first execution and got almost exactly the same result. It appears there is no data caching occurring in the data engine. On the web I have found one blog comment asking whether or not SQL Mobile performs data caching (no reply to the comment) and one PowerPoint presentation that suggested there wasn't any caching, but wasn't totally explicit.

These same, simple, queries worked fine when I had the database in RAM so I can only presume the speed difference is in the IO to the USB thumbs, but since a USB hard disk isn't likely to be 100+ times faster I'm not filled with hope about using this database engine for our product.

In any case, the more I tested the worse it got. I couldn't get a statement using the TOP keyword to work at all, presumably SQL Mobile doesn't support this ? Any query I ran on an indexed field without using the like keyword was pretty fast, any query that used like or an unindexed field felt like watching a full season of cricket all at once, without the one-day games. I was so distressed by the performance of these queries (and ran out of time anyway) I never even got around to trying >, <, or, in, sum, group by, order by or various other functions/operators/keywords. Since the CE version isn't on our current critical path for the POS, I'm forced back into doing some real work so I won't get much more time to play with this for a while. None of my tests were very scientific, and I really should get a USB hard-drive working to see how much faster that is than the USB thumbs. A bigger internal compact flash chip where we can store the database might also be worth a look. I could try the beta of SQL Everywhere, the next version of SQL Mobile. I'm not sure it's worth it though, since I haven't seen any comments saying it significantly faster or supports data caching so I don't expect it to help.

Maybe we'll see if we can get somwhere with embedded Firebird for CE although this doesn't look likely, or maybe we'll write our own database engine.

Our other project, a computerised trolley to assist staff picking stock in a retail warehouse, isn't dependent on SQL Mobile, but does require a similar spec CE device with the compact framework installed. The T5520 would be fine for this, if we could keep the compact framework installed between restarts, power offs etc. At this point, we're still looking for a solution to the install problem or a new device that fits the bill.