.netsqlitems-accessjet

How fast is SQLite compared to Microsoft Access MDB?


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:

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.


Solution

  • 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:

    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

    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

    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.