今天寫了一支簡單的測試程式目的為了解自己目前所使用資料庫之效能, 並希望能從中調整出更好的結果
底下是多次測試的結果
測試環境: Windows2003, .NET Framework 1.1, no AntiVirus, 1GB memory
測試軟體: SQLite V3, MySQL Type=InnoDB, SQL Server 2000 Sp3, Access 20003
參數設定: MySQL (預設*2)
max_allowed_packet=16M
key_buffer_size=16M
table_cache=128
sort_buffer_size=4M
其餘資料庫均為預設值
連線字串:
Private MSSQLConnStr As String = "initial catalog=CodeLib;Connect Timeout=20;data source=(local);integrated security=SSPI;persist security info=False;"
Private MySQLODBCConnStr As String = "DRIVER={MySQL ODBC 3.51 Driver};DATABASE=CodeLib;OPTION=18475;SERVER=localhost;UID=;PASSWORD=;"
Private MySQLByteFXConnStr As String = "Database=CodeLib;Data Source=localhost;User Id=;Password="
Private OLEDBConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=1.mdb"
Private SQLiteConnStr As String = "Data Source=;New=True;Version=3"
(INSERT with Transaction 10000筆)
00.6089(SQLite) < 01.1677(MSSQL) < 02.5346(ByteFx) < 08.9000(Access) < 13.9242(MyODBC) 0~10000
00.5476(SQLite) < 01.2679(MSSQL) < 02.4982(ByteFx) < 08.6672(Access) < 13.5140(MyODBC) 10000~20000
00.5245(SQLite) < 01.1508(MSSQL) < 02.5150(ByteFx) < 08.7101(Access) < 13.6411(MyODBC) 20000~30000
(INSERT with Transaction 50000筆)
03.4739(SQLite) < 09.4173(MSSQL) < 13.6408(ByteFx) < 44.3165(Access) < 71.6529(MyODBC) 50000
(INSERT without Transaction 300筆)
00.0908(MSSQL) < 00.3520(Access) < 10.3717(MyODBC) < 11.5118(ByteFx) < 25.8230(SQLite) 30000~30300
00.0907(MSSQL) < 00.3686(Access) < 08.7148(ByteFx) < 11.3625(MyODBC) < 25.1898(SQLite) 30300~30600
00.1027(MSSQL) < 00.3595(Access) < 08.4664(ByteFx) < 08.7934(MyODBC) < 25.3364(SQLite) 30600~31200
(SELECT 31200筆)
00.4161(SQLite) < 00.8362(MSSQL) < 00.8688(Access) < 02.3565(MyODBC) < 02.4857(ByteFx)
00.3519(SQLite) < 00.5707(Access) < 00.7055(MSSQL) < 02.5555(MyODBC) < 02.6173(ByteFx)
00.3472(SQLite) < 00.6037(MSSQL) < 00.6707(Access) < 02.5439(ByteFx) < 02.6328(MyODBC)
(SELECT 50000筆)
00.6129(SQLite) < 01.0132(MSSQL) < 01.2942(Access) < 04.0064(ByteFx) < 04.4904(MyODBC)
00.6741(SQLite) < 00.9719(MSSQL) < 01.2956(Access) < 03.7336(ByteFx) < 04.6592(MyODBC)
(DELETE 31200筆)
00.0091(Access) < 00.0247(ByteFx) < 00.1960(SQLite) < 00.6901(MSSQL) < 01.2297(MyODBC)
(DELETE 50000筆)
00.0092(Access) < 00.0301(ByteFx) < 00.3305(SQLite) < 01.9281(MyODBC) < 03.0269(MSSQL)
SQLite3 在不執行交易的情況下不是很理想, 但SELECT速度相當快
MSSQL/Access無論是否執行交易效能都相當不錯
MySQL的ByteFx與MyODBC在Insert/Delete的速度上差異略為明顯
以上數據僅供參考, 如果您對結果有更好的建議也請讓我知道.