Fast batch copying

[keithblows]

I am copying table data from a MSSQL 2000 database (using SDAC to read the data) to their Firebird V2 counterpart, using IBDAC. What I’m trying to achieve is to batch insert data into the Firebird tables as fast as possible.

I am using a TMSTable component to read the MSSQL data and writing the MSSQL data to a Firebird table using TIBCQuery, with the insert SQL statement being automatically generated by IBDAC.

I have disabled all indexes and triggers on the Firebird database and no other users are using the Firebird database, but I am still finding the copy process to be somewhat sluggish. I use the same code to copy the MSSQL data to a Nexus database and it takes 4 minutes. With IBDAC, the copy process takes 15 minutes, which seems a little slow. Here is what I’m doing with the IBDAC components. Is there any obvious optimizations or settings I'm missing?


lIBConnection := TIBCConnection.Create(Self);
lIBQuery := TIBCQuery.Create(Self);
lIBTransaction := TIBCTransaction.Create(Self);

lIBConnection.Server := '127.0.0.1';
lIBConnection.Username := 'SYSDBA';
lIBConnection.Password := 'masterkey';
lIBConnection.ClientLibrary := 'C:\ActiveAccess\Firebird\fbclient.dll';
lIBConnection.LoginPrompt := False;
lIBConnection.Database := 'ActiveAccess';

lIBTransaction.DefaultConnection := lIBConnection;
lIBTransaction.DefaultCloseAction := taCommit;

lIBQuery.Connection := lIBConnection;
lIBQuery.Options.QuoteNames := True;
lIBQuery.Options.CacheBlobs := False;
lIBQuery.CachedUpdates := False;
lIBQuery.AutoCommit := False;
lIBQuery.UpdateTransaction := lIBTransaction;

// Speed optimizations for batch insertion...
lIBQuery.LockMode := lmNone;
lIBQuery.RefreshOptions := [];
lIBQuery.CachedUpdates := False;
lIBQuery.AutoCommit := False;
lIBQuery.Options.QueryRecCount := False;
lIBQuery.Options.ReturnParams := False;

lIBQuery.SQL.Text := 'SELECT * FROM "xxxx"';
lIBQuery.KeyFields := 'RecID';
lIBQuery.UpdatingTable := AnsiQuotedStr('xxxx', "");
lIBQuery.Prepare;
lIBQuery.Open;

lIBTransaction.StartTransaction;
lRec := 0;
while not ladoDS.Eof do
begin
lIBQuery.Append;
AssignRecord(ladoDS, lIBQuery);
lIBQuery.Post;

Inc(lRec);
// Commit every 5,000 records
if ((lRec mod 5000) = 0) then
begin
lIBTransaction.Commit;
lIBTransaction.StartTransaction;
end;

ladoDS.Next;
end;
lIBTransaction.Commit;
lIBQuery.Close;

Is there anything else I can do to the IBDAC components, to speed the batch insert process up?

AssignRecord looks like:

procedure AssignRecord(Source, Destination: TDataSet);
var
j: Integer;
begin
// Copy the data.
for j := 0 to fc do
begin
if (fi[j] >= 0) then
begin
if Source.Fields[fi[j]].IsNull then
Destination.Fields[j].Clear
else
Destination.Fields[j].Assign(Source.Fields[fi[j]]);
end;
end;
end;

[Alex]

There are several possibilities to optimize your procedure:
1. As I can see, you use local connection. In this case removing of "lIBConnection.Server := '127.0.0.1';" line from your code
will increase performance in several times
2. Removing of "lIBQuery.Options.CacheBlobs := False;" line from your code will slightly increase performance.
3. You can increase performance by adding "lIBQuery.Options.StrictUpdate := False;", this wil prevent IBDAC from
getting affected records after each update.
4. The best way is to use lIBQuery with SQL filled with the "INSERT INTO ..." statement and assign ladoDS fields values to
lIBQuery prarmeters. E.g.:

Code: Select all

lIBQuery.AutoCommit := False;
lIBQuery.Transaction := lIBTransaction;
lIBQuery.SQL.Text := 'INSERT INTO IB_MIGRATION' +    // Use INSERT SQL that corresponds to your table
                     '(ID, VCH_VALUE, INT_VALUE)' +
                     ' VALUES (:ID, :VCH_VALUE, :INT_VALUE)';
lIBQuery.Prepare;
lIBTransaction.StartTransaction;
lRec := 0;
while not ladoDS.Eof do
  begin
   AssignParams(ladoDS, lIBQuery);
   lIBQuery.Execute;

   Inc(lRec);
  // Commit every 5,000 records
   if ((lRec mod 5000) = 0) then begin
    lIBTransaction.Commit;
    lIBTransaction.StartTransaction;
   end;
   ladoDS.Next;
end;
if not lIBQuery.Transaction.Active then
 lIBQuery.Transaction.Commit;

procedure AssignParams(Source, Destination: TDataSet);
var
  j: Integer;
begin
  //We suppose that Source and Destination tables are identical and Destination.Params has
  //direct map to Source fields
  for j := 0 to Source.FieldCount - 1 do begin
    if Source.Fields[j].IsNull then
      TCustomIBCDataSet(Destination).Params[j].Clear
    else
      TCustomIBCDataSet(Destination).Params[j].AssignFieldValue(Source.Fields[j], Source.Fields[j].Value);
  end;
end;