Optimizing a large Insert

Optimizing a large Insert

Postby john@haldi.net » Thu 13 Jul 2006 13:05

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
john@haldi.net
 
Posts: 38
Joined: Tue 07 Mar 2006 17:13

Postby Alexey » Thu 13 Jul 2006 14:15

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.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

UpdateRows method

Postby john@haldi.net » Thu 13 Jul 2006 17:09

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."
john@haldi.net
 
Posts: 38
Joined: Tue 07 Mar 2006 17:13

Postby Alexey » Fri 14 Jul 2006 06:30

Yes, you can use this method.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for MySQL