In the ForNAV standard report pack we have a few reports that are traditionally slow when running. One of my design goals when developing these reports was to see if I can increase performance.
The names of the challenged reports will sound familiar to those in our channel for a longer time.
The latter only exists in the North American localization but whomever spends a lot of time on MiBuSo has seen the questions on performance of these guys.
Both reports have slow performance because they loop through the entry tables one-by-one which means they get slower over time. Both reports were created a long time ago. In case of the Aged Accounts Receivables & Payables report it was done before we had detailed entries.
So, this is the question everybody asks and the only true answer is “it depends”. It depends not just on the size of your database but even more on the ratio between Master Data and Entries.
Also, you need a reasonable amount of data to test this, not just a CRONUS database with Microsoft Demo data.
When I started my freelance career 12 years ago I decided to step into upgrades. Not alone, but with the help of my good friend Tom Wickstrom. Tom has probably done thousands of upgrades over the last decades.
Tom picked two databases for me that I’ve used to test with. One database is about 60 GB and the other is about 50GB. This is a good representation of a professional bespoke NAV system.
The ratio’s in these databases are different, especially at an Item level.
On average each customer has made between 25 and 30 purchases in 10 years. The number of sales per item is the biggest difference as is the amount of value entries per item entry.
The databases are installed on the same SQL Server. The servers are warmed up. We run the report once before we measure the results and then we take the average of three adjacent runs. We run using the Windows client. No Azure, No Docker, No VMWare or HyperV. Pure iron, bare metal. Each drive is an individual 500 gig ssd drive
The ForNAV reports are up to 8 or 9 times faster than the Microsoft RDLC reports. The difference gets smaller as the ratio between Master Data and Entries gets lower which makes perfect sense.
Well, although it is not a secret, I am not going to tell you. We wrote this blog post to trigger you to look at our product.
There are a lot of goodies in our report pack if you are a modern programmer. Where feasible we use the MVC pattern, Dependency Inversion and Polymorphism. This means that the Aged Receivables and Payables report use the same code where possible which then is reused in the Statement report.