SETCURRENTKEY with SETRANGE and SETFILTER

jordi79jordi79 Posts: 147Member
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

Answers

  • cvanderpoelcvanderpoel Posts: 9Member
    No, in later versions of NAV, MSSQL determinate the best index. SETCURRENTKEY is useful for sorting the data in specific way only.
  • Slawek_GuzekSlawek_Guzek Posts: 1,488Member
    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.
    Slawek Guzek
    Poszukuje programistow znajacych Microsoft Dynamics NAV
  • RockWithNAVRockWithNAV Posts: 691Member
    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.
  • mcoscasmcoscas Posts: 14Member
    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
Sign In or Register to comment.