Embedded Database engines benchmark-SQLite 3.6.3 vs ms SQL ce 3.5 vs Firebird 2

ArticleDirectory

• How did you implement the test? You ask
• Conclusion

 

Http://engin.bzzzt.biz/embeddeddb.shtml

 

 

Update 20-oct-2008:

Instead of creating a dbcommand for each iteration, I started to use one dbcommand instance, as any normal person wocould do in the first place. and it chagned The resultS quite a bit. it looks like object initiation for Firebird is expensive. when I got the initiation Out Of The Way Firebird excelled. I was doing some tests about embedded database engines. here's what I 've ve got.
How did you implement the test? You ask

Test program starts a transaction, does a bunch of inserts and commits. it measures all the time spent on insert queries and the commit that applies the changes excluding the time spent on opening the database file. there's no index columns in any of the tables.

This test is made for 50,250,500,100 0, 5000,100 00, 20000 and 50000 inserts.

You can view the Code with below link.

[View] Source

Here's the actual results with default configuration for each database engine.

Testing for 50 iterations
Testing SQLite... 499 Ms inserts/sec
Testing Firebird... 1136 Ms inserts/sec
Testing ms SQL Ce 3. 5... 23,0046 Ms 2173 inserts/sec

Testing for 250 iterations
Testing SQLite... 2524 Ms inserts/sec
Testing Firebird... 27772 Ms inserts/sec
Testing ms SQL Ce 3. 5... 33,0066 Ms 7574 inserts/sec

Testing for 500 iterations
Testing SQLite... 3875 Ms inserts/sec
Testing Firebird... 35707 Ms inserts/sec
Testing ms SQL Ce 3. 5... 7351 Ms inserts/sec

Testing for 1000 iterations
Testing SQLite... 120,024 Ms 8331 inserts/sec
Testing Firebird... 38453 Ms inserts/sec
Testing ms SQL ce 135,027 .. 7405 Ms inserts/sec

Testing for 5000 iterations
Testing SQLite... 145,029 Ms 34475 inserts/sec
Testing Firebird... 165,033 Ms 30296 inserts/sec
Testing ms SQL ce 8303... 602,1204 Ms inserts/sec

Testing for 10000 iterations
Testing SQLite... Ms 52072 inserts/sec
Testing Firebird... 47837 Ms inserts/sec
Testing ms SQL ce 8701 inserts/sec

Testing for 20000 iterations
Testing SQLite... later, 09 ms 44435 inserts/sec
Testing Firebird... 42909 Ms inserts/sec
Testing ms SQL Ce 3. 5... 2276, 4552 Ms 8785 inserts/sec

Testing for 50000 iterations
Testing SQLite... 639,1278 Ms 78231 inserts/sec
Testing Firebird... 34982 Ms inserts/sec
Testing ms SQL Ce 3. 5... 5674,1346 Ms 8811 inserts/sec

Now I'm going to turn synchronization off (with SQL command "Pragma synchronous = OFF") for SQLite engine. which means do not synch with filesystem right away. this shoshould increase the performance dramatically but more fragile during power/OS failures. keep in mind that, this gives an unfair advantage to SQLite.

Testing for 50 iterations
Testing SQLite... 16663 Ms inserts/sec
Testing Firebird... 1162 Ms inserts/sec
Testing ms SQL Ce 3. 5... 23,0046 Ms 2173 inserts/sec

Testing for 250 iterations
Testing SQLite... 124975 Ms inserts/sec
Testing Firebird... 27772 Ms inserts/sec
Testing ms SQL ce 6943... Ms inserts/sec

Testing for 500 iterations
Testing SQLite... 124975 Ms inserts/sec
Testing Firebird... 38453 Ms inserts/sec
Testing ms SQL ce 70,014 .. 7141 Ms inserts/sec

Testing for 1000 iterations
Testing SQLite... 142828 Ms inserts/sec
Testing Firebird... 24, 41658 Ms inserts/sec
Testing ms SQL Ce 3. 5... 136,0272 Ms 7351 inserts/sec

Testing for 5000 iterations
Testing SQLite... 25,005 Ms 199960 inserts/sec
Testing Firebird... 175,035 Ms 28565 inserts/sec
Testing ms SQL Ce 3. 5... 599,1198 Ms 8345 inserts/sec

Testing for 10000 iterations
Testing SQLite... 188641 Ms inserts/sec
Testing Firebird... 220,044 Ms 45445 inserts/sec
Testing ms SQL ce 8724 inserts/sec

Testing for 20000 iterations
Testing SQLite... 188641 Ms inserts/sec
Testing Firebird... 38978 Ms inserts/sec
Testing ms SQL Ce 3. 5... 2316,4632 Ms 8633 inserts/sec

Testing for 50000 iterations
Testing SQLite... 253,0506 Ms 197588 inserts/sec
Testing Firebird... 37928 Ms inserts/sec
Testing ms SQL Ce 3. 5... 5600,1198 Ms 8928 inserts/sec

Conclusion

Unfortunetely there's no definitive answer.

engine	small insert set performance	large insert set performance	feature set	documentation	cross-platform	license
SQLite	6898	217544	lacks stored procedures	good	Yes	public domain
SQLite (Sync off)	266613	917658	same as above
Firebird	67361	194477	very complete	poor	Yes	idpl (something like lpgl)
ms SQL ce 3.5	17098	42005	very minimal	very good	NO	free to use and redistribute but POPs-up a license agreement on the end users box.

Small insert set performance graph
1. Firebird 2	67361
2. ms SQL ce 3.5	17098
3. SQLite 3.6.3	6898

Large insert set performance graph
1. SQLite 3.6.3	217544
2. Firebird 2	194477
3. ms SQL ce 3.5	42005

Now, just for the record, if we include SQLite with Syncronization = off parameter set, the graphs wocould be like this:

Small insert set performance graph
1. SQLite 3.6.3 (Sync off)	266613
2. Firebird 2	67361
3. ms SQL ce 3.5	17098
4. SQLite 3.6.3	6898

Large insert set performance graph
1. SQLite 3.6.3 (Sync off)	917658
2. SQLite 3.6.3	217544
3. Firebird 2	194477
4. ms SQL ce 3.5	42005

Small insert set performance: Sum of insert rates of 50,250,500 sets.
Large insert set performance: Sum of insert rates of 1000,500 0, 10000,200 00, 50000 sets.
Documentation: This is for my taste. YMMV.
Note:Feel free to crrect me on any of these.