Jet Database (ms access) ExecuteNonQuery - Can I make it faster?
I have this generic routine that I wrote that takes a list of sql strings and executes them against the database. Is there any way I can make this work faster? Typically it'll see maybe 200 inserts or deletes or updates at a time. Sometimes there is a mixture of updates, inserts and deletes. Would it be a good idea to separate the queries by type (i.e. group inserts together, then updates and then deletes)?
I am running this against an ms access database and using vb.net 2005.
Public Function ExecuteNonQuery(ByVal sql As List(Of String), ByVal dbConnection as String) As Integer
If sql Is Nothing OrElse sql.Count = 0 Then Return 0
Dim recordCount As Integer = 0
Using connection As New OleDb.OleDbConnection(dbConnection)
connection.Open()
Dim transaction As OleDb.OleDbTransaction = connection.开发者_如何学CBeginTransaction()
'Using cmd As New OleDb.OleDbCommand()
Using cmd As OleDb.OleDbCommand = connection.CreateCommand
cmd.Connection = connection
cmd.Transaction = transaction
For Each s As String In sql
If Not String.IsNullOrEmpty(s) Then
cmd.CommandText = s
recordCount += cmd.ExecuteNonQuery()
End If
Next
transaction.Commit()
End Using
End Using
Return recordCount
End Function
You can use a data adapter to update the whole dataset at once. It will be faster to run the queries on the ADO object than the database directly. After the batch has cycled, update the whole dataset. That might be faster, but will require some extra coding up front and overhead on the application.
精彩评论