NAV 2016 Import Performance - where to look ? Workflow firing for each journa line

SteveKnott

Hi,

I have an Xmlport that runs so incredibly slowly @ client- that I can attach the debugger and see the inserts happening.

By slow.. i mean 20 rows takes over a minute - i am hardly even validating - Using a straight XMLport with text attributes.

I have simplified the code down to the below to try and find the cause..

GenJnlLine.INIT;
GenJnlLine.VALIDATE(GenJnlLine."Journal Template Name",'GENERAL');
GenJnlLine.VALIDATE(GenJnlLine."Journal Batch Name",'STEVE');
GenJnlLine.VALIDATE(GenJnlLine."Line No.",Int);
EVALUATE(GenJnlLine."Account Type", AccountType);
GenJnlLine.VALIDATE("Account Type");
GenJnlLine.INSERT;

When I break the debugger - I see many calls to RestrictedRecord checking - from Workflows. No Journal workflow is enabled though - although we do have Purchase and Sales document approvals working.

If I copy the object to a "development" copy on my laptop.. the import is almost instantaneous - milliseconds. Even with workflows turned on.. the same

I have setup a new service tier just for me.. and I am the only one in the database and it is still extremely slow.

There is no filtering - nothing... so I don't know what is slwoing it down.

SQL and NAV service tiers both have ample memory - and are idling.

If I comment out the .INSERT - it runs instantly again - I do not know what else to check ? Server properties are identical in my DEV and this database ?

Any ideas as to where i can look ?

Peter+is1

Hi Steve,

Do you see the same RestrictedRecord checking on your development copy?
If so, perhaps there are index differences or even index problems on the production DB.

But this is a tricky one to find an answer for. (perhaps use SQL server tracing?)

Good luck.

Alex_Chow

What's the RAID setting on the server? If you're using RAID 5, inserts and modifications will be EXTREMELY slow.

parm

SteveKnott wrote: »

When I break the debugger - I see many calls to RestrictedRecord checking - from Workflows. No Journal workflow is enabled though - although we do have Purchase and Sales document approvals working.

Hi,
If you see several calls to RestrictedRecord then this is the place to start looking. Before find the solution, you must found the root problem.
I think the NAV 2015/2016 has some triggers to catch all inserts, even INSERT(FALSE).

Try to disable workflows so see if performance improves.

Try to create a codeunit/report that executes the same amounts of inserts with the same code. Check if insert 20 lines with codeunit also takes one minute like the XML port. If it takes the same amount of time the problem isn't the XML Port.

Try to create a copy of table 81 in range 50000 to check if there any improvement. If exists, there should be some configuration specific to table 81 (change log, workflow, add-on)

Good look and Regards,

parm

SteveKnott

Found the issue. OnAfterInsert subcriptions to table 81. With 14k workflow step instances... each check if workflow is enabled takes 200ms.. there are 4 -5 checks per journal line... this makes it incredibly slow..

Removing the subscription makes it fast again.

The check is done via a Query... something not right with that query when lots of workflow step instances exist. Going to log with MS.. seems this is an issue.

hensby.richard@gmail.com

Hi Steve

I have the same issue at one of my customers.

In their case it only takes about 5000 records in the Workflow Step Instances table to significantly slow down GL posting.

We have a secondary issue that the workflow steps are not getting archived when they should be, so the customer has to go in regularly and delete records. This part of the issue is being worked on by a third party software supplier.

But on the peformance impact front, did you get a response from MS?

And whereabouts are the Onafterinsert subscriptions held? As a temporary fix I may need to remove those for my customer.

Peter+is1

Hi,

-And whereabouts are the Onafterinsert subscriptions held?
I think you would look in CU 1520.

SteveKnott

Hi guys..

No feedback from MS.... other than use CU8 - and then.. wait dont use CU8 - wait for 9.

Best places for all event names is this blog post..

or you have to debug to see where they fire.. but this list is a good starting point..


http://www.waldo.be/2016/05/30/nav-2016-all-published-events/