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.

 

Advertisements
This entry was posted in Dynamics NAV. Bookmark the permalink.

4 Responses to Performance Issue with Workflow + Solution

  1. Jens Glathe says:

    Nice one 🙂 You could also use FILTERGROUP to avoid this filter string cut and paste logic. If I were into performance, I would go for ISEMPTY, though 😉

    Like

    • Matthias says:

      Hi, can you explain the FILTERGROUP and ISEMPTY-Solution? That would be great because we have a similar problem and this Hotfix is not working for me

      Liked by 1 person

      • Jens Glathe says:

        Hi Matthias,

        this is the code of the complete function: :

        LOCAL EvaluateConditionOnTable(SourceRecordId : RecordID;TableId : Integer;TempBlob : Record TempBlob) : Boolean
        RecRef.OPEN(TableId);

        IF NOT RequestPageParametersHelper.ConvertParametersToFilters(RecRef,TempBlob) THEN
         EXIT(TRUE);

        //PX001s px.jgl
        RecRef.FILTERGROUP(20);
        RecRef.GET(SourceRecordId);
        RecRef.SETRECFILTER;
        RecRef.FILTERGROUP(0);
        EXIT(NOT RecRef.ISEMPTY);
        //PX001e px.jgl

        Instead of looping through the list of records, the code gets the source record, sets the primary key filter on it (on a different filtergroup) and tests the whole filter with ISEMPTY. This is only false when the filters set by RequestPageParametersHelper.ConvertParametersToFilters() contain the source record. A GET is independent of the filters set on a Rec/RecRef, so it also works in this order. If you’re not sure you can change the call order:

        LOCAL EvaluateConditionOnTable(SourceRecordId : RecordID;TableId : Integer;TempBlob : Record TempBlob) : Boolean
        RecRef.OPEN(TableId);

        //PX001s px.jgl
        RecRef.FILTERGROUP(20);
        RecRef.GET(SourceRecordId);
        RecRef.SETRECFILTER;
        RecRef.FILTERGROUP(0);
        //PX001e px.jgl

        IF NOT RequestPageParametersHelper.ConvertParametersToFilters(RecRef,TempBlob) THEN
        EXIT(TRUE);

        //PX001s px.jgl
        EXIT(NOT RecRef.ISEMPTY);
        //PX001e px.jgl

        You could also only include the first part and leave the original FINDSET loop in there. And encapsulate the remaining modification in a hook. But it should be slower than ISEMPTY.
        Fun question: If a GET doesn’t care about filters, how does a security filter work?

        with best regards

        Jens

        Like

  2. Andri Wianto says:

    I do not know what’s wrong with my code, but the issue is still persists, until I delete 3 subscriber:
    COD 1550
    RemoveGenJournalLineRestrictionsBeforeDelete
    COD 1535
    DeleteApprovalEntriesAfterDeleteGenJournalLine
    DeleteApprovalCommentsAfterDeleteGenJournalLine

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s