How fast is SQLite compared to Microsoft Access MDB?

Question

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.

Answer 1

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

• SQL Server Express 2014 on the same local PC and local SSD drive as the test application.
• SQL Server Express 2014 on a server in a gigabit LAN.
• SQL Server Compact (CE) on the same local PC and local SSD drive as the test application.
• Microsoft Access MDB/Jet on the same local PC and local SSD drive as the test application.
• Microsoft SQLite on the same local PC and local SSD drive as the test application.
• Microsoft VistaDB 5 on the same local PC and local SSD drive as the test application.

Since some databases do not support connection pooling, I've done two tests:

1. One test with closing the connection as soon as possible through a using block.
2. Another test with an always open connection to each database for the entire application lifetime

Test results when closing the connections immediately

• SQL Express running locally was the fastest.
• SQL Express on our local network was at second position.
• SQL Compact Edition (CE) was much faster than SQLite and Jet/MDB.
• Jet/MDB was a little bit faster than SQLite and much slower than SQL CE.
• SQLite was a little bit slower than Jet/MDB.
• VistaDB 5 was the slowest database in my test.

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.

• SQL Express running locally was the fastest.
• SQL Express on our local network was at second position.
• SQL Compact Edition (CE) was much faster than SQLite and Jet/MDB.
• Jet/MDB was a little bit faster than SQLite and much slower than SQL CE.
• SQLite was a little bit slower than Jet/MDB.
• VistaDB 5 was the slowest database in my test.

Detailed output of my test application when closing the connections immediately

1.: 1 x DELETE FROM Tabelle1 (Closing connections):
- SQL Express local : 00:00:00.1723705
- SQL Express remote: 00:00:00.2093229
- SQL CE            : 00:00:00.3141897
- MS Access         : 00:00:00.3854029
- SQLite            : 00:00:00.4639365
- VistaDB           : 00:00:00.9699047

2.: 1 x INSERT INTO Tabelle1 (Name1, Wert1) VALUES ({LOOPCTR}, '{LOOPCTR}') (Closing connections):
- SQL Express local : 00:00:00.0039836
- SQL Express remote: 00:00:00.0062002
- SQL CE            : 00:00:00.0432679
- MS Access         : 00:00:00.0817834
- SQLite            : 00:00:00.0933030
- VistaDB           : 00:00:00.1200426

3.: 10 x INSERT INTO Tabelle1 (Name1, Wert1) VALUES ({LOOPCTR}, '{LOOPCTR}') (Closing connections):
- SQL Express local : 00:00:00.0031593
- SQL Express remote: 00:00:00.0142514
- SQL CE            : 00:00:00.3724224
- MS Access         : 00:00:00.7474003
- SQLite            : 00:00:00.8818905
- VistaDB           : 00:00:00.9342783

4.: 100 x INSERT INTO Tabelle1 (Name1, Wert1) VALUES ({LOOPCTR}, '{LOOPCTR}') (Closing connections):
- SQL Express local : 00:00:00.0242817
- SQL Express remote: 00:00:00.1124771
- SQL CE            : 00:00:03.6239390
- MS Access         : 00:00:07.3752378
- SQLite            : 00:00:08.6489843
- VistaDB           : 00:00:09.0933903

5.: 1000 x INSERT INTO Tabelle1 (Name1, Wert1) VALUES ({LOOPCTR}, '{LOOPCTR}') (Closing connections):
- SQL Express local : 00:00:00.2735537
- SQL Express remote: 00:00:01.2657006
- SQL CE            : 00:00:36.2335727
- MS Access         : 00:01:13.8782439
- SQLite            : 00:01:27.1783328
- VistaDB           : 00:01:32.0760340

6.: 1 x SELECT * FROM Tabelle1 (Closing connections):
- SQL Express local : 00:00:00.0520670
- SQL Express remote: 00:00:00.0570562
- SQL CE            : 00:00:00.1026963
- MS Access         : 00:00:00.1646635
- SQLite            : 00:00:00.1785981
- VistaDB           : 00:00:00.2311263

