Evaluating lightweight database systems

17 June 2007

#sqlite#simplednsplus

I spent the past few days evaluating different embedded database system for possible use in the upcoming Simple DNS Plus v. 5.

Simple DNS Plus v. 5 will be .NET based, so my first requirement was that the database system had a .NET API or data provider.
Ideally it should run on both 32 and 64 bit Windows versions and under MONO on non-Windows systems (Linux, MacOS, Solaris, etc.).
And of course it should be fast and small as possible.

I looked at the following options:

1) Microsoft SQL Server Compact Edition (MS SQL CE)
(http://www.microsoft.com/sql/editions/compact/default.mspx)

At first this looked like the obvious choice since it is practically part of the Visual Studio toolbox (or will be in Visual Studio "Orcas").
However, at this time there is no 64 bit version, which would also prevent Simple DNS Plus from getting certified for Windows Vista (see http://channel9.msdn.com/ShowPost.aspx?PostID=312747).
And from what little information I can find, Microsoft has not committed to a 64 bit version in the "Orcas" timeframe either
(see http://blogs.msdn.com/stevelasker/archive/2007/01/16/sql-server-compact-edition-3-1-released.aspx#1753365)

Of course being win32 code it probably doesn’t run on non-Windows systems.

2) VistaDB
(http://www.vistadb.net)

This database system is a 100% .NET managed code DLL which can be linked directly into another .NET assembly. So this is truly an "embedded" database system.
The same code runs on both 32 and 64 bit – and even on non-Windows systems (under MONO).
The downside is that this is rather slow compared to the other database systems (according to my own simple speed tests), and the documentation is not great.

3) SQLite
(http://www.sqlite.org and http://sqlite.phxsoftware.com)

This was significantly faster than both MS SQL CE and VistaDB.

For .NET development, this is available as a hybrid DLL containing both the native code SQLite engine and the .NET provider.
Because of the included native code, there are separate DLLs for win32, x64, Itanium, and .NET compact which makes the total distribution size significantly larger than the other database engines.

A potential issue is that it has no type safety. Anything can be stored in any column.

I imagine the hybrid DLLs cannot be used on non-Windows systems, but the SQLite engine itself is available for many operating systems and as C source code, so there is probably some way around that…

4) Codebase
(http://www.codebase.com)

About 10 years ago I used this database engine to build a larger invoicing system with VB3 16 bit.
It was VERY fast back then and still is today.
It uses xBase data files which explains the speed (very simple and efficient file layout), but also why the data file becomes very large (data fields are padded to maximum length).
It has a .NET API but is itself native code. I am not sure if it works under MONO.

5) Firebird
(http://www.firebirdsql.org)

I didn’t actually get around to testing this, but wanted to mention it because it looks like another good option.
It has a .NET data provider but is itself native code. I am not sure if it works under MONO.

And the winner is… none!

Basically they were all just overkill for what I needed.
Had I chosen one, it would have been either VistaDB because it best fit my requirements or SQLite because it was very fast.

Instead of a database I ended up using a "homemade" highly optimized proprietary file structure.

A quick comparison of inserting 150.000 random records (representing 150.000 DNS zones):

And the resulting "homemade" file was significantly smaller than any of the database files.

Read more about the "homemade" file structure here...