Portable Database Choice

This is a really nice post discussing portale database choise related issues

Migrating to SqLite - Part 2

What took us to migrate to SqLite?


In general we had the following checklist:
1.Replace the classes from the System.Data.SqlCe namespace with the classes from the System.Data.SqLite namespace.
2. DataType compatibility problems
3. Foreign key related problems
4. Sql syntax compatibility problems


The migration became really easy and smooth. The major reason was the system architecture. Our system design utilizes the common idea for separating the business and data access logic. Out business logic layer operates over custom business objects (not DataSets) and invokes the data access layer for fetching and persistence operations. It has a performance impact of course, however it is a reasonable tradeoff I may say.

Checklist item #1:
We have only one place in our code to create IDataConnection implementations:
The code is like:
public IDataConnection GetConnection()
{
return new System.Data.SqlServerCe.SqlCeConnection(cnnString)
}

so we've changed it to
public IDataConnection GetConnection()
{
return System.Data.SQLite.SQLiteConnection(cnnString)
}

We use IDataCommand, IDataParameter,IDataReader and other interfaces to perform the actual database interactions. So we didn't change anything in data manipulating routines. The code was like:
using(IDataConnection cnn=DataManager.GetConnection())
{
cnn.Open()
IDataCommand cmd = cnn.GetCommand(cnn);
cmd.CommandText = "Select * from sometable";
...
IDataReader reader = cmd.ExecuteDataReader()
...


Checklist item #2
Are we going to have issues related to data types. For example very often the datetime data type may be a big pain when using the same code to access different dbms. So we did a test application where we performed some database operations identified as potentially problematic. The operations involved reading, selecting, fitlering ,joining and aggregating in SqLite db populated with a real data (from a customer). We have operations with DataReaders,ExecuteScalar calls,etc. It showed us the problematic areas and they were few indeed.

Checklist item #3
Fortunately it was not a big deal for us. Our data access layer provide some features usually available in the ORM products and dealing with business objects relations at application and database levels was one of them.

Checklist item #4
The test application helped us to identify the possible problems. In general there was no need to rewrite sql statements. We had the following issues:
- statements for the foreign constraint creation
- primary key creation statements. We used ALTER TABLE ..ADD PRIMARY KEY, which is not supported in SqLite

Note that this post only scratches the surface of the possible problems around such a migration.The issues are of course specific to the actual application migrted.
What was the benefit?
The migration process helped us to identify some design and implementation issues in our code so in result we had a better code as a whole. And we have a really better performing application as a whole. The bottleneck features are performing up to 9 times faster after migration. It worth it!

Migrating from SqlMobile 2005 to SqLite requires understanding of the tradeoffs which have to be made.
Read the SqLite documentation and make a test bed before before proceeding.

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.