How fast is SQLite compared to Microsoft Access MDB?
Asked Answered
B

4

16

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.

Boogiewoogie answered 24/6, 2011 at 15:57 Comment(11)
"rather small sets of data" - is performance even an issue then?Carboy
Thanks, @Rup. I think, performance is an issue, since I do access the data quite often. I'll update the question with more details.Boogiewoogie
You may want to add some detail in regards to the number of tables you have. The number of rows and size of the database.Coyne
I'm not familiar with c#, so this question may be naive, but ... can't you create Jet and SQLite versions of your small db and compare their performance in your application's context?Riplex
@Riplex - I would expect that I have to change my SQL queries I run against the different types of databases. So this would be the part with the most effort to put in. I therefore asked the question so that I can decide whether it is worth the work :-)Boogiewoogie
In that case I would select just a few representative queries which best reflect your app's performance issues ... convert and test those to see if further efforts are justified.Riplex
Have you considered Microsoft SQL CE? See weblogs.asp.net/scottgu/archive/2011/01/11/…Isar
@Michael Beside that SQL CE does not support views (which we need here), we had very bad performance issues in another app, that we solved by switching from SQL CE to SQLite.Boogiewoogie
I'm very suspicious of these reports of corrupted Jet installations, as Jet is a component of Windows that is managed and updated by the OS. If it got corrupted, I'd expect there to be major issues with Windows in general (particularly Active Directory, the component that uses the Jet database engine). Certainly there is no installation requirement with Jet, since it's been installed with every copy of Windows starting with Windows 2000. The only issue in the future is the 64-bit one, and that's a thorny one...Prefigure
@David-W-Fenton: "Active Directory, the component that uses the Jet database engine" -- undoubtedly components of Windows depend on Jet Red but AFAIK the Active Directory dependency is on Jet Blue.Khalif
Sorry to hear that. I was wondering what happened to him, as I called the number he had posted on his website and was unable to speak to him.Manlike
B
15

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.

Boogiewoogie answered 5/2, 2016 at 13:35 Comment(6)
Thanks for posting your results. Just to clarify: Were the Access and SQLite databases on the local hard drive?Kenward
@GordThompson Yes, I'm just about to update my answer with more details.Boogiewoogie
OK, I've changed it, @HansUp. Thank you :-)Boogiewoogie
Are you using prepared statements? Are you committing upon each insert, update or delete? Or do you have autocommit on? That has a huge impact on performance. I suggest that you modify your code accordingly and advise on the results.Verdure
Looking at the pastebin code, you dont enable pooling enabled for SQLite (its off by default). A simple test inserting 1000 recs (SQLite) for me was 17-19 secs. With pooling on (and still disposing the connection) was abut half: 9.1 - 10.9. Just about the same as using one open connection: 9.4 - 10.2.Carlettacarley
I am working on a winforms application, should I use ms access as database or sqlite db for security? I can password protect ms access also. Which is better ??Staunch
R
8

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
Riplex answered 24/6, 2011 at 17:12 Comment(1)
You're welcome. SQLite seems to be less accommodating about field names; if your field names include spaces or punctuation characters, you will have to change them to keep SQLite happy. But under the best circumstances, the Jet export/ SQLite import operation could be quick and easy. "could be!" Hope you'll be so lucky. :-)Riplex
W
5

Actually in a fact, I'm not sure you're really asking the right question here.

It sounds to me like you're looking for solution by changing your tools, and not changing your design and your approaches. In a fact, the access jet engine is substantially faster than something like a oracle, or mySQL, or SQL server for most operations. The reason is those other systems are huge mass of server based systems that have socket connections to the server. They have layers of transaction processing. There is probably 500 extra layers of software and systems between you and the actual data that resides on the hard drive.

Contrast that to access which is essentially an in process program (not as running service). You do not connect to Access data files through some TCP/IP connection like you do with server based systems (in fact most of those server based systems force you to connect through and networking layer, even on your local machine and less you use a local memory connection, assuming that option is available).

