Debtor Aged Balance Summary not working 65.10

[Ernst]

Debtor Aged Balance Summary report has been switched to as at date type report. This works fine on a small database, but run it on a live system and you will be waiting a very long time.

Did a quick fix by copying back the report from 65.8 and reloading usp_JIWADebtorAgedBalanceSummary proc from 658. (it gets deleted in 65.10). Now report is back to 30 sec.

Hope this gets fixed in 65.11.

[Mike.Sheen]

Debtor Aged Balance Summary report has been switched to as at date type report. This works fine on a small database, but run it on a live system and you will be waiting a very long time.

Did a quick fix by copying back the report from 65.8 and reloading usp_JIWADebtorAgedBalanceSummary proc from 658. (it gets deleted in 65.10). Now report is back to 30 sec.

Hope this gets fixed in 65.11.

Ah, the joys of not being able to please all of the people, all of the time. First we get hounded to change the report to be as-at, then when we do, we get hounded to change it back.

I'll log a case for you Ernst - that way your reported problem will get the attention it deserves. If you email through support related queries to [email protected], they'll automatically get assigned a case number, and your issue / query will be allocated resources to investigate.

[Ernst]

Thanks Mike. Have just had another case today.

Customers db_trans is 128,000 records, agedout = 121,000

debtors aged balance summary runs for 15mins - no results.

Just posted this so those wanting a solution have a way forward.

Is their a way we can see all the case numbers and solutions, so we can
see the if a problem has already been reported and solution provided. Instead of bugging you guys with the same problem?

[Mike.Sheen]

Is their a way we can see all the case numbers and solutions, so we can
see the if a problem has already been reported and solution provided. Instead of bugging you guys with the same problem?

I'm not quite sure where the support is at with a self-service portal into our case system - we did trial it with a handful of dealers aboout 3 months ago, and thus far we found they don't use it. The self-service portal only allows you to see cases associated with you, but it does allow you to see all solutions.

Alternatively, you can always look at the bug-tracking database : http://jiwa.com.au:81/bugzilla/

You don't need a login to view or search - this is our internal bug track database, only Jiwa staff have logins for maintaining it.

[Mike.Sheen]

Debtor Aged Balance Summary report has been switched to as at date type report. This works fine on a small database, but run it on a live system and you will be waiting a very long time.

Did a quick fix by copying back the report from 65.8 and reloading usp_JIWADebtorAgedBalanceSummary proc from 658. (it gets deleted in 65.10). Now report is back to 30 sec.

Hope this gets fixed in 65.11.

06.05.12 addresses this issue. We've implemented indexes on the temp tables used by the stored proc (many thanks to our customer, Stuart Barnes from McGloins for this!), and this sped things up nicely.

The Inventory Quantity As-At stored proc (and hence report, and view from the Inventory Maintenance form) also has been modified to utilise this new technique (known in Jiwa now as the 'Barnes' technique !!!) and has also seen quite a dramatic improvement in performance.

On the topic of slow reports, another to see a significant performance improvement in 06.05.12 of Jiwa is the General Ledger Listing report. Indexes on temp tables yielded no benefits, but modifying the report to not use user defined functions to determine the year and period number saw this report improve it's performance substantially.

[Danny C]

Look forward to seeing this sped up in later releases as per Barnes technique.

In the meantime, I've just edited the stored procs to include the clause
WHERE AgedOut = 0
when the stored proc is retrieving the debtor trans data.

Not only does it speed up the reports, but it also excludes transactions where we've needed to fiddle with some transactions by directly aging them in SQL via that field.

Danny

[Mike.Sheen]

In the meantime, I've just edited the stored procs to include the clause
WHERE AgedOut = 0
when the stored proc is retrieving the debtor trans data.
Danny

This certainly would speed it up, but it will not actually be an as-at report any longer - so anyone employing this technique should understand it results in the as-at date being ignored.

The new report now does this also, but only if the as-at date you're running it for is the current server date (ie: today).

If you're not running the date today, it goes back to an as-at report, using the revised indexing technique.

So the result is, at worst case the report will be one or two minutes if it is running as at, and best case instantaneous.