ArcherPoint Dynamics NAV Developer Digest - vol 168
The NAV community, including the ArcherPoint technical staff, is made up of developers, project managers, and consultants who are constantly communicating, with the common goal of sharing helpful information with one another to help customers be more successful.
As they run into issues and questions, find the answers, and make new discoveries, they post them on blogs, forums, social media…so everyone can benefit. We in Marketing watch these interactions and never cease to be amazed by the creativity, dedication, and brainpower we’re so fortunate to have in this community—so we thought, wouldn’t it be great to share this great information with everyone who might not have the time to check out the multitude of resources out there? So, the ArcherPoint Microsoft Dynamics NAV Developer Digest was born. Each week, we present a collection of thoughts and findings from NAV experts and devotees around the world. We hope these insights will benefit you, too.
Adjust Cost Report in Microsoft Dynamics NAV 2009 Takes too Long to Run
Tom: Does anyone know about the Adjust Cost-Item Entries report to help me out with a problem a client is having? The issue is that there are a handful of items that have so many Value Entry records that Adjust Cost-Item Entries does not run in a timely manner. (I’ve been running the report for my test item for over five hours, and I’d estimate that it’s not quite halfway done.) Is there a way I can tell it to process some of the activity for an item instead of all the activity? Is there something else I can do to speed it up? Any advice is helpful.
Matt: Average Costing? Adjust Cost has gotten better over the years and I see they are still on NAV 2009. You could get someone to look in SQL to see if there are any recommended indexes to add or remove. Generally, it should not take five hours to process an item every time. So if they are running it regularly there’s not an issue.
Kyle: Doesn’t the process mark Value Entries as it goes along so it does not have to do as much work the next time it runs? Maybe the process isn’t completing correctly. If you run the report by hand, you can specify some filtering like locations or item number ranges.
Tom: It is not that the report is taking five plus hours to process every item; it is that it is taking five plus hours to process a few specific items, and I need to get those specific items processed.
It’s one of those things where they have a shipping charge that’s put on the order as an item, and so it goes on nearly every order, and then Adjust Cost has to grind through every order to get the adjustments made. (Side note: I wish customers would use Resources or G/L Accounts or really anything other than items for this.)
Kyle: So write a small bit of code that runs adjust cost for a single item and run that during off-hours. Once it makes it through successfully, it should not need to touch those five-hour entries again.
Tom: If it were only that simple. By my estimate, the item that we’re having trouble with will take 20+ hours to run Adjust Cost. Is there a way (or would there be any damage) if I could tell Adjust Cost to go after a specific number of Value Entries for an item?
Kyle: Someone smarter than me would have to answer that question. The only safe and sure way that I know is to find that 20 hours. All day Sunday, the next holiday, something like that.
And suggest they get a better server.
Jon: This is an age-old problem for customers. Modifying the report has not given performance increases relative to the amount of time it takes to make the modifications and analyze the data (it goes beyond the report and the data). I have made modifications that allow running a single Item (with COMMIT after a certain range as desired). That is just a band-aid, but it does log time metrics per item and can give insight into what items are actually causing issues. Even that can be a red herring. The customer will get very frustrated if you spend 40 – 100 hours with little performance gain. Unfortunately, I know this from experience.
What has worked for me:
- Upgrading to the latest version of NAV
- Upgrading the drives to Solid State (or better)
- Upgrading the CPU speed (fractions can give dramatic increases in performance)
- Upgrading to SQL Server Enterprise (I know it’s expensive, but so are you)
- Running the Adj. Cost nightly
- Ensure proper VM Farm Resource Allocation (this can be huge)
So, when I am tasked with a request to solve a long running Adj. Cost report, and I’ve seen 48+ runtimes, I always ask stupid questions about their VM farm to determine if they have properly allocated their resources. And by stupid I mean I prod them until they reveal exactly how they’ve allocated their VM’s. You’d be surprised at how confident a Senior Level Sys Admin can be until you prod them, only to find that they overlooked something obvious, like allocating 380 GB of RAM to the SQL Server, when the physical server only has 286, or they have a version of SQL Server that can’t even use half of what is allocated. Brian Winfrey can help in this area.
Kyle: I think a key point is still getting lost. Correct me if I am wrong, but I though that once Adjust Cost had churned through its 20+ hours and made whatever corrections, that the next iteration would be much faster. It doesn’t have to go back and look at every single value entry each and every time. So I still think if you can get it to run once and correct whatever 20 hour thing is out there, then you can turn it on nightly and it will stay caught up without taking too long.
Jon: You would think so, but, when it get’s really bad, there’s nothing you can do except try the suggestions I stated above. Customer A and Customer B, for instance. Before they upgraded their system, the Adj. Cost would take 40+ hours. If they run it immediately after it finishes it’s 40 hour runtime, it takes a few seconds. But, at the end of the week, it churns for 40+ hours. For some reason, Customer A didn’t want to run it daily, Customer B ran it every night, manually, and it would take 10 to 12 hours. They would kill it before users start their day, rolling back to the last item processed. They would catch up on the weekend, running it for 40 hours. There was literally not enough time in a week to run it fully every week.
Stay abreast of what is new in the Microsoft Dynamics NAV community and at ArcherPoint by subscribing to our monthly newsletter, Better Business, by completing the form in our Resource Center.
If you are interested in NAV development, be sure to see our collection of NAV Development Blogs.
Read the “How To” blogs from ArcherPoint for practical advice on using Microsoft Dynamics NAV.