Friday, September 3, 2010

A minor modification in the SQLite connection string. A major improvement in performance

If you use EF (entity framework 1) with SQLite you might know that it does not have bulk import support (this is because EF doesn't provide any bulk import API. 

So for quick code you end up with multiple import commands sent to the database in a loop. With synchronization enabled (sqlite flushes the data to the disk after every change) the performance is slow. With synchronization disabled it is BLAZING fast. Just be sure to flush the data at the end :) 

Just add:
synchronous=Off

to your connection string. For a comparison of the perforamance change you can look at : http://www.sqlite.org/speed.html 
SQLite 2.7.6:   13.061
SQLite 2.7.6 (nosync):   0.223

This is how much it feels in my code as well :) It now actually works faster than a portable untuned mysql (5) / postgresql (8.4) / firebird (2.2) 

I keep loving this database more and more :)

Found the .net settings here:

Enjoy!