Dot Net Perls

Problem

Compare the performance of SQLite and SQL Server Compact Edition, also known as SQLCE. Even after several optimization attempts, the version of my program that used SQL Server CE by Microsoft ran slower than I would like. We need to choose between SQLCE and SQLite based on performance metrics and find the most efficient embedded database.

Solution

I switched from SQLCE to SQLite for a substantial performance advantage. Let me elaborate on some of the changes in the performance characteristics of my program, both before and after the change. The following graph and table compares how my program evolved, at first using SQLCE and later switching to SQLite.

What are some advantages of SQLite?

One huge bonus of SQLite is that it is compatible and there are versions for Mac OS X and Linux, unlike SQL Server Compact Edition. So a Mac user could look at the databases my programs use and change or export the data. I have to say that I doubt Mac users will have C# .NET in the same way Windows users do any time soon.

What is the easiest way to use SQLite in C#?

I have explored different techniques of database usage with SQLite, and the best method I have found is a open-source ADO.NET provider. It combines C# and SQLite and is simply called System.Data.SQLite. I highly recommend it if you need a client-side database. That project also includes FTS3 full-text search in the assembly.

Conclusion

SQLite is far more efficient than Microsoft's SQLCE. Database changes can make a program more efficient and shouldn't be dismissed. SQLite is a marvelous database and Richard Hipp is awesome for putting it in the public domain. SQLite generally uses less memory and is faster than SQLCE, and using System.Data.SQLite is best for C# developments.

Ads by The Lounge