Embedded .Net Database criteria

An embedded db is a db you can embed in your apps. The following list summarizes the features I expect from embedded DBs:

• Light weight, minimal amount of DLLs for redistribution.

• Fast

• Concurrent ( 2 or more threads can do stuff with the DB)

• Minimal licensing restriction

• Small (Minimal size for redistributable)

• Complete

• Support for triggers, stored procs, defaults etc…

• Support most of the SQL92 standard

• XCopy deployment

• ACID compliant

• (optional) Multiplatform (works on mono and ce edition)

Sqlite.Net - a wrapper for the SQLite database. ( blog )

Firebird .Net - a wrapper for the Firebird database which is an open source incarnation of Interbase. ( blog )

VistaDB - a commercial 100% managed database. ( blog )

Sql 2005 Compact Edition - Microsoft's embedded DB, not to be confused with SQL 2005 express. ( blog )

Embedded Databases I did not include

SharpHSQL - A port of the java HSQL database. The project on gotdotnet is very inactive. Has not been ported to .net 2.0.

SQL 2005 Express - The free version of SQL server. It technically can be embedded. But the installer is fairly large (~36 MB) and to embed it you need to include an MSI merge module. XCopy deployment is out of the question.

Microsoft Jet - Its free and installed on almost all windows computers, however it is not ACID and does not have XCopy deployment which rules it out.

Platform support

All the embedded DBs reviewed can work on mono except for SqlCe which is windows only.

License

Sqlite.Net - Public domain - there are zero licensing restrictions for private or commercial use.

Firebird .Net- Initial developers public license - modifications must be published under IDPL. Pretty permisive can be used in commercial apps.

VistaDB - Royalty free distribution - Commercial, 1 license must be purchased per developer

Sql 2005 Compact Edition - Commercial, free, can be redistributed in a commercial app provided you comply with the EULA

Sqlite and Firebird would be the winners in this category. All the DBs reviewed can be included in your apps.

Redistributable Size

Sqlite.Net - Single DLL ~550Kb

VistaDb - Single DLL ~680Kb

Sql Server Compact Edition - About 8 files ~1.6MB

Firebird .Net - About 10 files ~6MB

All the DBs reviewed are pretty easy to distribute, Sqlite and VistaDb are a little easier to distribute as they are single DLLs. All the DBs support XCopy deployment.

Performance

Test setup

• Pentium D 3Ghz
• 4Gb of RAM
• 2x160gb 7200 RPM drives in onboard Raid 0

Single Threaded

Test setup:

The underlying table: "create table Orders(Id int identity primary key, Name nvarchar(100), CustId int, OrderDate datetime, DeliveryDate datetime, Comments nvarchar(4000))"

• A single thread runs insert statements

25,000 inserts

SQLite nosync - 59400ms

SQLite nosync (in transaction) - 3250ms

SQLite - 160380ms

SQLite (in transaction) - 3290ms

VistaDb - 12300ms

VistaDb (in transaction) - 15000ms

Sql Server CE - 3280ms

Sql Server CE (in transaction)- 2900ms

Firebird - 48700ms

Firebird (in transaction) - 13800ms

Multithreaded

Test setup:

• Same table as in the previous test
• Prepopulate table with 20K rows
• Start 2 writer threads and 5 reader threads
• Writer threads insert 1000 rows each
• Reader threads perform a basic query 3000 times each
  -select count(*) from table where id between random and random + 100

Results:

Database Total time Avg read thread Time Avg write thread time

SqlCe 10800ms 10350ms 750ms

Firebird 15000ms 14950ms 7300ms

Sqlite 14900ms 1730ms 14300ms

Sqlite nosync 6900ms 1930ms 5300ms

Observations:

Due to the way Sqlite is implemented, having multiple writers can cause writers to perform in an inconsistent fashion. In the test it took the first write up to 500msecs, occasionally on a test run a single write took upwards of a second.

I was not able to test VistaDb as it started complaining that I did not have a trial license installed, which I had put in my db directory like the exception told me to.

Comments

VistaDB

• Figuring how to do stuff on VistaDb can be tricky

• You can get weird exception (Eg. Error 507: Expected expression(s) - when you have a bug in your create table command) - other providers gave a lot more data. Looks like the errors come from the parser without a translation layer. (sample of this issue)

• To create a DB from scratch you create a connection, then execute a “create database” command on the connection. The other providers have a much cleaner way of doing this.

Firebird

• Getting the equivalent of identity columns to work is pretty verbose.

Sqlite

• When creating a Sqlite connection you have the option to specify Synchronous parameter. This gets translated to a pragma command which tells Sqlite not to flush to drive after writes, this makes writes a faster but risks corrupting the database if the OS crashes (app crash should be fine). I am unclear on what happens if the OS or app crashes with SqlCe and Firebird, which I should probably test.

Summary

I performed a very limited range of tests and will try to extend the set as I go. For my next post I will try to look at complex selects.

SqlCe appears to be the king for insert performance, Sqlite appears to be the fastest for simple select performance. Sqlite can be very slow when adding data to tables outside of transactions, so if your app is predominantly writing to tables (while other threads are reading) it may not the best choice.