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

Source: Internet
Author: User
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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.