Words on my addictions: .NET, Philosophy, Music

13 June 2007

Hunting for embedded database engine Part 1

Filed under: C# — stefanprodan @ 1:29 pm

This year I was developing a windows application that needed data storage, because the data that needed to be saved was not huge I’ve chose to use the binary serialization but now the product has a new target and the data that needs to be saved will take up too much resources, the serialization/deserialization takes too much time and RAM.  So I’ve been searching in the last 2 weeks for an embedded SQL engine that has a small footprint and an ADO.NET provider. After 1 day I came out with a list: SQL CE 2005, VistaDB, SQLite, Firebird.

From the features list VistaDB is by far the best, VistaDB has SQL Server 2005 compatible data types, T-SQL syntax, C# stored procedures, BLOB support, table level and row level locking, encryption and it comes with it’s own GUI manager. So I’ve decide to run some tests, SQLite has a lot of GUI managers and the tests were easy to do. Well after inserting 300.000 records in the VistaDB and SQLite the select query blows away VistaDB…

Table structure:

CREATE TABLE t1(
 [id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
 [Number] [nvarchar](50) NOT NULL,
 [Name] [nvarchar](255) NOT NULL,
 [Code] [smallint] NULL,
 [VarName] [nvarchar](255) NULL
 );

Select tests SQLite vs VistaDB:

select * from t1 where number like ‘D0%’ and id<100000

SQLite: 200 ms

VistaDB: 10.000 ms

select * from t1 where number like ‘D%’ and code >0 and varname like ‘%)%’

SQLite: 300 ms

VistaDB: 58.000 ms

After this 2 queries I’ve decided to drop VistaDB from the list, VistaDB from the features list looks great I think any C# developer will be attracted to it but I need SPEED. In part 2 I’ll post the tests made on SQL CE 2005 vs SQLite vs Firebird.  

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: