Aged Accounts Receivable report - performance issues

northerner

Hi has anybody come across performance issues with report 120? We have a Nav 2009 SP1 database, running with classic SQL client, and report 120 is "out of the box" (i.e. no customisations). Running the report takes significantly longer than the version 3.7 report (used prior to the 2009 upgrade). The database size is 8.5GB, running on SQL Server 2008 with a reasonable hardware spec. I'd be grateful for any input.

Thanks

xSwazi

Hi,

I have exactly the same problem. Also with Accounts Recievable report. Taking over 5 hours to run...

Database is upgraded from 2.6 to NAV2009 SP1. Database is 12gig.

Any helps appreciated too.

JedrzejT

Hi

I analyse this once, because this report in 4.00 was fast. Now it isn't.
There is one huge diffrence between them. In dataitem OpenCustLedgEntry, there is no filter for OPEN=TRUE. So time of report grow with every new CL entry (report create temp rec for every one). If old report give your customer good results (i think it is not when you have partial payments), you can analyse this and maybe find a way to do some changes.

davmac1

The fix I did in 4.0SP2 was to use a custom temp table for the customer ledger entry with only the keys needed. The problem was the time it was taking to insert and delete records in the temp table.
You could check if this is still the case.
As a developer you can create a table out of your customer's range, because temp tables do not have to be in the license range when executed.
My fix sped it up dramatically, but it was still much slower than 3.7 because of the logic changes.
You could also see if Microsoft has fixed the speed issue.

The custom temp table is a quick fix in the interim.

xSwazi

Hi,

I have put a NAV 4.0 version of report 120 on the customer site (I did test ofsite first). This version of the report ran in seconds. I suspect that it is the RTC parts of the report in 2009 that cause the problems. Perhaps the 5.0 and 5 SP1 versions will work too.

Solved it for me \:D/ . I am going to do the same for the Aged Receivables Report 322.

Hope that helps you.

northerner

There is a Microsot hotfix for this issue, KB953359. This has given a limited improvement in performance in my case. The hotfix applies to version 4 onwards, and has to be manually applied (there is no fob file).