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.