about 8 years ago
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.
- Concurrent ( 2 or more threads can do stuff with the DB)
- Minimal licensing restriction
- Small (Minimal size for redistributable)
- Support for triggers, stored procs, defaults etc..
- Support most of the SQL92 standard
(optional) Multiplatform (works on mono and ce edition)
Embedded Databases I did not include
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.
All the embedded DBs reviewed can work on mono except for SqlCe which is windows only.
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.
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.
- Pentium D 3Ghz
- 4Gb of RAM
- 2x160gb 7200 RPM drives in onboard Raid 0
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
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
- 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
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
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.
- 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.
- Getting the equivalent of identity columns to work is pretty verbose.
- 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.
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.