Query 1501 "Workflow Instance" Performance

bbrown
bbrown Member Posts: 3,268
in SQL Performance
Has anyone had any experience (luck) improving the performance of Query object 1501 "Workflow Instance"? I'm working here with NAV 2016. As I investigate the performance of various processes I often find calls to this object account for a sizable portion of the processing time. This seems to be checking if a Workflow needs to be executed based on a table that has been updated.

The process I am focused on currently is a simple Excel import that loads a bunch of records into a General Journal batch. The calls to Query 1501 account for almost 40% of the processing time. Looking at the execution plan is SSMS suggested adding an index for "[Workflow Code]" to the [Workflow Step Instance] table. Along with several included columns. I tested that with and without the included columns. While it did improve the query when run in SSMS, it did not seem to have any noticeable improvement in NAV.

Improvement of this query would likely have a fairly global improvement on overall performance as it appears to get executed rather often.
There are no bugs - only undocumented features.

Comments

  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    How many entries do you have in Workflow Step Instance ? It is a work table, there should be only active workflow entries in there, which implies not that many records..
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • bbrown
    bbrown Member Posts: 3,268
    The table contains 9182 records currently. This is a system with over 300 users. Are yo thinking some of those records may be outdated or not legit? I have not dug into that. Any suggestions on the best approach to sort that out?


    There are no bugs - only undocumented features.
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    With less that 10k records in the table indexing normally do not make much difference, most likely the whole table sits in server cache anyway.

    Nonetheless try adding an index for Workflow Code to the Workflow Step Instance AND make it clustered.
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    On a second thought.. 10k records in here... if you have a 20 steps per Workflow on average it would mean 500 active workflows, almost 2 active workflows per every user. That's a bit fishy I'd say.

    You could match this with Approval Entry table (the relation is Workflow Step Instance ID on Approval Entry =ID on Workflow Step / Workflow Step Instance) and delete everything in Workflow Step / Workflow Step Instance not found in Approval Entry
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • bbrown
    bbrown Member Posts: 3,268
    Thanks. That made a world of difference. I had added that index several months ago but not made it clustered. Making it cluster made a huge difference. My test function went from 8 minutes to 30 seconds.

    Thanks for the help.

    There are no bugs - only undocumented features.
  • allanohlfsen
    allanohlfsen Member Posts: 7
    Query 1501 was hotfixed at some point in time. Think it was this one.

    https://support.microsoft.com/en-us/help/3202891/cumulative-update-13-for-microsoft-dynamics-nav-2016-build-47256

    Made a huge difference in performance.

    B.R

Categories