Tim Anderson's ITWriting

February 7, 2006

Who's fastest: SQLite, SQL Server, MySQL, or what?

I am always interested in speed comparisons even though I realise that they often lack meaning. A bunch of tests have just been run by contributors to the SQLite mailing list. These are tests run on ordinary PC hardware, not those high-end multi-processor beasts the big guys use for their TPC challenges. Results as follows:

SQLite vs PostgreSQL vs MySQL vs Firebird SQL

SQLite vs Firebird vs VistaDb vs JET vs SQL Server 2005

SQLite vs SQL Server Mobile (.NET Compact Framework)

You shouldn't take too much notice because a little tuning of any individual database engine can dramatically change its results. Another factor is that database engines with fewer features may be faster, but if you need those features that's a price worth paying.

There's usually something to learn though. For example, I was interested in the impact of using a transaction on the tests in the first set of results above. SQLite performs vastly better when you wrap a series of inserts within a transaction, but the difference is less marked with the other engines tested, which may actually be slower (the number of inserts is different, so we really need to modify the tests). Under the covers, this is all to do with at what point the engine ensures that the inserts have really been written to the disk.

Learning how to get the best from your chosen database engine can be just as important as making the initial selection.

Tags: sqlite database mysql