7.: 10 x SELECT * FROM Tabelle1 (Closing connections):
- SQL Express local : 00:00:00.0183055
- SQL Express remote: 00:00:00.0501115
- SQL CE            : 00:00:00.3235680
- MS Access         : 00:00:00.7119203
- SQLite            : 00:00:00.7533361
- VistaDB           : 00:00:00.9804508

8.: 100 x SELECT * FROM Tabelle1 (Closing connections):
- SQL Express local : 00:00:00.1787837
- SQL Express remote: 00:00:00.4321814
- SQL CE            : 00:00:03.0401779
- MS Access         : 00:00:06.8338598
- SQLite            : 00:00:07.2000139
- VistaDB           : 00:00:09.1889217

9.: 1000 x SELECT * FROM Tabelle1 (Closing connections):
- SQL Express local : 00:00:01.6112566
- SQL Express remote: 00:00:03.9542611
- SQL CE            : 00:00:29.1209991
- MS Access         : 00:01:07.2309769
- SQLite            : 00:01:10.3167922
- VistaDB           : 00:01:31.4312770

10.: 1 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID ORDER BY a.ID (Closing connections):
- SQL Express local : 00:00:00.0029406
- SQL Express remote: 00:00:00.0088138
- SQL CE            : 00:00:00.0498847
- MS Access         : 00:00:00.0893892
- SQLite            : 00:00:00.0929506
- VistaDB           : 00:00:00.2575795

11.: 10 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID ORDER BY a.ID (Closing connections):
- SQL Express local : 00:00:00.0174026
- SQL Express remote: 00:00:00.0400797
- SQL CE            : 00:00:00.3408818
- MS Access         : 00:00:00.7314978
- SQLite            : 00:00:00.7653330
- VistaDB           : 00:00:01.9565675

12.: 100 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID ORDER BY a.ID (Closing connections):
- SQL Express local : 00:00:00.1565402
- SQL Express remote: 00:00:00.3787208
- SQL CE            : 00:00:03.3516629
- MS Access         : 00:00:07.2521126
- SQLite            : 00:00:07.5618047
- VistaDB           : 00:00:19.5181391

13.: 1000 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID ORDER BY a.ID (Closing connections):
- SQL Express local : 00:00:01.5686470
- SQL Express remote: 00:00:03.7414669
- SQL CE            : 00:00:35.3944204
- MS Access         : 00:01:14.6872377
- SQLite            : 00:01:17.9964955
- VistaDB           : 00:03:18.1902279

14.: 1 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID WHERE a.ID < 100 OR a.ID > 300 ORDER BY a.ID (Closing connections):
- SQL Express local : 00:00:00.0053295
- SQL Express remote: 00:00:00.0089722
- SQL CE            : 00:00:00.0395485
- MS Access         : 00:00:00.0797776
- SQLite            : 00:00:00.0833477
- VistaDB           : 00:00:00.2554930

15.: 10 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID WHERE a.ID < 100 OR a.ID > 300 ORDER BY a.ID (Closing connections):
- SQL Express local : 00:00:00.0168467
- SQL Express remote: 00:00:00.0552233
- SQL CE            : 00:00:00.3929877
- MS Access         : 00:00:00.7886399
- SQLite            : 00:00:00.8209904
- VistaDB           : 00:00:02.1248734

16.: 100 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID WHERE a.ID < 100 OR a.ID > 300 ORDER BY a.ID (Closing connections):
- SQL Express local : 00:00:00.1705345
- SQL Express remote: 00:00:00.3969228
- SQL CE            : 00:00:03.4886826
- MS Access         : 00:00:07.4564258
- SQLite            : 00:00:07.7828646
- VistaDB           : 00:00:20.4092926

17.: 1000 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID WHERE a.ID < 100 OR a.ID > 300 ORDER BY a.ID (Closing connections):
- SQL Express local : 00:00:01.6237424
- SQL Express remote: 00:00:03.9816212
- SQL CE            : 00:00:35.1441759
- MS Access         : 00:01:14.7739758
- SQLite            : 00:01:17.9477049
- VistaDB           : 00:03:24.0049633

