Making RENAME function Fast.

kolaboy

Hi All,
I have this codeunite that is taking very long time to run, infact i have waited for over five hours and it has never end.
This codeunite is suppose to rename employee nos. which are also linked to five other tables. The employee table has over 99000 records and another with over 1 million records and others have records too.

how can we make this codeunite work fast using the RENAME function.

recEmployee.RESET;

recEmployee.SETFILTER("No.",'?????'); // codes are always 5 characters

 IF recEmployee.FIND('-') THEN

  REPEAT

    recEmployee2 := recEmployee;

    recEmployee2."SSNo." := recEmployee2."No.";

    CASE COPYSTR(recEmployee."No.",1,1) OF

      'A': codNewNo := '01';

      'B': codNewNo := '02';

      'C': codNewNo := '03';

      'D': codNewNo := '04';

      'E': codNewNo := '05';

      'F': codNewNo := '06';

      'G': codNewNo := '07';

      'H': codNewNo := '08';

      'I': codNewNo := '09';

      'J': codNewNo := '10';

      'K': codNewNo := '11';

      'L': codNewNo := '12';

      'M': codNewNo := '13';

      'N': codNewNo := '14';

      'O': codNewNo := '15';

      'P': codNewNo := '16';

      'Q': codNewNo := '17';

      'R': codNewNo := '18';

      'S': codNewNo := '19';

      'T': codNewNo := '20';

      'U': codNewNo := '21';

      'V': codNewNo := '22';

      'W': codNewNo := '23';

      'X': codNewNo := '24';

      'Y': codNewNo := '25';

      'Z': codNewNo := '26';

    END;

    recEmployee2.RENAME(codNewNo + COPYSTR(recEmployee."No.",2));

    COMMIT; // this commit can be useful in case the process is interupted. With this you can continue where it left of

                   // the records already renamed will NOT be renamed again because of the SETFILTER("No.",...)

  UNTIL recEmployee.NEXT = 0;

](*,)
Any idea on how to make it fast.
Thanks.

garak

faster than an rename is delete the record an insert the record new. But in these scenario, u must also modify all the value in fields in other tables which have an relation to the employee table.

example:

OldEmplRec := CurrEmplRec.
CurrEmplRec.delete;
NewEmplRec := OldEmplRec;
NewEmplRec.PrimaryKeyField := NewValue;
NewEmplRec.insert;


RelationTable.setrange(RelationFieldOnEmployeeTable,OldEmplRec.PrimaryKeyField);
if RelationTables.find('-') then begin
  repeat
    RelationTables2.get(RelationTables.PrimaryKey);
    RelationTables2.RelationFieldOnEmployeeTable := NewEmplRec.PrimaryKeyField;
    RelationTables2.modify;
  until RelationTables.next = 0;
end;

Xypher

Hey kolaboy,

How does this look to you... ? 8-[

Appears to work for me.

recEmployee.RESET;

recEmployee.SETFILTER("No.",'?????');

 IF recEmployee.FIND('-') THEN

  REPEAT

    recEmployee2 := recEmployee;

    recEmployee2."SSNo." := recEmployee2."No.";

//Changed Code  ******

    Mask := '%1';                    //Local Variable Text

    chrVar := recEmployee."No."[1];  //Local Variable Char

    intVar := chrVar - 64;           //Local Variable Integer

    IF intVar < 10 THEN
      Mask := '0' + Mask;            //It appears you want a double digit view of the number even if the resultant is a single digit number


    recEmployee2.RENAME(STRSUBSTNO(Mask,intVar) + COPYSTR(recEmployee."No.",2));    

//********************

    COMMIT;

  UNTIL recEmployee.NEXT = 0;

kine

Are you using Native or SQL?

Generating number from first character we solved in another post... ;-)

You can "speed up" the process by temporary removing all the Employee.No. table relations on fields you are not using. But you need to know what you are doing...

Savatage

kine wrote:

Generating number from first character we solved in another post... ;-)

kine wrote:

Hey guys, why you are doing it in this hard way? You need just one line of code...

b := FORMAT(a[1]-'A'+1,2,'<integer,2><filler character,0>')+COPYSTR(a,2);

a - is variable of type Code with the original string.
b - is variable of type Code with result

Do you know how it works? I will let you some time to think about it, if you want I can describe it for the rest, who do not understand...

tinoruijs

kine wrote:

You can "speed up" the process by temporary removing all the Employee.No. table relations on fields you are not using. But you need to know what you are doing...

If you're going to remove all TableRelations in other related tables, You can loop through the related tables on their primary key.

If for example a employee is connected to a job (employee no. is present in the job-table) you have to delete the TableRelation on the employee no..
First rename the employee table.
Then loop through the job-table and look in the just renamed employee table if there is a record which contains the old employee no..
If you find that record, use the new employee no. in the job-table and modify the job-table.

Like Kamil said; You need to know what you're doing.
Good luck!

kolaboy

Xypher i have tried your code but it also slow those not as slow is the first on. I would prefer a faster one.

kine i am using Native database on 4.0

Any other fast suggestion?
Thanks

tinoruijs

The solution Kamil and I suggested makes Rename faster..

You could take a look how many fields have a tablerelation to the employee table.

You can do this by creating a form based on table Field. Just create it by pressing New in the Object Designer and add all fields to the form.
Put a filter on RelationTableNo (filter is the ID of the employee table).

If there are a lot of fields in that filter and the tables are filled, then that's probably the reason the Rename isn't fast.

zulq

You can "speed up" the process by temporary removing all the Employee.No. table relations on fields you are not using. But you need to know what you are doing...

Kine how can we achieve this?


Thanks.

kine

zulq wrote:

You can "speed up" the process by temporary removing all the Employee.No. table relations on fields you are not using. But you need to know what you are doing...

Kine how can we achieve this?


Thanks.

It is just manual work and you need to have developer license (be a partner or have AB or SD license). If you do not know how to do that, than it will be better to not doing that and find someone who will do that for you...

garak

for Kine solution you must have the developer license and if you do this, you must know what you are do.

An other way is, store temporary, delete (without true), insert (without true) tempory back with new primary key.
After this u must modify all tables (and there records in an loop) which has an relation to the old empl. number.

To change the value of the empl. No, u can use the format function.

Regards

tinoruijs

garak wrote:

for Kine solution you must have the developer license and if you do this, you must know what you are do.

An other way is, store temporary, delete (without true), insert (without true) tempory back with new primary key.
After this u must modify all tables (and there records in an loop) which has an relation to the old empl. number.

To change the value of the empl. No, u can use the format function.

Regards

I think the solution Garak suggests, is the safest solution.

garak

and it was the first anwser