資料庫存取速度提升

我們通常使用索引來提升SQL指令的執行速度, 其實做大量資料更新時還有一個簡單的地方可以提升速度, 就是使用交易(Transaction), 新增一萬筆資料至MS SQL Server, 大約快了三倍多, 測試程式如下:
1. 不使用交易
Dim cnn As New SqlConnection("server=.;database=sampledb;integrated security=sspi;")
cnn.Open()

Dim MyWatch As New Stopwatch
MyWatch.Start()
Dim mycommand As New SqlCommand()
mycommand.Connection = cnn
Dim n As Integer
Try
mycommand.CommandText = "truncate TABLE ex1;"
mycommand.ExecuteNonQuery()
Catch
End Try

For n = 0 To 10000 - 1
mycommand.CommandText = String.Format("INSERT INTO ex1 (a,b,c) VALUES('a','c',{0})", n + 1)
mycommand.ExecuteNonQuery()
Next
MsgBox("MyWatch.ElapsedMilliseconds=" & MyWatch.ElapsedMilliseconds)

2. 使用交易
Dim cnn As New SqlConnection("server=.;database=sampledb;integrated security=sspi;")
cnn.Open()

Dim MyWatch As New Stopwatch
MyWatch.Start()
Dim mycommand As New SqlCommand()
Dim mytransaction As SqlTransaction = cnn.BeginTransaction
mycommand.Connection = cnn
mycommand.Transaction = mytransaction
Dim n As Integer
Try
mycommand.CommandText = "truncate TABLE ex1;"
mycommand.ExecuteNonQuery()
Catch
End Try

' use transaction to make the process more quick
For n = 0 To 10000 - 1
mycommand.CommandText = String.Format("INSERT INTO ex1 (a,b,c) VALUES('a','c',{0})", n + 1)
mycommand.ExecuteNonQuery()
Next
mytransaction.Commit()
MsgBox("MyWatch.ElapsedMilliseconds=" & MyWatch.ElapsedMilliseconds)

3. 不使用交易, 但採用批次更新, 比第一種快1/5
Dim cnn As New SqlConnection("server=.;database=sampledb;integrated security=sspi;")
cnn.Open()

Dim MyWatch As New Stopwatch
MyWatch.Start()
Dim mycommand As New SqlCommand()
mycommand.Connection = cnn
Dim n As Integer
Try
mycommand.CommandText = "truncate TABLE ex1;"
mycommand.ExecuteNonQuery()
Catch
End Try

Dim da1 As New SqlDataAdapter()
da1.InsertCommand = mycommand
da1.UpdateBatchSize = 10000
For n = 0 To 100000 - 1
mycommand.CommandText = String.Format("INSERT INTO ex1 (a,b,c) VALUES('a','c',{0})", n + 1)
da1.InsertCommand.ExecuteNonQuery()
Next
MsgBox("MyWatch.ElapsedMilliseconds=" & MyWatch.ElapsedMilliseconds)