Currently I'm thinking about replacing the usage of Microsoft Jet MDB databases on a single-user .NET C# Windows Forms application by a SQlite database.
My goal is to lower installation requirements like the Jet drivers and some nasty errors when the Jet installation got corrupted (we have customers every now and then reporting those errors).
My question regarding performance is:
Are there any performance benchmarks out there comparing MDB and SQLite on a rather small sets of data?
Or are there any developers who already did this step and can tell some stories from their own experiences?
(I am googling for hours now without success)
Update
Although the database does not contain that many records and tables, I think performance is still an issue, since the data is being accessed quite often.
The application is a so called "Desktop CMS system" that renders HTML pages; during the rendering, quite a lot of data is being accessed and lots of SQL queries are being executed.
Update 2
Just found this documentation which indicates some speed comparisons, unfortunately not with MDB, as far as I can see.
Update 3
As of request, some figures:
- approx. 30 tables in the database.
- Most tables with way below 100 records.
- approx. 5 tables with usually a few 100 up to a few thousand records.
- A large MDB file would be around 60 MB.
Update 4
Just to rephrase: I am not having any performance issues with the current MDB implementation. I am asking this question to get a feeling whether the performance would be equal (or better) when using SQLite instead of MDB.
Best Answer
More than 4 years later, I actually did a small (probably somewhat naive) performance comparison test between MDB and SQLite.
I've also added more databases.
Datebases I've tested
Since some databases do not support connection pooling, I've done two tests:
using
block.Test results when closing the connections immediately
Test results when keeping the connections open
The results are rather similar to the results when closing a connection immediately.
Relatively to each other, the order from the fastest to the slowest did not change. Some databases with no actual connection pooling improved their absolute performance quite a bit.
Detailed output of my test application when closing the connections immediately
Detailed output of my test application when keeping the connections open
Update 1, April 2019
I did some tests comparing Microsoft Access MDB to LiteDB, an embedded database for .NET.
So again, some rather naive comparison, but I still wanted to get a feeling.
This code does 1000 reads and writes and resulted in these values:
So in my scenario, LiteDB was much faster than Access.
Update 2, April 2019
I've also ran my original code against VistaDB 6 Beta 1 in comparison to VistaDB 5.
I've got very similar speed results. The Beta of VistaDB 6 was slightly slower compared to VistaDB 5, most likely because it was a debug build.
As a conclusion, I see no significant performance improvements in my scenario between VistaDB 5 and VistaDB 6 Beta 1. I will have to try again with the final version of VistaDB 6.