In this post, we will establish a performance comparison among several freely distributable embedded database technologies for the .NET crowd. Your real-life mileage may vary (depending on your exact usage scenario), so make sure to run your own (more specific) benchmarks as required. Also, keep in mind that performance (while important) is only one of the many aspects to consider when choosing an embedded database technology (together with ease of development and enhancement, availability and quality of tooling, ease of maintenance, licensing and distribution, …).
The lineup
The following selection criteria were considered:
- Only technologies that can be called “first class citizens” from a .NET perspective (with reasonable scrutiny) will be included.
- We will limit the selection to flat-file, traditional relational data stores (because including NoSQL or document databases would really be comparing apples and oranges).
We still include some older and deprecated technologies (for comparison and reference purposes). Also, some of these might still make for viable choices in their own right - in very specific circumstances.
Access 2013
Although its popularity has likely decreased substantially, Access undeniably has a long history as a well-featured embedded database platform. We will test both the historic JET OleDB driver (4.0 - 32 bit only) and the newer ACE OleDB driver (15.0). The Microsoft Access 2013 runtime (a 200MB+ download) can be used for clients that don’t have a corresponding version of Office installed.
SQL Server Compact 4.0.8876.1
For a long time, SQL Server CE was Microsofts default, well supported, stable and fast embedded database offering. For many, its deprecated status came as a bit of a surprise (mainstream support for CE 4.0 ended on 2016-07-12 and extended support will end on 2021-07-13).
SQL Server Express LocalDB 2012 - 2014 - 2016
As the now official successor to SQL Server CE (no longer in-proc), LocalDB is the newest kid on the block. As a zero-configuration autobootstrapped SQL Server instance, it promises both world-class performance and a very mature feature set. LocalDB can either be installed as an option in the (rather heavyweight) SQL Server Express installation (all versions) or as a standalone SqlLocalDb.msi install (around 35MB). I did not manage to locate the 2016 standalone msi installer.
System.Data.Sqlite (1.0.104)
System.Data.Sqlite is the official ADO.NET provider for Sqlite, a hugely popular, well maintained and widely used open source embedded database technology (with the initial release dating back to 2000). Wikipedia claims “It is arguably the most widely deployed database engine, as it is used today by several widespread browsers, operating systems, and embedded systems (such as mobile phones), among others.“.
Methodology
Full source code for the benchmarks can be found here: BenchmarkEmbeddedDatabases.NET.
The tests were run on a personal machine (with the power scheme configured to high performance, and no significant processes running at the same time):
OS: Microsoft Windows [Version 10.0.14393]
CPU: Intel(R) Core(TM) i3-4030U CPU @ 1.90GHz
DISK: Kingston HyperX Savage SSD SSHS37A/240GThe focus is raw CRUD operation performance (insert, select, update, delete) - for both indexed and non-indexed scenarios. Combined, these figures yield a fairly good picture of fundamental database performance. Regular System.Diagnostics Stopwatches are used, although leveraging BenchmarkDotNet would be a good idea.
No specific database performance tuning was performed. All tests run with default connection pooling options (in
other words enabled in ADO.NET if supported by the driver). All n executed operations are performed together in the context of a single transaction
(with the commit time included in the timings). For SQLite, the rowid is reused as autoincrementing primary key
(as described in the documentation).
The results
Opening and closing connections in quick sucession
From this graph, we can tell that SQL Server CE does not seem to implement native connection pooling in its ADO.NET driver, so it’s important to
use a library or roll your own if you need to open a lot of connections. There is very little overhead for opening new connections to SQL Server
Express LocalDB.
000007 ms - avg 000028 µs - SQL Server Express LocalDB 2012
000008 ms - avg 000032 µs - SQL Server Express LocalDB 2014
000009 ms - avg 000036 µs - SQL Server Express LocalDB 2016
000400 ms - avg 001600 µs - System.Data.Sqlite (1.0.104)
000732 ms - avg 002928 µs - Access 2013 32-bit (JET OLEDB 4)
000748 ms - avg 002992 µs - Access 2013 32-bit (ACE OLEDB 15)
009729 ms - avg 038916 µs - SQL Server Compact 4.0.8876.1Insert performance
We insert 25000 records (consisting of two fields: id - int, guid - varchar). It’s disappointing to see how Access only has
half the insert performance when using the ACE driver. SQLite outperforms everyone.
000418 ms - avg 000017 µs - System.Data.Sqlite (1.0.104)
001963 ms - avg 000079 µs - SQL Server Compact 4.0.8876.1
003668 ms - avg 000147 µs - SQL Server Express LocalDB 2012
003784 ms - avg 000151 µs - SQL Server Express LocalDB 2016
003793 ms - avg 000152 µs - SQL Server Express LocalDB 2014
008590 ms - avg 000344 µs - Access 2013 32-bit (JET OLEDB 4)
015398 ms - avg 000616 µs - Access 2013 32-bit (ACE OLEDB 15)
25000 inserts (consisting of only a single guid - varchar value to be set) are inserted in a table with an autoincrementing primary key.
000370 ms - avg 000015 µs - System.Data.Sqlite (1.0.104)
001909 ms - avg 000076 µs - SQL Server Compact 4.0.8876.1
003784 ms - avg 000151 µs - SQL Server Express LocalDB 2014
004014 ms - avg 000161 µs - SQL Server Express LocalDB 2016
004846 ms - avg 000194 µs - SQL Server Express LocalDB 2012
007836 ms - avg 000313 µs - Access 2013 32-bit (JET OLEDB 4)
013958 ms - avg 000558 µs - Access 2013 32-bit (ACE OLEDB 15)Select performance
We can establish the same patterns here: SQLite is blazingly fast and Access loses half of its speed when used through ACE rather than JET.
SQL Server (CE and LocalDB) outperform Access by a fair margin.
000258 ms - avg 000026 µs - System.Data.Sqlite (1.0.104)
001575 ms - avg 000158 µs - SQL Server Express LocalDB 2016
001603 ms - avg 000160 µs - SQL Server Express LocalDB 2012
001692 ms - avg 000169 µs - SQL Server Express LocalDB 2014
002026 ms - avg 000203 µs - SQL Server Compact 4.0.8876.1
004438 ms - avg 000444 µs - Access 2013 32-bit (JET OLEDB 4)
008309 ms - avg 000831 µs - Access 2013 32-bit (ACE OLEDB 15)
003680 ms - avg 001840 µs - System.Data.Sqlite (1.0.104)
006130 ms - avg 003065 µs - SQL Server Express LocalDB 2012
006380 ms - avg 003190 µs - SQL Server Express LocalDB 2016
006469 ms - avg 003235 µs - Access 2013 32-bit (JET OLEDB 4)
006985 ms - avg 003493 µs - SQL Server Express LocalDB 2014
011852 ms - avg 005926 µs - Access 2013 32-bit (ACE OLEDB 15)
026003 ms - avg 013002 µs - SQL Server Compact 4.0.8876.1Update performance
This graph is quite close to the graph we have seen in select performance (indexed).
000224 ms - avg 000022 µs - System.Data.Sqlite (1.0.104)
001586 ms - avg 000159 µs - SQL Server Express LocalDB 2012
001641 ms - avg 000164 µs - SQL Server Express LocalDB 2014
001674 ms - avg 000167 µs - SQL Server Express LocalDB 2016
002163 ms - avg 000216 µs - SQL Server Compact 4.0.8876.1
004755 ms - avg 000476 µs - Access 2013 32-bit (JET OLEDB 4)
007781 ms - avg 000778 µs - Access 2013 32-bit (ACE OLEDB 15)
This is one of the few scenarios where Access really performs rather well, while SQL Server CE comes in last.
007413 ms - avg 003707 µs - System.Data.Sqlite (1.0.104)
012214 ms - avg 006107 µs - Access 2013 32-bit (JET OLEDB 4)
021977 ms - avg 010989 µs - Access 2013 32-bit (ACE OLEDB 15)
023567 ms - avg 011784 µs - SQL Server Express LocalDB 2012
024603 ms - avg 012302 µs - SQL Server Express LocalDB 2014
025623 ms - avg 012812 µs - SQL Server Express LocalDB 2016
055916 ms - avg 027958 µs - SQL Server Compact 4.0.8876.1Delete performance
000181 ms - avg 000018 µs - System.Data.Sqlite (1.0.104)
001440 ms - avg 000144 µs - SQL Server Express LocalDB 2012
001469 ms - avg 000147 µs - SQL Server Express LocalDB 2014
001526 ms - avg 000153 µs - SQL Server Express LocalDB 2016
002090 ms - avg 000209 µs - SQL Server Compact 4.0.8876.1
004393 ms - avg 000439 µs - Access 2013 32-bit (JET OLEDB 4)
007416 ms - avg 000742 µs - Access 2013 32-bit (ACE OLEDB 15)
007163 ms - avg 003582 µs - System.Data.Sqlite (1.0.104)
011869 ms - avg 005935 µs - Access 2013 32-bit (JET OLEDB 4)
021091 ms - avg 010546 µs - Access 2013 32-bit (ACE OLEDB 15)
023309 ms - avg 011655 µs - SQL Server Express LocalDB 2012
024477 ms - avg 012239 µs - SQL Server Express LocalDB 2014
025272 ms - avg 012636 µs - SQL Server Express LocalDB 2016
055222 ms - avg 027611 µs - SQL Server Compact 4.0.8876.1Conclusion
SQLite really is the fastest embedded database of the presented options. However, be sure to read the documentation as SQLite’s model of storage classes / type affinity can be quite confusing for developers used to a more traditional (statically typed) RDBMS. In short, a column in SQLite can store (and will accept) anything (regardless of the type definition of the column, which functions more as a hint), potentially leading to some hard to spot errors. Access is impossible to recommend if it achieves only half of its former performance throught the newer (ACE) drivers (and it already had its quirks to start with). SQL Server Express LocalDB lives up to its promise though. It consistently outperforms SQL Server CE, making it a very good option for .NET developers if you can live with its out-of-process nature and deployment footprint / model. Mature SQL syntax support, the ability to upsize to ‘full’ SQL server if required and the great tooling (SSMS) make it a very attractive choice for many use cases.