Options

Performance when filtering huge records

AndwianAndwian Member Posts: 627
Dear Experts,

I have a very huge number of Customer Ledger Entries, and these lines of codes:
ReleaseSalesDoc - OnRun
...
      recCustLedgerEntries.RESET;
      recCustLedgerEntries.SETRANGE("Customer No.","Sell-to Customer No.");
      recCustLedgerEntries.SETFILTER("Due Date",'<%1',"Document Date"));
      recCustLedgerEntries.SETFILTER("Remaining Amt. (LCY)",'>0');
      IF recCustLedgerEntries.FINDFIRST THEN
...

It will take about 15 secs. to do a Release Document.

I have also tried to change the use of FINDFIRST with recCustLedgerEntries.ISEMPTY, but no significant difference in performance.

Then I try this way:
      recCust.GET(Rec."Sell-to Customer No.");
      IF recCust.GETFILTER("Date Filter") = '' THEN
        recCust.SETFILTER("Date Filter",'..%1',"Document Date");
      recCust.CALCFIELDS("Balance Due (LCY)");
      IF recCust."Balance Due (LCY)" > 0 THEN

This way, the running time is about 5 secs.

Any other brilliant ideas to get a better performance?
I am afraid with this very rapid growth records in this table in the future will lead to slow down in performance.

Thank you.
Regards,
Andwian

Comments

  • Options
    ErictPErictP Member Posts: 164
    Try this
          recCustLedgerEntries.RESET;
          recCustLedgerEntries.SETCURRENTKEY("Customer No.", Open, Positive, "Due Date", "Currency Code");
          recCustLedgerEntries.SETRANGE("Customer No.","Sell-to Customer No.");
          recCustLedgerEntries.SETRANGE(Open, TRUE);
          recCustLedgerEntries.SETFILTER("Due Date",'<%1',"Document Date"));
          recCustLedgerEntries.SETFILTER("Remaining Amt. (LCY)",'>0');
          IF recCustLedgerEntries.FINDFIRST THEN
    
  • Options
    ShedmanShedman Member Posts: 194
    What would be interesting to know is what happens after the FINDFIRST in the first code snippet. If you really use the first record for something you can use FINDFIRST. If you only use it to find out if records exist, you should use NOT ISEMPTY for better performance.
  • Options
    FDickschatFDickschat Member Posts: 380
    As Eric P suggested: As a gernal rule: Always use SETCURRENTKEY.

    It depends very much on your NAV version/Build and the version of SQL server (if you use it) how slow or fast it will be without the SETCURRENTKEY.

    Also: In a SETCURRENTKEY always use the full key and not only the first 2 or 3 fields.
    Frank Dickschat
    FD Consulting
  • Options
    ShedmanShedman Member Posts: 194
    FDickschat wrote:
    [...]
    Also: In a SETCURRENTKEY always use the full key and not only the first 2 or 3 fields.
    I wouldn't use all fields, but include all fields that your are filtering on. That way if fields are removed from or added to your key, you don't have to change all references to that key.
  • Options
    DenSterDenSter Member Posts: 8,304
    Shedman wrote:
    I wouldn't use all fields, but include all fields that your are filtering on. That way if fields are removed from or added to your key, you don't have to change all references to that key.
    Actually, you should specify the exact key as it exist on the table. When you don't specify all fields of the key, SQL Server might revert to a clustered index scan, which is never good for performance.
  • Options
    ShedmanShedman Member Posts: 194
    DenSter wrote:
    [...]
    Actually, you should specify the exact key as it exist on the table. When you don't specify all fields of the key, SQL Server might revert to a clustered index scan, which is never good for performance.
    That's good to know :D
  • Options
    AndwianAndwian Member Posts: 627
    Shedman wrote:
    What would be interesting to know is what happens after the FINDFIRST in the first code snippet. If you really use the first record for something you can use FINDFIRST. If you only use it to find out if records exist, you should use NOT ISEMPTY for better performance.
    Andwian wrote:
    I have also tried to change the use of FINDFIRST with recCustLedgerEntries.ISEMPTY, but no significant difference in performance.
    FDickschat wrote:
    As Eric P suggested: As a gernal rule: Always use SETCURRENTKEY.

    It depends very much on your NAV version/Build and the version of SQL server (if you use it) how slow or fast it will be without the SETCURRENTKEY.

    Also: In a SETCURRENTKEY always use the full key and not only the first 2 or 3 fields.
    So the SETCURRENTKEY really have an impact to the filtering process? I ever read about it and forgot to use that :)
    Shedman wrote:
    Actually, you should specify the exact key as it exist on the table. When you don't specify all fields of the key, SQL Server might revert to a clustered index scan, which is never good for performance.
    Thank you all for sharing. I will give it a try. :D
    Regards,
    Andwian
  • Options
    kinekine Member Posts: 12,562
    recCustLedgerEntries.SETFILTER("Remaining Amt. (LCY)",'>0');

    Isn't "Remainging Amt. (LCY)" calculated field? ;-) Filtering on it will be each time time-consuming...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    AndwianAndwian Member Posts: 627
    kine wrote:
    Isn't "Remainging Amt. (LCY)" calculated field? Filtering on it will be each time time-consuming...
    That's right. Thank you for pointing that out. :wink:
    Regards,
    Andwian
  • Options
    AndwianAndwian Member Posts: 627
    DenSter wrote:
    Actually, you should specify the exact key as it exist on the table.
    What does it mean? :?
    Regards,
    Andwian
  • Options
    einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    That means e.g. if your key is "No.",Open,"Posting Group","Posting Date" then it is possible to use only the the first field(s) like
    SETCURRENTKEY("No.",Open);
    
    You could omit some of the last fields of the key. NAV will search for the right key that matches with the given fields. But on SQL the server possibly will use a clustered index scan. That might turn to a significant loss of performance.
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • Options
    AndwianAndwian Member Posts: 627
    That means e.g. if your key is "No.",Open,"Posting Group","Posting Date" then it is possible to use only the the first field(s) like
    SETCURRENTKEY("No.",Open);
    
    You could omit some of the last fields of the key. NAV will search for the right key that matches with the given fields. But on SQL the server possibly will use a clustered index scan. That might turn to a significant loss of performance.
    Thank you Einstein for your explanation. I understand now :D
    That might turn to a significant loss of performance.
    Anyway, is it about next future performance, or just "that time" performance?
    Regards,
    Andwian
  • Options
    einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    Andwian wrote:
    Anyway, is it about next future performance, or just "that time" performance?
    Each time you execute that SETCURRENTKEY statement.
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • Options
    AndwianAndwian Member Posts: 627
    Each time you execute that SETCURRENTKEY statement.
    I understand now.

    Danke, einsTeIn.NET!
    Glückliches neues Jahr
    Regards,
    Andwian
Sign In or Register to comment.