UPDATE 20-OCT-2008: Instead of creating a DBCommand for each iteration, I started to use one DBCommand instance, as any normal person would 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 got.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, 1000, 5000, 10000, 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... 100,02ms 499 inserts / sec Testing firebird... 44,0088ms 1136 inserts / sec Testing MS SQL CE 3.5... 23,0046ms 2173 inserts / sec Testing for 250 iterations Testing sqlite... 99,0198ms 2524 inserts / sec Testing firebird... 9,0018ms 27772 inserts / sec Testing MS SQL CE 3.5... 33,0066ms 7574 inserts / sec Testing for 500 iterations Testing sqlite... 129,0258ms 3875 inserts / sec Testing firebird... 14,0028ms 35707 inserts / sec Testing MS SQL CE 3.5... 68,0136ms 7351 inserts / sec Testing for 1000 iterations Testing sqlite... 120,024ms 8331 inserts / sec Testing firebird... 26,0052ms 38453 inserts / sec Testing MS SQL CE 3.5... 135,027ms 7405 inserts / sec Testing for 5000 iterations Testing sqlite... 145,029ms 34475 inserts / sec Testing firebird... 165,033ms 30296 inserts / sec Testing MS SQL CE 3.5... 602,1204ms 8303 inserts / sec Testing for 10000 iterations Testing sqlite... 192,0384ms 52072 inserts / sec Testing firebird... 209,0418ms 47837 inserts / sec Testing MS SQL CE 3.5... 1149,2298ms 8701 inserts / sec Testing for 20000 iterations Testing sqlite... 450,09ms 44435 inserts / sec Testing firebird... 466,0932ms 42909 inserts / sec Testing MS SQL CE 3.5... 2276,4552ms 8785 inserts / sec Testing for 50000 iterations Testing sqlite... 639,1278ms 78231 inserts / sec Testing firebird... 1429,2858ms 34982 inserts / sec Testing MS SQL CE 3.5... 5674,1346ms 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 should 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... 3,0006ms 16663 inserts / sec Testing firebird... 43,0086ms 1162 inserts / sec Testing MS SQL CE 3.5... 23,0046ms 2173 inserts / sec Testing for 250 iterations Testing sqlite... 2,0004ms 124975 inserts / sec Testing firebird... 9,0018ms 27772 inserts / sec Testing MS SQL CE 3.5... 36,0072ms 6943 inserts / sec Testing for 500 iterations Testing sqlite... 4,0008ms 124975 inserts / sec Testing firebird... 13,0026ms 38453 inserts / sec Testing MS SQL CE 3.5... 70,014ms 7141 inserts / sec Testing for 1000 iterations Testing sqlite... 7,0014ms 142828 inserts / sec Testing firebird... 24,0048ms 41658 inserts / sec Testing MS SQL CE 3.5... 136,0272ms 7351 inserts / sec Testing for 5000 iterations Testing sqlite... 25,005ms 199960 inserts / sec Testing firebird... 175,035ms 28565 inserts / sec Testing MS SQL CE 3.5... 599,1198ms 8345 inserts / sec Testing for 10000 iterations Testing sqlite... 53,0106ms 188641 inserts / sec Testing firebird... 220,044ms 45445 inserts / sec Testing MS SQL CE 3.5... 1146,2292ms 8724 inserts / sec Testing for 20000 iterations Testing sqlite... 106,0212ms 188641 inserts / sec Testing firebird... 513,1026ms 38978 inserts / sec Testing MS SQL CE 3.5... 2316,4632ms 8633 inserts / sec Testing for 50000 iterations Testing sqlite... 253,0506ms 197588 inserts / sec Testing firebird... 1318,2636ms 37928 inserts / sec Testing MS SQL CE 3.5... 5600,1198ms 8928 inserts / sec
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 would 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, 5000, 10000, 20000, 50000 sets.
Documentation: This is for my taste. YMMV.
NOTE: Feel free to crrect me on any of these.