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超人提供的測試結果圖：（測試案例稍有不同）