Optimizing a large Insert

[[email protected]]

I have a program which goes through a collection of tables every evening and archives the day's data by appending it to historical tables on another database (on another server).

Everything I've written works fine, except that for the larger tables it is very slow. Here is the routine I call which handles the archiving (I've stripped extraneous code and error handling to make it easier to read):

Private Sub AppendData(ByRef CurrentTable As MySqlDataTable, ByRef HistoricalTable As MySqlDataTable)

Dim CurrRow As DataRow

For Each CurrRow In CurrentTable.Rows

Dim HistRow As DataRow = HistoricalTable.NewRow
Dim CurrCol As DataColumn

For Each CurrCol In HistoricalTable.Columns
If CurrCol.ColumnName "trade_date" And CurrCol.ColumnName "id" Then
HistRow.Item(CurrCol.ColumnName) = CurrRow.Item(CurrCol.ColumnName)
End If
Next

HistRow.Item("trade_date") = Date.Now

HistoricalTable.Rows.Add(HistRow)

Next

HistoricalTable.Update()

End Sub

Can you recommend any ways to optimize this code? For tables with less than 500 records it works fairly quickly, but on tables with 2,500+ records it really drags.

Some questions:

1. Is it better to pass tables into a routine ByRef or ByVal?
2. Is it better to issue an update for large tables at the end of all the Add methods, or better to flush any cache by issuing the Update command after each Add method?
3. Can the code as I've written it be modified to use UpdateRows and thus see performance gains?

Many thanks,

John

[Alexey]

1. Is it better to pass tables into a routine ByRef or ByVal?
2. Is it better to issue an update for large tables at the end of all the Add methods, or better to flush any cache by issuing the Update command after each Add method?
3. Can the code as I've written it be modified to use UpdateRows and thus see performance gains?

1. Does not matter.
2. Issue Update only once.
3. What is meant by "UpdateRows"?

Also we recommend you to use Profiler to find out what is the "bottle neck" of your project.

[[email protected]]

The documentation makes mention of the UpdateRows method, whereby one can create an array of new datarows and specify that those rows be updated all at once.

Per the documentation:

"Remarks
Using this method may result in performance gains, for example, on very big tables."

[Alexey]

Yes, you can use this method.