When we implemented GP, the accounting team selected the Payables Historical Aged Trial Balance (HATB) report as one to run at month end. They really like being able to run the report as of the last day of the prior month after all the payments have been recorded. 10 years of data later and the report is taking forever to run. When I tested, it took 9 minutes. If I run the Steve Gray version of PM HATB, it completes in 6 seconds.
I looked into the stored procedures behind the report, pmHistoricalAgedTrialBalance and pmPrintHATBGetDocuments, and they are a case study of how not to write SQL code. They use multiple nested loops and dynamic SQL. The proper thing to do would be to refactor the code using SQL coding best practices. I thought of opening a case with Microsoft and politely asking them to fix this. But it seems like they don't really want to touch much legacy code. Refactoring this would need a lot of testing and they probably would think the risk of introducing calculation errors is not worth it.
Has anyone found a solution to keep this report running well? We have GP and SQL running on good hardware. (And as I mentioned, properly written code completes in 6 seconds.) Any chance opening a support case would have Microsoft cheerfully refactoring the code for the next release?
*This post is locked for comments
I have the same question (0)
