目前,我正在考虑通过sqlite数据库在单用户.NET C#
Windows Forms应用程序上替换Microsoft Jet MDB数据库的用法.
我的目标是降低安装要求,如Jet驱动程序和Jet安装损坏时的一些令人讨厌的错误(我们现在有客户,然后报告这些错误).
关于表演的问题是：
有没有任何性能基准测试比较MDB和sqlite在一组相当小的数据？
还是有任何开发者已经做了这个步骤,可以从自己的经验中讲出一些故事？
(我现在已经几个小时了,没有成功)
更新
虽然数据库不包含许多记录和表,但我认为性能仍然是一个问题,因为数据被频繁访问.
该应用程序是一个所谓的“桌面CMS系统”,用于呈现HTML页面;在渲染期间,正在访问相当多的数据,并且正在执行大量的SQL查询.
更新2
刚刚发现this documentation这表示一些速度比较,不幸的是不是与MDB,据我所知.
更新3
根据要求,一些数字：
>约.数据库中有30个表.
>大多数表的方式低于100条记录.
>约. 5个表,通常有几百个,几千个记录.
>一个大的MDB文件将是大约60 MB.
更新4
只是为了改写：我目前的MDB实现没有任何性能问题.在使用sqlite而不是MDB时,我正在问这个问题,以了解性能是否相等(或更好).
4年多以后,我实际上在MDB和sqlite之间做了一个小的(可能有点天真的)性能比较测试.
我测试过的日期
> sql Server Express 2014在同一本地PC和本地SSD驱动器上作为测试应用程序.
> sql Server Express 2014在千兆网络中的服务器上.
> sql Server Compact(CE)在同一本地PC和本地SSD驱动器上作为测试应用程序.
> Microsoft Access MDB / Jet在同一本地PC和本地SSD驱动器上作为测试应用程序.
> Microsoft sqlite在同一本地PC和本地SSD驱动器上作为测试应用程序.
> Microsoft VistaDB 5在同一本地PC和本地SSD驱动器上作为测试应用程序.
自some databases do not support connection pooling以来,我做了两个测试：
>通过一个使用块尽快关闭连接的一个测试.
>另一个测试,始终打开连接到每个数据库,为整个应用程序的生命周期
在立即关闭连接时测试结果
>本地运行的sql Express是最快的.
>本地网络上的sql Express处于第二位.
> sql Compact Edition(CE)比sqlite和Jet / MDB快得多.
> Jet / MDB比sqlite快一点,比sql CE慢得多.
> sqlite比Jet / MDB慢一点点.
> VistaDB 5是我测试中最慢的数据库.
保持连接打开时测试结果
结果与立即关闭连接时的结果相似.
相对来说,从最快到最慢的顺序没有改变.一些没有实际连接池的数据库提高了它们的绝对性能.
>本地运行的sql Express是最快的.
>本地网络上的sql Express处于第二位.
> sql Compact Edition(CE)比sqlite和Jet / MDB快得多.
> Jet / MDB比sqlite快一点,比sql CE慢得多.
> sqlite比Jet / MDB慢一点点.
> VistaDB 5是我测试中最慢的数据库.
详细输出我的test application,立即关闭连接
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,'{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,'{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,'{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
保持连接打开时,我的test application的详细输出
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,'{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,'{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,'{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,'{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
