Migrating to SqLite -Part 1

Finally I am taking my time to blog

We decided to investigate the idea for migrating our product from Sql Mobile 2005 to SqLite in order to benefit from the better performance. This was decided after investigating a large list of performance optimization tips without having the desired results.

We have 2 features identified as unacceptable from a performance point of view:
The first one involves a "discounts and prices calculation", which involves a number of unrelated db hits (no way to rewrite them as one). There are some scenarios, when this takes too long time to have the user satisfied. The second is the synchronization feature of the product. It has the ability to synchronize the mobile database with a remote (server-side )system using our proprietary mechanism. It is actually the most important feature from a marketing point of view. It involves massive insert and update operations. In most cases sync operation takes few seconds, however there are some scenarios, when the customer has a really huge amount of data and initial bulk loading of the mobile system may take few minutes. And we don't want to have the user waiting so long.

Some of the considerations against that migration was :
- the lack of database level encryption
- the lack of foreign key constraints enforcement
- the lack of strong type enforcement at a database level.
- the lack of experience with SqLite

However spending some time with SqLite in combination with the last version of the ADO .NET 2.0 SqLite Data Provider
made us optimistic about the success.

What it took us to migrate to SqLite? See here.