Jet Database (ms access) ExecuteNonQuery - Can I make it faster?

Question

Hi all, 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.BeginTransaction()

    '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

Answer 1

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.