Reducing locking

BorisK

Imagine you have a table, let's call it MyRec, primary key "Entry No."
You run some kind of processing on it, and when an entry gets processed a temporary entry TempMyRec is inserted with the same Entry No.
When the processing has finished you want to update your MyRec with "Processed" flag.
The most obvious way of doing it (to me) would be
IF TempMyRec.FINDSET THEN REPEAT
MyRec.GET(TempMyRec."Entry No.");
MyRec.Processed := TRUE;
MyRec.MODIFY;
UNTIL TempMyRec.NEXT = 0;

Slight problem is if you have millions of records this may lock MyRec table for long time causing problems for other users.
So I thought of an alternative:
IF TempMyRec.FINDSET THEN REPEAT
MyRec.GET(TempMyRec."Entry No.");
MyRec.MARK(TRUE);
UNTIL TempMyRec.NEXT = 0;
MyRec.MARKEDONLY;
MyRec.MODIFYALL(Processed,TRUE);

Question: will this lock the table for shorter time or will it not make any difference?

Slawek_Guzek

Hi,

It may take possibly much much longer as the filtering based on MARKed records is getting very slow with begger number of records. Or at least it used to in versions pre-NAV2013.

Since you are now on RTC you have Text variable available which can be VERY long. I would try to build a filter for Entry No. field. The simple version would be Entry1|Entry2|Entry3.. and so on, then apply this filter to the MyRec table and use MODIFYALL.

Slawek

Rebogh

As far as i know, SQL server has maximum no of parameters (2100).
Therefore, you cannot add Entry No in text filter for large tables.
MODIFYALL could save some time.
Usually, all such modifications are scheduled during non-working hours with job queue.
And with log entries you could further analyze how much time it takes for each of these solutions.

xStepa

Maybe you can try to change the physical structure of the table using different Clustered key - this could reduce locking on the same pages.

clustered key GUID - can help, will store entries out-of-order
clustered key USERID, Entry No. - will keep blocks of records by users (don't know if possible in your case)
or create another key, which will make blocks more user dependend ...

Duikmeester

Why not MyRec.Processed := TRUE + MODIFY instead of Temping it? And then every 100 recs or so give it a COMMIT

Slawek_Guzek

Rebogh wrote: »

As far as I know, SQL server has maximum no of parameters (2100).

That is actually true. The string having 2101 Entry Nos separated by the | char would fail.

In 'advanced' version of the filter, the filter expression could be built by a function like GetSelectionFilter in C46 in NAV 2017 (it was on the page/form 18 in earlier versions), which compresses consecutive PK values and could give much shorter (and more effective) expressions like Entry1|Entry3...Entry10|Entry11.. Which of course also does not guarantee that the parameter limit would not be hit

Perhaps the simplest solution as suggested by Duikmeester would be the best