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.
