SETCURRENTKEY with SETRANGE and SETFILTER

jordi79
jordi79 Member Posts: 280
in NAV Three Tier
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

  • cvanderpoel
    cvanderpoel Member Posts: 9
    No, in later versions of NAV, MSSQL determinate the best index. SETCURRENTKEY is useful for sorting the data in specific way only.
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    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
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • RockWithNAV
    RockWithNAV Member Posts: 1,172
    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.
    Thanks

    Blog - rockwithnav.wordpress.com/
    Twitter - https://twitter.com/RockwithNav
    Facebook - https://facebook.com/rockwithnav/
  • mcoscas
    mcoscas Member Posts: 34
    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

Categories