JET (Access database engine) is not a service, and is simply scrape the file off the hard drive and displays the results. That scraping of the data off the disk drive occurs at the same speed as oracle or SQL server and all of those other systems (we're assuming the same machine and hardware here ). Yet those other systems still have another 500 perhaps even 1000 extra layers of code and Software and Network connections and massive amounts of thngs like user security etc. All of these things substantially slow down that getting to the data on the disk drive by large amounts.

Now course if you talking about a connection over some type of network, then those server based systems are better, because you want all the processing and all that majic to occur BEFORE any data starts to flow down the network pipe.

However in your scenario, the server and the machine are one and the same. Therefore it makes complete sense to eliminate the mass of huge context of thousands of extra layers of software. As I pointed out, and these types of scenarios, jet can be 50% or even double the speed of server based systems like MySql or Oracle.

Access can join, categorized, and total up inventoried for 150,000 records in well under a second, and that with a several table join.

Now on the other hand, in any of these systems, usually the large overhead is, is to open up a connection to a particular table. In fact the time it takes to open a table is about the cost of 30,000 records to transfer. So, this means you want to ensure that your code and use of these tables does not unnecessary open up a new table (especially in some type of code loop. In other words, even in places of repeatedly executing an insert command a SQL, you're far better off to open up a record set, and then do inserts that way, as then you're not using SQL commands anymore, and for each row insert you're not executing a separate parsing of the text in that sql (this can give you about 100 times increase in performance when using access this way – in other words the often quoted advice here is that using SQL commands is faster than opening a record set, is completely incorrect).

What this means is, if your are experiencing some kind of slow down here, I would look at your code and designs, and ensure that record sets and datasets are not being repeatedly opened and closed. You should not be experiencing any kind of noticeable delay in your data operations given the tiny size of the files you mention here.

To be fair, sqlLITE is also (i believe) a in-process non server based edition of MySql, and most of the advantages pointed out above would also apply. But then again, your bottle neck would not be much differnt in each case, and thus we back to desing issues here.

In other words, you're barking up the wrong tree, and a developer who looks for changes in their tools to fix performance is simply looking for a fix by blaming the tools when in most cases the problem lies in the designs adopted.

Winchell answered 25/6, 2011 at 22:20 Comment(11)
I totally agree. 30 tables and some 100 records can't be a performance issue, unless your design or code is somehow wrong.Cowberry
Thanks for your comment. As I said in the 2nd paragraph, I am not having any performance issues with MDB at all. I am asking the question to get a feeling whether I could expect a good performance with SQLite, too.Boogiewoogie
SQLLite has nothing at all to do with MySQL, so far as I'm aware.Prefigure
-1 for paragrapghs of stuff that is just made up and have no basis in realitySandblind
@Conrad: be specific -- what is made up?Prefigure
@David. wow its hard to pick. I think I'll go "with any of these systems, usually the large overhead is, is to open up a connection to a particular table. In fact the time it takes to open a table is about the cost of 30,000 records to transfer" Let's assume a small record that contained 100 Bytes. That means Albert is asserting that transferring 3 MB of data is faster than opening a connection.Sandblind
@David. Also the line of "500 perhaps even 1000 extra layers of code" is just plain delusional, and meaningless. Even if true there isn't a 1 to 1 correlation between an abstract idea like a layer and how a system performs.Sandblind
Most of that post was misguided. His comparisons of Jet with SQL Server/MySQL/Oracle are not useful in response to a question about Access vs. SQLite.Riplex
@Conrad Frix: in regard to the 3MBs of data transfer, you're misinterpreting what Albert is saying, since you are obviously describing a network transaction. Albert's point was that in a NON-NETWORK environment, where Jet/ACE is operating on a locally stored database, it's going to be faster. That was explicit in what he said. I don't know if his numbers are correct, but the point is certainly true. With a server database, all of that overhead is ALWAYS going to be there. With Jet/ACE, if you are operating locally, you see blazingly fast performance.Prefigure
On the "500 extra layers" I think this is an extension of the point I just made. Again, the numbers are made up, true, but the point is quite real -- server databases have a lot of provisions within them for things that Jet/ACE doesn't need to do (one example would be transaction logging). I think Albert's point is that if you don't need the capabilities the server database provides, why pay the performance price?Prefigure
@HansUp: I would agree that Albert seems to think that SQLLite is a server database, since otherwise, there'd really be no reason to be making the points about the differences between the overhead of a server database vs. a file-based database engine. Maybe Albert will clarify what he was intending here.Prefigure
B
4

Jet 4.0, DAO, MDAC and ADO have been included as part of the Windows OS since Windows 2000. Thus there is no need to distribute any Jet "drivers" with your application.

Bradney answered 27/6, 2011 at 5:3 Comment(6)
Thanks, @Tony. Still I have customers complaining from time to time that they have a corrupted installation which only can be repaired by reinstalling MDAC/Jet (or tools like this one). Does this sound reasonable to you, or am I hunting for the wrong things here?Boogiewoogie
I'm quite puzzled then. (Your link goes directly to downloading an exe which I'm not willing to do without some understanding of what the tool does and how trustworthy the site is.) I think detailed trouble shooting of the exact errors which happen to your next client would be in order to understand what is all happening. Are you including DAO, ADO or MDAC in your install package? You shouldn't need to.Bradney
We are already excluding the JET/DAO/ADO/MDAC stuff from the setup as you recommend. So this should be OK.Boogiewoogie
BTW, we are still seeing several customers having corrupted Jet installations, even on Win7/Win8. Using the SFC /SCANNOW command line command sometimes helped. I guess anti-virus tools are responsible for this. In one case we found no solution at all but discovered several viruses being found on the system. So I would guess all OleDB stuff is a large battlefield between virus and anti-virus vendors, leaving the actual customer as a collateral damage.Boogiewoogie
Tony Toews: I hope you're correct about not needing additional libraries to use to access .mdb files on Windows, but this hasn't been my experience. I have a Win app that uses OleDbConnection's to query .mdb's. It works on Win systems that have MS Office. If I run it on a fresh Azure VM (Win Server 2012) it fails every time. No corruption, just missing libraries. After running this installer on the VM, it works every time: microsoft.com/en-us/download/details.aspx?id=23734 Other customers (Vista, WinXP, etc.) report the same behavior, but the fresh VM rules out corruption.Bethune
I'm not familiar with OleDBConnection to give an answer one way or another. I do know that using DAO or ADO is built into the Windows OS and has been since Windows 2000.Bradney

© 2022 - 2025 — McMap. All rights reserved.