Options

Reducing locking

BorisKBorisK Member Posts: 45
edited 2017-10-04 in NAV Three Tier
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?

Answers

  • Options
    Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    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
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • Options
    ReboghRebogh Member Posts: 13
    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.
  • Options
    xStepaxStepa Member Posts: 106
    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 ...
    Regards
    xStepa
  • Options
    DuikmeesterDuikmeester Member Posts: 304
    Why not MyRec.Processed := TRUE + MODIFY instead of Temping it? And then every 100 recs or so give it a COMMIT
  • Options
    Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    edited 2017-10-05
    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
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Sign In or Register to comment.