Query 1501 "Workflow Instance" Performance

bbrown

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.

Slawek_Guzek

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..

bbrown

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?

Slawek_Guzek

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

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

bbrown

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.

allanohlfsen

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