Bad performance in Inventory Adjustment

Fommo · 2012-09-16

Hi

I have a customer that recently got really bad performance in Inventory adjustment.
They are using function MakeMultiLevelAdjmt in CU 5895 which normally runs in 45 min when scheduled in the Job Queue.
A couple of weeks ago they suddenly got really bad performance with this job. I have traced the execution with SQL Profiler and as I understand it the cause of the problem is locking. With SQL Profiler I get the following question frequently used:

SELECT TOP 501 * FROM "NAV DATABASE"."dbo"."COMPANY$Item Ledger Entry" WITH (UPDLOCK) WHERE (("Item No_"=@P1)) AND (("Applied Entry to Adjust"=@P2)) ORDER BY "Item No_","Applied Entry to Adjust","Entry No_"

When running this rather simple question in a separate copy of the production database, where my user is the only connected user, it's like runnin into a wall, it can run for hours without finish. ](*,)
If I run the question directly when starting up the system it finish though, so I think it should be some kind of lock that is not released by NAV.

Have anyone else experienced this issue? Any ideas of how to get around it?

DenSter · 2012-09-16

The duration of one single query does not explain the level of performance for the entire process. You have to check whether you have the proper maintenance on your system, and see if that didn't stop functioning around the time that you started seeing the problem. You need to verify whether there have been any changes, any additional reporting, any additional processes. You need to check whether this person's computer is functioning properly, whether the network connection is right, whether there is enough memory, whether you didn't have additional jobs in the Job Queue. There's a ton of other stuff that can contribute.

SO MANY things contribute to performance issues, you can't just point at one query and expect that to explain everything.

stryk · 2012-09-17

As Daniel said, there could be plenty of reasons for poor performence; thus there could be plenty of solutions as well!

The first step should be to really identify the problem - if it is indeed a (b)locking problem maybe this might help:
http://dynamicsuser.net/blogs/stryk/archive/tags/Locking/default.aspx

If it's due to an Expensive Query then maybe this could be feasible:
http://dynamicsuser.net/blogs/stryk/archive/tags/Indexes/default.aspx

But then again: hardware, configuration and setup also have major impact on performance. Since you say the performance "suddenly" decreased, the question might be what changes have been implemented shortly before this issue arised!?!

Fommo · 2012-09-17

Thanks a lot for your answers.
The copy of the database is running on a really cool server, with identical specs as the production server. The database is over 100 Gb (excluding the transaction log) and still it runs the backup and compression within 3 minutes.
So, the hardware is really good and the test database is running there alone, no other users or jobs interfering.
So, I will try the tips you gave about locking.

BUT, now I just thought about why they are running this CU. The idea from the customer side is to adjust cost on the items when they get the real cost from the vendor. In my opinion this is done with report 795.
So, I'm curious about why they are not running that report instead.
I ran that report now, with the most troublesome item, and it doesn't create any locks in the database. While the report is running I can still run queries in the database that I couldn't do while CU 5895 was running.
Do you know what is the difference between report 795 and CU 5895? When is it needed to run CU 5895?

Kind regards

Fommo · 2012-09-17

Fommo wrote:

Do you know what is the difference between report 795 and CU 5895? When is it needed to run CU 5895?

Okay, so that was obviously a stupid question. Report 795 calls CU 5895 so there is no difference.
Well, back to drawing board then.
At least I've found now why it's taking so long to run the report. In the logs I've found that the report took 45 min to up till one month ago. That day it suddenly took hours and had to be killed to release resources for the users. I have asked around and noone knows anything special that happened that day.
But when I run the job now it starts to adjust Item Ledger Entries from 2010.

I know that if you change "cost calculation type" it will recalculate all entries with the same "cost calculation type" within the same fiscal year. But firstly they say they haven't changed anything, and secondly it shouldn't include 2010 in this fiscal year.
Do you know some other reason why it would recalculate all historical Item Ledger Entries?
It seem to focus on a specific location for the recalculation of the historical transactions, but I'm not sure if that's entirely true.

Please help.

swati_india · 2012-09-17

Have you checked for back dated entries posted during the period or a credit memo with "Item Charge" having application to old transaction?

Fommo · 2012-09-18

Thanks for all the suggestions.
I found the source of the problem now. Someone on the customer side had posted a big negative adjustment on the date when this error occured. The adjustment was so big that all the items involved had to be adjusted back to the transactions in october 2010, that's why the execution of the cost adjustment suddenly took so long time to finish.
I'm investigating with the customer about the reason to this inventory report, but I'm glad I found what caused the problem anyway. :-k

There is usually a logical explaination and sadly it usually involve human interactions. ](*,)

Bad performance in Inventory Adjustment

Comments

Categories