SETCURRENTKEY with SETRANGE and SETFILTER

jordi79

Hi,
I know in the old NAV before NAV2009, SETCURRENTKEY is called before SETRANGE/SETFILTER to improve filter performance. Does this still stand true for later versions of NAV? NAV2017? NAV2018?
Jordi

cvanderpoel

No, in later versions of NAV, MSSQL determinate the best index. SETCURRENTKEY is useful for sorting the data in specific way only.

Slawek_Guzek

SETCURRENTKEY had a dramatic impact in NAV installations running on native database, which was possible up to the latest release 2009 R2 including Classic Client.

The SETCURRENTKEY still has some impact on performance in SQL Server based installations, in old versions using Classic Client, and in all versions using RTC. This is because SETCURRENTKEY determines the order in which the result will be returned, which in turn has an impact on what index SQL Server picks up for filteing. To recap - yes, SETCURRENTKEY still has an impact on filtering performance.

The best is always to do some tests on a large data sets. When a table has a just few thousands of records indexing/ordering does not make much difference.

RockWithNAV

jordi79 wrote: »

Hi,
I know in the old NAV before NAV2009, SETCURRENTKEY is called before SETRANGE/SETFILTER to improve filter performance. Does this still stand true for later versions of NAV? NAV2017? NAV2018?
Jordi

Yes It has Impact.

mcoscas

Hi,
I am sorry but I am not sure that it really impact the performance. I perform a test on the Item Ledger Entries, retrieving all records where the posting date >= 010318D (=755338 records).
1. without setcurrentkey 18157 ms / 18219 ms / 18000 ms
2. with setcurrentkey("Posting date") -18125 ms / 17922 ms / 17973 ms

Regards,
Michael