Performance when filtering huge records

Andwian · 2010-10-21

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.

ErictP · 2010-10-21

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

Shedman · 2010-10-21

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.

FDickschat · 2010-10-21

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.

Shedman · 2010-10-21

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.

DenSter · 2010-10-21

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.

Shedman · 2010-10-21

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

Andwian · 2010-10-22

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.

kine · 2010-10-23

recCustLedgerEntries.SETFILTER("Remaining Amt. (LCY)",'>0');

Isn't "Remainging Amt. (LCY)" calculated field? ;-) Filtering on it will be each time time-consuming...

Andwian · 2010-10-25

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.

Andwian · 2010-12-30

DenSter wrote:

Actually, you should specify the exact key as it exist on the table.

What does it mean? :?

einsTeIn.NET · 2010-12-30

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.

Andwian · 2010-12-30

einsTeIn.NET wrote:
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

einsTeIn.NET wrote:

That might turn to a significant loss of performance.

Anyway, is it about next future performance, or just "that time" performance?

einsTeIn.NET · 2010-12-30

Andwian wrote:

Anyway, is it about next future performance, or just "that time" performance?

Each time you execute that SETCURRENTKEY statement.

Andwian · 2010-12-31

einsTeIn.NET wrote:

Each time you execute that SETCURRENTKEY statement.

I understand now.

Danke, einsTeIn.NET!
Glückliches neues Jahr

Performance when filtering huge records

Comments

Categories