Detailed output of my test application when keeping the connections open

1.: 1 x DELETE FROM Tabelle1 (keeping connection open):
- SQL Express local : 00:00:00.0426930
- SQL Express remote: 00:00:00.0546357
- SQL CE            : 00:00:00.0786765
- MS Access         : 00:00:00.0909099
- SQLite            : 00:00:00.1101572
- VistaDB           : 00:00:00.4637726

2.: 1 x INSERT INTO Tabelle1 (Name1, Wert1) VALUES ({LOOPCTR}, '{LOOPCTR}') (keeping connection open):
- SQL Express local : 00:00:00.0030936
- SQL Express remote: 00:00:00.0051136
- SQL CE            : 00:00:00.0054226
- MS Access         : 00:00:00.0074847
- SQLite            : 00:00:00.0154474
- VistaDB           : 00:00:00.0373701

3.: 10 x INSERT INTO Tabelle1 (Name1, Wert1) VALUES ({LOOPCTR}, '{LOOPCTR}') (keeping connection open):
- SQL Express local : 00:00:00.0023271
- SQL Express remote: 00:00:00.0109913
- SQL CE            : 00:00:00.0119872
- MS Access         : 00:00:00.0152531
- SQLite            : 00:00:00.1131698
- VistaDB           : 00:00:00.1261859

4.: 100 x INSERT INTO Tabelle1 (Name1, Wert1) VALUES ({LOOPCTR}, '{LOOPCTR}') (keeping connection open):
- SQL Express local : 00:00:00.0201695
- SQL Express remote: 00:00:00.0888872
- SQL CE            : 00:00:00.0966017
- MS Access         : 00:00:00.1256167
- SQLite            : 00:00:01.3632978
- VistaDB           : 00:00:01.9422151

5.: 1000 x INSERT INTO Tabelle1 (Name1, Wert1) VALUES ({LOOPCTR}, '{LOOPCTR}') (keeping connection open):
- SQL Express local : 00:00:00.1693362
- SQL Express remote: 00:00:00.9181297
- SQL CE            : 00:00:01.0366334
- MS Access         : 00:00:01.2794199
- SQLite            : 00:00:13.9398816
- VistaDB           : 00:00:19.8319476

6.: 1 x SELECT * FROM Tabelle1 (keeping connection open):
- SQL Express local : 00:00:00.0481500
- SQL Express remote: 00:00:00.0507066
- SQL CE            : 00:00:00.0738698
- MS Access         : 00:00:00.0911707
- SQLite            : 00:00:00.1012425
- VistaDB           : 00:00:00.1515495

7.: 10 x SELECT * FROM Tabelle1 (keeping connection open):
- SQL Express local : 00:00:00.0157947
- SQL Express remote: 00:00:00.0692206
- SQL CE            : 00:00:00.0898558
- MS Access         : 00:00:00.1196514
- SQLite            : 00:00:00.1400944
- VistaDB           : 00:00:00.3227485

8.: 100 x SELECT * FROM Tabelle1 (keeping connection open):
- SQL Express local : 00:00:00.1517498
- SQL Express remote: 00:00:00.3399897
- SQL CE            : 00:00:00.5497382
- MS Access         : 00:00:00.8619646
- SQLite            : 00:00:01.0463369
- VistaDB           : 00:00:02.8607334

9.: 1000 x SELECT * FROM Tabelle1 (keeping connection open):
- SQL Express local : 00:00:01.5042900
- SQL Express remote: 00:00:03.8431985
- SQL CE            : 00:00:05.9075477
- MS Access         : 00:00:09.2642402
- SQLite            : 00:00:11.4427914
- VistaDB           : 00:00:30.8470936

10.: 1 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID ORDER BY a.ID (keeping connection open):
- SQL Express local : 00:00:00.0033803
- SQL Express remote: 00:00:00.0062499
- SQL CE            : 00:00:00.0141105
- MS Access         : 00:00:00.0188573
- SQLite            : 00:00:00.0208236
- VistaDB           : 00:00:00.1796513

