Performance Issue with Workflow + Solution

When I upgraded with one of my customers to NAV 2016 workflow was one of the new features I was looking forward to working with. You can imagine how disappointed I was when I found out that there was a huge performance issue with conditional workflows.

Here are a few links:

“When I’m posting a G/L Journal with 8000 lines, then NAV nicely updates the progress bar the first 50 seconds and reaches 100%.
Then NAV freezes for about 8-9 minutes without any sign of life, before it tells me it is done.”

https://community.dynamics.com/nav/f/34/t/186493

http://forum.mibuso.com/discussion/comment/309940#Comment_309940

Let’s look at the core reason of this problem (or at least what I’ve found when debugging):

When using filters this piece of code is executed:

performance

LOCAL EvaluateConditionOnTable(SourceRecordId : RecordID;TableId : Integer;TempBlob : Record TempBlob) : Boolean
RecRef.OPEN(TableId);
IF NOT RequestPageParametersHelper.ConvertParametersToFilters(RecRef,TempBlob) THEN
EXIT(TRUE);
IF RecRef.FINDSET THEN
REPEAT
IF RecRef.RECORDID = SourceRecordId THEN
EXIT(TRUE);
UNTIL RecRef.NEXT = 0;
EXIT(FALSE);
What this code does, is loop through all records one-by-one until it finds the source to see if it matches within the filter.
Not so good for performance and this code is unchanged in NAV2017.

So how do you fix it?

In order to fix this we need to make a raw-source-code modification. Sorry James.
We need to combine the filter from the workflow with the primary key of the record we are looking at. If we do that, we no longer have to read every record. If the record is in the filter it will exit true. One read in the database. We can even replace it with ISEMPTY but we want our code upgradable so we use a hook.
This is the AL code in your hook.
performance-2
In text:
LOCAL AddPrimaryKeyToFilterForPerformanceBoost(VAR RecRef : RecordRef;RecordID : RecordID)
RecRefForKey.GET(RecordID);
RecRefForKey.SETRECFILTER;
FirstPartOfView := RecRefForKey.GETVIEW;
FirstPartOfView := COPYSTR(FirstPartOfView, 1, STRPOS(FirstPartOfView, ‘WHERE(‘) + 5);
KeyView := RecRefForKey.GETVIEW;
KeyView := COPYSTR(KeyView, STRPOS(KeyView, ‘WHERE(‘) + 6, STRLEN(KeyView));
KeyView := COPYSTR(KeyView, 1, STRLEN(KeyView) – 1);
View := RecRef.GETVIEW;
View := COPYSTR(View, STRPOS(View, ‘WHERE(‘) + 6, STRLEN(View));
View := COPYSTR(View, 1, STRLEN(View) – 1);
RecRef.SETVIEW(FirstPartOfView + View + ‘,’ + KeyView + ‘)’);
I’m not sure if this will work always and I don’t consider myself  a mathematical programmer. But for me it did the trick.
Simply call this function like this
microsoftshoulddothis
People who took my classes will regognise my self-explaining documentation style.
This should be reported to Microsoft and hopefully this is “automagically” done by me blogging this. Microsoft seems to closely watch me at the moment.

 


Related
Recommended