locktable and commit

mikest

I have over a million records in this newtable. new status need to be saved after every 1000 records.
Database is NAV 4SP3 with SQL 2005.

And here is the code from the codeunit we are running

---

NewTable.RESET;
NewTable.SETCURRENTKEY(status);
NewTable.SETRANGE(status,NewTable.status::inactive);
IF NewTable.FINDSET(TRUE,TRUE) THEN //Navision does a locktable here

repeat
newtablecopy := newtable;
//some calculations here
.....................................
....................................
//end of caluclations
newtablecopy.status := NewTablecopy.status::active;
newtablecopy.modify;
counter := counter + 1;
if counter = 1000 then begin
counter := 0;
commit;
// The question is
//how do I lock the table again and still be in the same loop
// Is newtable.locktable good enough?
end;
until newtable.next = 0;

---

The question is
how do I lock the table again after commit and still be in the same loop
Is newtable.locktable good enough?


Cheers,
Mike

leugim

it's enough, the locktable sentence you use manually is the same Navision does.

kine

One remark: modifying copy of the record doesn't help from "next from hell" anymore. Better is to add the modifyed records into temporary table and after to save them in second loop. And it will help you much more in NAV 5.0SP1 with the delayed insert.

And if you are reding more than 500 records, better is to use LOCKTABLE and FIND('-'), because FINDSET is optimal for sets smaller than 500 records (depends on the settings in dtabase properties).