The Databases
Firebird 1.5.2 w/ ADO.NET 2.0 Provider Final
This is a great database engine, no doubt. It comes in several flavors, but for our purposes we're only concerned with the embedded edition. It has row-level locking (only one process may have the database open at a time), supports stored procedures, and is generally designed to be a robust embedded database engine. Although the ADO.NET provider does run on the Compact Framework, it only supports a client-server model where the main database is located on a desktop. If you need a full blown database engine on the mobile platform, Firebird will not do the trick.
VistaDb 2.1.7 w/ ADO.NET 2.0 Provider
VistaDb is a commercial embedded product having similar limitations as Firebird. The provider runs on the Compact Framework but only to connect to a VistaDB server. It does have row-level locking, and lots of customized classes for bulk loading data and other Vista-specific operations. Vista's ADO.NET 2.0 provider is lacking, however. It has no DbProviderFactories support, no ConnectionStringBuilder, no GetSchema() on the connection object, and no VS2005 data designer extensibility features.
MS Jet 4.0 w/ OLEDB ADO.NET 2.0 Provider
Lots of people are still using this engine, and despite its limitations, it's still a great engine. It does have stored proc support (sortof) and row-level locking, but it is not ACID compliant, databases are limited to 2gb. When finely tuned and in the hands of an expert, Jet can absolutely fly.
SQLite 3.3.7 w/ ADO.NET 2.0 Provider 1.0.32.0
This is the smallest of all the engines, and arguably has the least amount of features. It doesn't support row-level or even table-level locking (all locks are on the file itself), but it does support multiple processes accessing the database simultaneously. It doesn't support stored procedures. It does however have triggers, allows for user-defined functions, aggregate functions and collating sequences, and is ACID compliant. It also implements most of SQL92 and is fully mobile. Databases can be copied freely across *nix and Windows, all CPU types and byte orders, and supports mobile environments.
Sql Server 2005 Express Edition
This is the gold standard of database engines, but I hesitate to call it "embedded" as the redistributable weighs in at 53mb. It has some reasonable licensing restrictions. It has every sql option you could imagine, but database sizes are limited to 4gb on Express. If you're reading this far you've probably already discarded it as a potential engine for your lightweight database needs. Sql Server's query optimizer is phenomenal however, and trounces everyone with its ability to chew up complex queries.
Sql Everywhere Edition CTP
Microsoft opened up it's Sql Mobile database to the desktop. It is currently in CTP form but is looking promising. It has row level locking, but I believe only one process can have the database open at a time. It has no stored procedures, and I believe it also lacks trigger support. Databases are limited to 4gb
The Test Machine
Windows XP Pro x64 Edition w/ all patches and hotfixes applied
ASUS A8N Motherboard
AMD Athlon X2 4400+ dual core CPU
2gb DDR SDRAM
2x Western Digital 10k RPM 72gb SATA drives in RAID0 config
The Data
The following 4 tables were created in each environment. Variations were made to allow for each individual engine's SQL, but in all cases, each table had a primary key defined. Aside from the Invoice table, all the tables also had a foreign key which was not explicitly declared, but on which an index was created.
CREATE TABLE [Invoice] ([PKeyInvoice] INTEGER PRIMARY KEY, [InvoiceNum] VARCHAR(15), [InvoiceDate] DATETIME, [NetCharge] CURRENCY, [ChargeCurrency] VARCHAR(3))
CREATE TABLE [Shipment] ([PKeyShipment] INTEGER PRIMARY KEY, [InvoiceKey] INTEGER, [TrackingId] VARCHAR(16), [ShipDate] DATETIME, [NetCharge] CURRENCY, [ChargeCurrency] VARCHAR(3));
CREATE INDEX ix_invoice ON [Shipment] ([InvoiceKey])
CREATE TABLE [Packages] ([PKeyPackages] INTEGER PRIMARY KEY, [ShipmentKey] INTEGER, [Pieces] INTEGER, [WeightUnits] CHAR, [Weight] REAL, [DimLength] INTEGER, [DimWidth] INTEGER, [DimHeight] INTEGER, [DimUnits] CHAR);
CREATE INDEX ix_packages_shipment ON [Packages] ([ShipmentKey])
CREATE TABLE [Charges] ([PKeyCharges] INTEGER PRIMARY KEY, [ShipmentKey] INTEGER, [Code] VARCHAR(3), [Amount] CURRENCY, [ChargeCurrency] VARCHAR(3));
CREATE INDEX ix_charges_shipment ON [Charges] ([ShipmentKey])
For each table, a comma-delimited file was constructed with a number of rows: 106 invoices, 57579 shipments, 57589 packages, and 182176 charges for a total of 297450 rows.
Importing the Data
An optimized method was chosen for each database engine to import the rows into the tables, and all inserts were performed in a transaction:
- For SQLite, a parameterized insert using standard SQL statements.
- For Firebird, the same as SQLite, using standard parameterized insert statements.
- For Access, we take advantage of the Jet engine's ability to import CSV files directly and construct special INSERT statements that load the data from CSV files.
- For VistaDB, we use its DDA language and call ImportFromFile() on the VistaDBTable objects.
- For Sql Server Express, I used a DataTable and SqlBulkCopy.
- For Sql Everywhere Edition, an updatable SqlCeResultset was used.
The Tests
For each SELECT test, a command was created, ExecuteReader() was called with no parameters, and the Read() method of the DataReader was called until no more rows were returned. Inside the read loop, a single column of the row was read.
Notes, Caveats, and Exceptions
- VistaDB
- The VistaDB scores are blanked out because their license agreement forbids posting benchmarks of their engine. If you really want to see the exact numbers for VistaDB, download the source code for the tests and get a trial of VistaDB.
- I am not sure why it faulted on the insert 1 row test. I had the database open for exclusive access, and the import worked fine, so I'm baffled.
- Firebird
- When selecting columns, Firebird's performance degrades significantly the more columns you return in your resultset. I deliberately returned few columns in most of the tests because of this. I suspect it may be a problem in the ADO.NET provider, but there's no way for me to be certain.
- Sql Server 2005 Express
- The scores are blanked out because their license agreement forbids posting benchmarks of the engine. You'll have to download the attached source and run it yourself to find out the numbers. However, since the charts are organized from best to worst, you can infer the numbers from there.
- Sql Everywhere Edition CTP
- The scores are blanked out because their license agreement forbids posting benchmarks of the engine. You'll have to download the attached source and run it yourself to find out the numbers. However, since the charts are organized from best to worst, you can infer the numbers from there.
Each chart is ordered from best to worst:
| BULK INSERT 297450 rows (milliseconds) |
| SQLite x64 |
|
4641 |
| SQLite |
|
4750 |
| Jet |
|
7719 |
| Sql Everywhere |
|
|
| Sql Server |
|
|
| Firebird |
|
39094 |
| VistaDB* |
|
|
| 0 |
Scale |
72000 |
| * VistaDB completed, but failed to import the YYYYMMDD formatted dates |
| Database size (kb) |
| SQLite |
|
14208 |
| Sql Everywhere |
|
17652 |
| Sql Server* |
|
17728 |
| Firebird |
|
28420 |
| VistaDB |
|
45872 |
| Jet |
|
91776 |
| 0 |
Scale |
92000 |
| * Sql Server's logfile was 219264kb |
Join Test #1 (milliseconds)
SELECT * FROM Invoice A
INNER JOIN Shipment B
ON A.PKeyInvoice = B.InvoiceKey |
| SQLite |
|
156 |
| SQLite x64 |
|
172 |
| Sql Server |
|
|
| Jet |
|
453 |
| Sql Everywhere |
|
|
| VistaDB |
|
|
| Firebird |
|
11796 |
| 0 |
Scale |
12000 |
Join Test #2 (milliseconds)
SELECT B.PKeyShipment, SUM(C.Amount)
FROM Invoice A
INNER JOIN Shipment B ON A.PKeyInvoice = B.InvoiceKey
INNER JOIN Charges C ON B.PKeyShipment = C.ShipmentKey
GROUP BY B.PKeyShipment
ORDER BY B.PKeyShipment
|
| Sql Server |
|
|
| SQLite x64 |
|
734 |
| SQLite |
|
782 |
| Jet |
|
2047 |
| Sql Everywhere |
|
|
| Firebird |
|
5032 |
| VistaDB |
|
|
| 0 |
Scale |
438900 |
INSERT 1 Row Test (implicit transaction) (milliseconds)
INSERT INTO Invoice
(PKeyInvoice, InvoiceNum, InvoiceDate, NetCharge, ChargeCurrency)
SELECT MAX(PKeyInvoice) + 1, '123456789', '2005-01-01', 100.5, 'USD'
FROM Invoice |
| Sql Server |
|
|
| Jet |
|
0 |
| Sql Everywhere |
|
|
| SQLite x64 |
|
0 |
| SQLite |
|
0 |
| VistaDB* |
Unhandled Exception |
|
| Firebird |
|
0 |
| 0 |
Scale |
0 |
* Unhandled Exception: VistaDB.VistaDBException:
Error code: 158 Incompatible user mode Invoice
Error code: 590 Unable to open table: Invoice
Error code: 515 Table is not opened: Invoice
Error code: 610 OpenSQL:
Error:515 Table is not opened: Invoice |
|
Scan Test #1 (milliseconds)
SELECT A.TrackingId, B.Weight
FROM Shipment A
INNER JOIN Packages B ON A.PKeyShipment = B.ShipmentKey
WHERE B.Weight > 10 |
| Sql Server |
|
|
| SQLite x64 |
|
203 |
| SQLite |
|
203 |
| Sql Everywhere |
|
|
| Jet |
|
469 |
| Firebird |
|
593 |
| VistaDB |
|
|
| 0 |
Scale |
2300 |
Scan Test #2 (milliseconds)
SELECT A.InvoiceNum, B.TrackingId
FROM Invoice A
INNER JOIN Shipment B ON A.PKeyInvoice = B.InvoiceKey
WHERE B.TrackingId LIKE '%55%' |
| SQLite x64 |
|
94 |
| SQLite |
|
109 |
| Jet |
|
171 |
| Firebird |
|
204 |
| Sql Server |
|
|
| Sql Everywhere |
|
|
| VistaDB |
|
|
| 0 |
Scale |
2400 |
Scan Test #3 (milliseconds)
SELECT InvoiceNum
FROM Invoice
WHERE PKeyInvoice IN (SELECT InvoiceKey FROM Shipment WHERE NetCharge > 100) |
| SQLite x64 |
|
31 |
| SQLite |
|
31 |
| Jet |
|
47 |
| Firebird |
|
62 |
| Sql Server |
|
|
| Sql Everywhere |
|
|
| VistaDB |
|
|
| 0 |
Scale |
1800 |