Devart Forums

Posted: **Thu 21 Nov 2013 16:13**

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

Posted: **Fri 22 Nov 2013 09:56**

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;

Posted: **Fri 22 Nov 2013 13:57**

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?

Posted: **Tue 26 Nov 2013 11:10**

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);

Posted: **Thu 28 Nov 2013 14:36**

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

Posted: **Thu 28 Nov 2013 15:53**

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

Posted: **Tue 03 Dec 2013 14:43**

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..

Posted: **Wed 04 Dec 2013 10:41**

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?

Posted: **Wed 04 Dec 2013 10:51**

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.

Devart Forums

CRBatchMove Performance issues

CRBatchMove Performance issues

Re: CRBatchMove Performance issues

Re: CRBatchMove Performance issues

Re: CRBatchMove Performance issues

Re: CRBatchMove Performance issues

Re: CRBatchMove Performance issues

Re: CRBatchMove Performance issues

Re: CRBatchMove Performance issues

Re: CRBatchMove Performance issues