星期一, 9月 22, 2008

OLEDB比ODBC快呀!

看了李維回覆RURU有關ODBC, OLEDB, ADO和dbExpress的問題,想起VB超人告訴過我OLEDB比較快,因為ODBC多了一層呼叫。

雖然李維很強(是高手中的高手呀),但他對微軟資料庫的歷史似乎不是完全正確,記得微軟最早提出RDO,後來才有ADO的出現,而ADO裏再分OLEDB與ODBC。

以前有別人測過,但以讀取為主。(請看

所以在下敝人小弟我,用VB6寫了一個很破的測試程式,為避免cache,就只寫不讀取,完全避開Server的Cache機制,不然比較晚讀取的連線一定比較快呀!

測試方法簡述如下:兩個程式架構完全相同,對同一個DB寫入十萬次,執行前先truncate table。

兩次測試結果相同:OLEDB花了1分16秒,ODBC花了1分21秒。

結論:其他Database不敢說,但在MS SQLServer的情況下,OLEDB比ODBC快!

Update:一樣的程式,原測試環境在Win2000 VM,剛才心血來潮在Vista 64上執行,居然差不多。微軟在不同OS上可能有許多不同的tuning與cache;所以對於這種非程式技巧的效能,還是別研究了:P

原始碼:
Private Sub OleDBTest()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sql As String
Dim before As Date
Dim after As Date
Dim duration As Date
Dim connstr As String
connstr = "Provider=SQLOLEDB;" & _
"Data Source=MySQLServer;" & _
"Initial Catalog=TestDB;" & _
"User ID=sa;" & _
"Password=sa;"
before = DateTime.Now
Set conn = New ADODB.Connection
conn.Open (connstr)
Dim i As Long
For i = 1 To 100000
sql = "INSERT INTO TestTable Values('" & CStr(i) & "')"
conn.Execute (sql)
Next i
after = DateTime.Now

conn.Close
Set conn = Nothing
duration = after - before
Label1.Caption = ""
Label1.Caption = "before:" & CStr(before) & _
vbCrLf & _
"after:" & CStr(after) & _
vbCrLf & _
"duration:" & CStr(duration)
End Sub

Private Sub ODBCTest()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sql As String
Dim before As Date
Dim after As Date
Dim duration As Date
Dim connstr As String

connstr = "Driver={SQL Server};" & _
"Server=MySQLServer;" & _
"Database=TestDB;" & _
"UID=sa;" & _
"PWD=sa;"

before = DateTime.Now
Set conn = New ADODB.Connection
conn.Open (connstr)
Dim i As Long
For i = 1 To 100000
sql = "INSERT INTO TestTable Values('" & CStr(i) & "')"
conn.Execute (sql)
Next i
after = DateTime.Now

conn.Close
Set conn = Nothing
duration = after - before
Label1.Caption = ""
Label1.Caption = "before:" & CStr(before) & _
vbCrLf & _
"after:" & CStr(after) & _
vbCrLf & _
"duration:" & CStr(duration)
End Sub


最後Update:VB超人最近接某個案子剛好在Oracle 11g轉檔,他表示Oracle odbc 插入一萬筆要51秒,而OleDB只要14秒,用Oracle donet client 只要八秒,在XP下作業。請看VB超人提供的測試結果圖:(測試案例稍有不同)

2 則留言:

匿名 提到...

其實 DBMS 這玩了幾十年的玩意兒, 存取方式再變換, 也不過就是換湯不換藥.

記得以前看到 Oracle 文件, 講到 embedded SQL (Pro*C) 和 plain library (sqllib) 之間的差異時, 提到 Pro*C 可以叫到一些 private sqllib function, 所以效率不見得會差.

對照來看, 取「公約數」的最好實作方式就是包裝別人的元件, 那誰會比較快呢? 應該不用再問了.

Kun-Yi 提到...

記憶所及ODBC 比較早發展, MSFT原本想推Cross Platform Standard ref. http://en.wikipedia.org/wiki/Open_Database_Connectivity

MSFT先後推出 ODBC/RDO/ADO,
然後實際效率要看搭的Database
http://en.wikipedia.org/wiki/Image:MDAC_Architecture.svg

透過MSFT的ADO 連OLEDB會比連ODBC快
ODBC/OLEDB的實做會影響效率的