RecordRef performance issue.

aljosagr · 2009-04-16

Hi guys,

I have a strange problem while using RedordRef in a function to copy data from one company to another. The problem is that the function exponentionally slows down.
I know that the solution will be not to use RecordRef but i need it done in this way. The issue become visible when there are 20.000 records and more in the table.

Window.OPEN('Progress\@1@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@');
TableID := DATABASE::"G/L Entry";
FromRecRef.OPEN(TableID,FALSE,'COMPANY1');
ToRecRef.OPEN(TableID,FALSE,'COMPANY2');

AllEntries := FromRecRef.COUNT;
CurrEntry := 0;
IF NOT FromRecRef.FIND('-') THEN
  EXIT;

FieldsCount := FromRecRef.FIELDCOUNT;
REPEAT
  CurrEntry += 1;
  PercentDone := CurrEntry / AllEntries;
  PercentDone *= 10000;
  Window.UPDATE(1,ROUND(PercentDone,1));
  
  ToRecRef.INIT;
  FOR CurrFieldIndex := 1 TO FieldsCount DO
    BEGIN
      FromFieldRef := FromRecRef.FIELDINDEX(CurrFieldIndex);
      ToFieldRef := ToRecRef.FIELDINDEX(CurrFieldIndex);
      ToFieldRef.VALUE := FromFieldRef.VALUE;
    END;

  ToRecRef.INSERT;
UNTIL FromRecRef.NEXT=0;
Window.CLOSE;

Does anybody have an idea how to solve this issue?

Thanks in advance for any suggestions,

ara3n · 2009-04-16

what is the memory usage during the process for the cleint?

Also have you tried and used newer version of the client to compare the performance.

I'm guessing you are comparing it to a record variable.

What is the time difference betwen recordref and record for 20K records?

canadian_bacon · 2009-04-16

after the OPEN commands try:

FromRecRef.SETVIEW;
ToRecRef.SETVIEW;

and in your loop try:

ToRecRef.FIELDINDEX(CurrFieldIndex).VALUE := FromRecRef.FIELDINDEX(CurrFieldIndex).VALUE

Spawn · 2009-04-17

As I can see you are using COUNT and assign it to a variable which is used only for updating progress bar.
So my recommendation here is to use COUNTAPPROX instead of COUNT. It should speed up the process.

Also you can move this calculation below the exit criteria check. The code will look like this:
IF NOT FromRecRef.FIND('-') THEN
EXIT;
AllEntries := FromRecRef.COUNTAPPROX;
CurrEntry := 0;

ara3n · 2009-04-17

The count is called only once. The performance difference is between using record and recordRef.

kine · 2009-04-17

Do not forget that commiting the data after some count of inserted records can speed the thing up. But of course only if you can split the transfer into separate transactions. For big count of insert the transaction grows up and it can be the source of problems...

RecordRef performance issue.

Comments

Categories