Most of the projects that I have worked on were on North American version of Dynamics NAV. I've worked on version 2.0 and up. There were a couple of W1 projects as well and a couple of European localization. If you have worked with any implementation where the the databases are large. By large I mean more than 100 gig in size and more than 100 million gl transaction, you probably have dealt with performance issues.
Most of projects I have worked with, the client would complain about running Inventory Valuation Report. The Inventory Valuation Report in NA version of NAV is different than the W1 version.
Here is a screenshot of both reports.
They both show essentially the same information. W1 has increase and decreases column, which have been removed in NA.
The reason why I said removed is because of the history of the Report and NAV table structures.
W1 retrieves the data from Value Entry Table Mainly.
NA uses the Item Application table mainly to identify the Item ledgers to calculate as of Date fields.
So the question is why are they so different?
The answer that I came up with is that localization at one point for NAV was done in North America by MS employees.
And just like any NAV Developer, if they had to make a custom report, they would take an existing one copy it to 5000 Range and modify it.
The North American MS employee copied the report before Value Entries existed and in 2.6 the W1 Inventory Valuation was very similar to NA version.
When Value Entry table was introduced in NAV in 3.x version, the W1 Inventory Valuation report was improved to use the Value entry table.
The person who upgraded the NA version left the report as is and made sure it compiled and added Calcfields to Cost Amount fields which changed to Flowfields.
They didn't upgrade the NA report "Correctly". By "Correctly", I mean they should have taken W1 Inventory Valuation report in 3.x and copied to 10139 and make the UI Changes again.
In 2016 version I see that the NA version of the report has been modified event further by using a buffer table.
Using a buffer table like that with fields value 1, value 2 is horrible. Nobody knows now where it is being used and what is the purpose of them.
And why use Integer table as dataitem? You have introduced Temporary property for dataitem.
My suggestion for MS Localization team is to fix this properly. Copy the W1 report.
Instead of Temporary [Item Location Variant Buffer] use table [Avg. Cost Adjmt. Entry Point] It has all the records for items by Location, by Variant already.
This will improve the performance of the NA report at least to the same level as W1.
I don't know if they are reading this, but hopefully somebody forwards them this blog.
As far as Large databases concerned, Neither W1 or NA report will give you help you with running them quickly.
I had written a couple of years ago a Inventory Valuation report in sql. And sql version runs less than 5 minutes compared to hours for NAV report.