11.: 10 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID ORDER BY a.ID (keeping connection open):
- SQL Express local : 00:00:00.0168644
- SQL Express remote: 00:00:00.0377185
- SQL CE            : 00:00:00.1121558
- MS Access         : 00:00:00.1599104
- SQLite            : 00:00:00.1799435
- VistaDB           : 00:00:01.4042534

12.: 100 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID ORDER BY a.ID (keeping connection open):
- SQL Express local : 00:00:00.1547275
- SQL Express remote: 00:00:00.3692526
- SQL CE            : 00:00:01.1215470
- MS Access         : 00:00:01.5577172
- SQLite            : 00:00:01.7519790
- VistaDB           : 00:00:14.5687575

13.: 1000 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID ORDER BY a.ID (keeping connection open):
- SQL Express local : 00:00:01.4992800
- SQL Express remote: 00:00:03.7601806
- SQL CE            : 00:00:11.1738426
- MS Access         : 00:00:15.8112902
- SQLite            : 00:00:17.8045042
- VistaDB           : 00:02:21.4492368

14.: 1 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID WHERE a.ID < 100 OR a.ID > 300 ORDER BY a.ID (keeping connection open):
- SQL Express local : 00:00:00.0048145
- SQL Express remote: 00:00:00.0076790
- SQL CE            : 00:00:00.0152074
- MS Access         : 00:00:00.0204568
- SQLite            : 00:00:00.0229056
- VistaDB           : 00:00:00.2091614

15.: 10 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID WHERE a.ID < 100 OR a.ID > 300 ORDER BY a.ID (keeping connection open):
- SQL Express local : 00:00:00.0156564
- SQL Express remote: 00:00:00.0377571
- SQL CE            : 00:00:00.1138433
- MS Access         : 00:00:00.1598932
- SQLite            : 00:00:00.1793267
- VistaDB           : 00:00:01.4667061

16.: 100 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID WHERE a.ID < 100 OR a.ID > 300 ORDER BY a.ID (keeping connection open):
- SQL Express local : 00:00:00.1512625
- SQL Express remote: 00:00:00.4658652
- SQL CE            : 00:00:01.2441809
- MS Access         : 00:00:01.7224126
- SQLite            : 00:00:01.9297231
- VistaDB           : 00:00:14.9351318

17.: 1000 x SELECT a.* FROM Tabelle1 a LEFT JOIN Tabelle1 b ON a.ID=b.ID WHERE a.ID < 100 OR a.ID > 300 ORDER BY a.ID (keeping connection open):
- SQL Express local : 00:00:01.5223833
- SQL Express remote: 00:00:03.9885174
- SQL CE            : 00:00:11.8356048
- MS Access         : 00:00:16.5977939
- SQLite            : 00:00:18.6504260
- VistaDB           : 00:02:26.0513056

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:

Access             of 1000 WRITE iterations took 00:00:39.6488351.
LiteDB             of 1000 WRITE iterations took 00:00:01.6596646.
LiteDB (in-memory) of 1000 WRITE iterations took 00:00:00.1617220.
Access             of 1000 READ  iterations took 00:00:48.8517302.
LiteDB             of 1000 READ  iterations took 00:00:00.0082401.
LiteDB (in-memory) of 1000 READ  iterations took 00:00:00.0097933.

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.

Answer 2

In case you decide to do your own benchmark testing, I offer this procedure to export your Jet tables to CSV files. Then you can import them into your SQLite database.

Public Sub DumpTablesAsCsv()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strCsvFile As String
    Dim strFolder As String
    Dim strTable As String

    strFolder = CurrentProject.Path & Chr(92)
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        strTable = tdf.Name
        If Not (strTable Like "MSys*" Or strTable Like "~*") Then
            strCsvFile = strFolder & strTable & ".csv"
            DoCmd.TransferText acExportDelim, , strTable, _
                strCsvFile, HasFieldNames:=True
        End If
    Next tdf
    Set tdf = Nothing
    Set db = Nothing
End Sub