CRBatchMove Performance issues

[Bjarke_Moholt]

I am using Delphi XE ver 1.5 where I have a project using BDE to batch-move a paradox database file into my work database.
I have acquired IBDAC in order to improve the performance of my batchMove but when I compare the two on a two-column table of 10k entries, the BDE batchmover outperforms the IBDAC by almost a factor 10. Closer investigation revealed that the IBDAC batchmover performs at the same level with CommitCount=1 and CommitCount=10k, so I suspect that entries are written 1 at a time in my current setup. Clearly I am doing something wrong and I hope you can help me.

I use a TVirtualTable for source table and a TIBCTable for destination table. The Batchmover is TCRBatchmover.

This is the way I use the batchmover:

Code: Select all

var
  I                    : integer;
  iIBDACBatchMode      : TCRBatchMode;
  SourceTableName      : string;
  DestinationTableName : string;
  before,after         : Integer;
begin

  iIBDACBatchMode := bmAppend;
  
  FIBDACBatchMover.Source      := FIBDACSourceTable;
  FIBDACBatchMover.Destination := FIBDACDestinationTable;
  DestinationTableName       := FIBDACDestinationTable.TableName;
	  
  for i :=0 to FIBDACSourceTable.FieldCount-1 do                        
    FIBDACBatchMover.Mappings.Add(FIBDACSourceTable.FieldDefs[i].Name);  
  if FdbuType in [dbuIB,dbuACCESS97] then
    FIBDACBatchMover.Mode := iIBDACBatchMode
  else
    raise Exception.Create('DBU type not supported: '+GetEnumName(TypeInfo(TdbuType),Ord(FdbuType)));
  try
    before := getTickCount;
    FIBDACBatchMover.Execute;
    after := GetTickCount;
  except
    on E: Exception do raise Exception.Create('Error in batch mover'#10+E.Message);
  end;
end

The settings on the classes are default settings

[AndreyZ]

Hello,

To avoid the problem, you should start transaction explicitly. Here is a code example:

Code: Select all

FIBDACDestinationTable.AutoCommit := False;
FIBDACDestinationTable.Transaction.StartTransaction;
try
  FIBDACBatchMover.Execute;
  FIBDACDestinationTable.Transaction.Commit;
except
  FIBDACDestinationTable.Transaction.Rollback;
end;

[Bjarke_Moholt]

Thank you, that small code snippet effectively doubled the performance

However, the IBDAC is still underperforming compared to my old BDE by a factor 5. Do you have more tips to how I improve the performance?

[AndreyZ]

In this situation, it is better to use the TIBCLoader component, which serves for fast loading of data to the server. Here is a code example:

Code: Select all

IBCLoader1.TableName := 'tablename';
IBCLoader1.LoadFromDataSet(VirtualTable1);

[Bjarke_Moholt]

MUCH better! Performance multiplied by 25 after using the TIBCLoader.
Thank you

[AndreyZ]

You are welcome. If any other questions come up, please contact us.

[Bjarke_Moholt]

Actually, I have one more question:

Running with the TIBCLoader in a single process, I saw how the performance was limited by the CPU available to the process. This lead me to try with multiple threads moving a portion of the table each, but the performance drops as the number of threads increases. Performance investigation reveals how I/O access is now the limiting factor. Apparently, the best option seems to be to run a single thread.

Do you have any advice how to parallelize the commits? I have tried with different modes of IsolationLevel on the transaction I use, but nothing has made a significant difference..

[Bjarke_Moholt]

And another question:

I have increased the number of columns in my table, and I note how the performance of TIBCLoader drops, while that of BDE is relatively unaffected. With a high number of columns there is very little performance to be gained by using the TIBCLoader over the BDE batchmover.
Is there a better way to import larger tables?

[AndreyZ]

Concerning threads, you should try to determine the appropriate number of threads which gives the best performance in your case.
Also, you can try using different values of the TIBCLoader.RowsPerBatch property, it controls the number of records that are sent to the server at once.