Wednesday, March 04, 2009

Seek and you shall find

I recently found myself involved in a prototype Windows Mobile mapping application using the Virtual Earth tile system. Downloading the 256 x 256 bitmaps to a mobile device through a GPRS connection is slow and a bit painful, so I thought about creating an external cache mechanism. This would temporarily store the bitmaps for future use and, instead of writing the bitmaps to the file system I thought about using SQL Compact. There are a few advantages for using a database instead of the file system: you store all the bitmaps in a single file and you get the added benefit of easier maintenance (especially when deleting outdated bitmaps to clear the cache). So I looked back to the OLE DB library I have been working with for some help but I found a major whole (yes, there are more, I know!): there was no support for IRowsetIndex::Seek. Why do I need to use this method when I can equally retrieve individual records using a plain SELECT command? The answer is speed. When you go through the SQL Compact Query Processor your application pays a performance penalty that may be avoided (with varying degrees of effort). In this case I wanted to retrieve a single row from a table using an index (no fancy JOIN or GROUP BY clauses) so using a base table is very appropriate, performance-wise.

The idea is very simple: open a base table cursor and specify the index to use; seek using the required key values and retrieve the row data (if it's there). As I said, you can do this using SQL but there is performance penalty that I did not want to pay, especially when fast painting is required. My major problem was that there was no support for this in the previous version of the OLD DB library, so I had to implement it.

Implementing the IRowsetIndex::Seek supporting code implies adding an extra column binding code (similar to the one I wrote for the columns). In fact, you need an extra IAccessor object to bind the key columns as well as all the other supporting data structures. After a few hours worth of work, I managed to develop a working version of the code that I'm publishing here. If you look at the CRowset class implementation you will see how its complexity increased to a point where some new abstractions are just crying out to be created...

When using this code, remember to:
  • Add the DBPROP_IRowsetIndex property (set to true) to the rowset property set;
  • Call CRowset::SetKeyValue for each one of the index key values: ordinals start at one;
  • To seek to the requested row, call CRowset::Seek specifying the number of valid key columns (the number of valid values set with CRowset::SetKeyValue).
If you get an S_OK as return value, the row was found and the data loaded into the row and ready to use.

Sample code:

EDIT: The link has been fixed.


gabe said...

native work with sql compact would be worse off if not for your thoughts and samples. I really appreciate your work here.

(and the link looks broken)

/ gabe

João Paulo Figueira said...

Thanks for the heads-up: the link is now fixed.

vincent said...

and what about sqlite

João Paulo Figueira said...

Looks like all other mobile platforms are using it (Android, iPhone and Symbian). From what I've read, SQLite does not have a low-level interface for accessing the tables directly, or am I wrong?

elbibiq said...

hi there!

after roaming the Internet for one whole day you seem to be one of the men to ask when it comes to SQL CE/Mobile/Compact/whatever they call it these days.

my question is rather simple: can I use the OLEDB Provider under .NET CF 2.0? of course I can use the ADO.NET provider with all its SQLCEXXXX classes, but can I use the OLEDB one?

the thing I am after it's a bit esoteric: I want to run old ADO commands/queries under the SQL Mobile (i.e. SQL CE 3.0), such as SHAPE and APPEND and so on. these I cannot issue on the ADO.NET SQL Mobile provider so maybe I can use the OLEDB provider but it must be under .NET and I could not find any method to make it work. I tried tlbimp on sqlceoledb30.dll but this dll although seemingly COM doesn't contain any type library.

needless to say the resources on such a topci are nonexistent on the Internet so if you could give me a hand I'd owe you a beer! :)