Tuesday, July 20, 2010

WP7 SqliteClient Preview

WP7 developer devices are already out in the US and I still hope I will qualify for one here in Italy. I would really love to test one. Brandon PLEASE don't forget me :)
As my proposal was a better port of the Sqlite engine I did some optimizing on the engine and also implemented/adapted a SqliteClient that is easier to use than normal sqlite3 commands.
At the "engine" part I use the journal file persistent this way the engine doesn't delete and create the journal at each command. I've also eliminated SQLITE_DEBUG from conditional compilation symbols spares some temporary file access.
For the client at the beginning I've wanted to compile the System.Data and Sqlite class from Monotouch, but after looking a little bit at the source code I've realized that there are tons of things that doesn't make sense on WP7. I wanted to keep it simple and searching I've found the SqliteClient.cs written by Frank Krueger for Monotouch. I've adapted the class to run with our library and got a pretty nice result.
I've also improved the test project and now you are able to set how many rows will be inserted, select the inserted rows and see the elapsed times for insert/select commands. For the insert I've tested two methods: the first with prepare, step, finalize and the second one with sqlite3_exec (which should be the same) and the insert time are more or less the same.

Anyway it's only a preview as it was not intensively tested and because it can be improved. If some of you find ways to optimize the library please let me know and I will post it for everybody. Also let me know what you think.

Here you have the link to the source code



  1. I have followed your (and others) work on sqlite on WP7 for a few weeks. I am sure that I am not alone.

    With 4750 rows:
    Generate Data - 43s
    Generate Data Ex - 45s

    Working with sqlite on Android, I have noticed that inserting rows one by one is really slow. Common knowledge seems to be to wrap multiple inserts in a transaction.

    With 4750 rows, wrapped in a transaction:
    Generate Data - 1.1s.
    Generate Data Ex - 1.2s.

    This is a great accomplishment!

  2. Can you send me your test project? How about select times compared to Android?

  3. I've implemented the transaction part and it improves a lot the insert time. Hope I will post it later today.
    For 1950 rows without transaction it takes 20.540 sec., using transaction it takes 0.358 sec.

  4. I've posted the updated version with transaction support

  5. I don't know how these results compares to Android sqlite. Unfortunately I can't check with my own projects for a while (will be away from work the coming weeks).

    I noticed another thing in the test. Ideally, the insert statement would only need to be prepared once, and then reused, just binding new parameters to it on each insert (instead of preparing a new statement on each ExecuteNonQuery). Don't know if it will shave off a few milliseconds. Worth a try perhaps?

  6. Nice work! I added some code to be able to calculate geo distances. Post and code can be found http://cevdw.wordpress.com/2010/08/17/calculate-poi-distance-from-current-location-in-wp7/

  7. This comment has been removed by a blog administrator.

  8. This comment has been removed by a blog administrator.