与 Microsoft Access MDB 相比，SQLite 的速度有多快? [英] How fast is SQLite compared to Microsoft Access MDB?

查看：56
本文介绍了与 Microsoft Access MDB 相比，SQLite 的速度有多快?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

目前我正在考虑将 Microsoft Jet MDB 数据库在单用户 .NET C# Windows 窗体应用程序上的使用替换为 SQlite 数据库.

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.

我的目标是降低安装要求，例如 Jet 驱动程序和在 Jet 安装损坏时出现的一些严重错误(我们的客户时不时会报告这些错误).

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).

我关于性能的问题是:

是否有任何性能基准在相当小的数据集上比较 MDB 和 SQLite?

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?

(我现在在谷歌上搜索了几个小时没有成功)

更新

尽管数据库不包含许多记录和表，但我认为性能仍然是一个问题，因为数据经常被访问.

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.

该应用程序是一个所谓的桌面 CMS 系统"，用于呈现 HTML 页面；在渲染过程中，会访问大量数据，执行大量SQL查询.

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.

更新 2

刚刚找到此文档，其中显示了一些速度比较，不幸的是，据我所知，MDB 没有.

Just found this documentation which indicates some speed comparisons, unfortunately not with MDB, as far as I can see.

更新 3

根据要求，一些数字:

  • 大约数据库中有 30 个表.
  • 大多数表的记录少于 100 条.
  • 大约5 个表，通常有几百到几千条记录.
  • 一个大型 MDB 文件大约为 60 MB.

更新 4

重申一下:我没有对当前的 MDB 实现有任何性能问题.我问这个问题是为了了解使用 SQLite 而不是 MDB 时性能是否相同(或更好).

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.

推荐答案

4 年多后，我实际上做了一个小型(可能有点幼稚)的 MDB 和 SQLite 性能对比测试.

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.

我测试过的数据库

  • 在与测试应用程序相同的本地 PC 和本地 SSD 驱动器上安装 SQL Server Express 2014.
  • 千兆 LAN 中服务器上的 SQL Server Express 2014.
  • SQL Server Compact (CE) 在与测试应用程序相同的本地 PC 和本地 SSD 驱动器上.
  • 在与测试应用程序相同的本地 PC 和本地 SSD 驱动器上使用 Microsoft Access MDB/Jet.
  • Microsoft SQLite 在与测试应用程序相同的本地 PC 和本地 SSD 驱动器上.
  • 在与测试应用程序相同的本地 PC 和本地 SSD 驱动器上安装 Microsoft VistaDB 5.

由于部分数据库不支持连接池，我做了两个测试:

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

  1. 通过 using 块尽快关闭连接的一项测试.
  2. 另一个测试，在整个应用程序生命周期内始终打开与每个数据库的连接
  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

立即关闭连接时的测试结果

  • 本地运行的 SQL Express 是最快的.
  • 我们本地网络上的 SQL Express 位居第二.
  • SQL Compact Edition (CE) 比 SQLite 和 Jet/MDB 快得多.
  • Jet/MDB 比 SQLite 快一点，但比 SQL CE 慢得多.
  • SQLite 比 Jet/MDB 慢一点.
  • VistaDB 5 是我测试中最慢的数据库.

保持连接打开时的测试结果

结果与立即关闭连接时的结果非常相似.

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 是最快的.
  • 我们本地网络上的 SQL Express 位居第二.
  • SQL Compact Edition (CE) 比 SQLite 和 Jet/MDB 快得多.
  • Jet/MDB 比 SQLite 快一点，但比 SQL CE 慢得多.
  • SQLite 比 Jet/MDB 慢一点.
  • VistaDB 5 是我测试中最慢的数据库.

立即关闭连接时我的测试应用程序的详细输出

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

更新 1，2019 年 4 月

我做了一些测试，将 Microsoft Access MDB 与 LiteDB(用于 .NET 的嵌入式数据库)进行了比较.

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.

这段代码进行了 1000 次读取和写入，结果如下:

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.

所以在我的场景中，LiteDB 比 Access 快得多.

So in my scenario, LiteDB was much faster than Access.

与 VistaDB 5 相比，我还针对 VistaDB 6 Beta 1 运行了我的原始代码.

I've also ran my original code against VistaDB 6 Beta 1 in comparison to VistaDB 5.

我得到了非常相似的速度结果.VistaDB 6 的 Beta 版与 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.

总而言之，我在 VistaDB 5 和 VistaDB 6 Beta 1 之间的场景中没有看到显着的性能改进.我将不得不再次尝试使用 VistaDB 6 的最终版本.

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.

这篇关于与 Microsoft Access MDB 相比，SQLite 的速度有多快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