Aged Receivable/Payable Report Performance Issue

mcaverly

We updated from Nav 3. to Nav 5. Aged Receivable and Payable reports become very sluggish. Prior to update, it took seconds to create 90-page reports (receivable or payable), now, it takes more than 5 minutes to execute the same report(s).

Has anyone experienced this same problem?

PS. we've tested Nav5 SP1, same problem.

Alex_Chow

SQL or native database?

mcaverly

Thanks

Alex_Chow

mcaverly wrote:

Thanks

:-s

No, it was a question. Are you on SQL or using the native NAV database?

mcaverly

We are using SQL 2000.

Sorry, I did not state this info. in the body of my last response.

Alex_Chow

mcaverly wrote:

We are using SQL 2000.

Sorry, I did not state this info. in the body of my last response.

You might want to check your performance on the SQL profiler. It's most likely that you need make some changes to enhance SQL performance on the Cust. Ledg Entry table.

Or ask a SQL perform consultant to tune your database for you.

mcaverly

I thought this is a Navision issue. Since we had no problem executing these reports when we still ran on Nav 3.x.

The problem has arised after we updated to Nav 5.

Besides, what can a sql tuner can do that can affect Navision reports?

Thanks for your response.

davmac1

I think the report was rewritten in release 4. All the open customer ledger entries for each customer are written to a temp customer ledger entry table and then deleted. The more keys, the more time.
I saw one instance where this report was taking more than 12 hours. I cut it down by defining an alternate customer ledger entry table for temp table use with only 2 keys.
If it is only taking 5 minutes for you, then Navision must have come up with a fix.
I don't see how SQL Server tuning will improve a temporary table performance issue.

ara3n

There is an issue in 5.0 for reports to run slower, and there is a hotfix for it. But if you are mentioning that 5.0 sp1 has the same issue, then it's not the case.

Did you run 5.0 sp1 on the same box?

bbrown

I ran into this problem on a recent 3.60 to 5.0 upgrade. The customers AR aging went from 20 seconds (3.60) to over 5 minutes (5.0) and the AP aging went from 30 minutes (3.60) to over 3 hours (5.0).

The report was changed to handle situations where the posting date was different from the invoice date. The old reports did not work correctly in this situation. See KB artice 905300 on PartnerSource for details.

Our ultimate solution for this customer was to revert to the 3.60 reports since they did not have the mismatched date issue.

mcaverly

This problem is due to the way the design of the report and the way NAV5 browsing and filtering the report dataset(s). The longer the customer list is, the worse the performance becomes. Apparently, Navision report engine is acquiring data prior to applying filters to its dataset(s).