Debtor Aged Balance Summary not working 65.10

Find general Jiwa support here.

Debtor Aged Balance Summary not working 65.10

Postby Ernst » Tue Apr 01, 2008 9:48 am

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. :cry:
User avatar
Ernst
Kohai
Kohai
 
Posts: 216
Joined: Tue Feb 19, 2008 3:43 pm
Topics Solved: 12

Re: Debtor Aged Balance Summary not working 65.10

Postby Mike.Sheen » Wed Apr 02, 2008 9:59 am

Ernst wrote: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. :cry:


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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Debtor Aged Balance Summary not working 65.10

Postby Ernst » Wed Apr 23, 2008 3:00 pm

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?
User avatar
Ernst
Kohai
Kohai
 
Posts: 216
Joined: Tue Feb 19, 2008 3:43 pm
Topics Solved: 12

Re: Debtor Aged Balance Summary not working 65.10

Postby Mike.Sheen » Fri Apr 25, 2008 7:13 pm

Ernst wrote: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
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Debtor Aged Balance Summary not working 65.10

Postby Mike.Sheen » Fri Jul 18, 2008 8:29 pm

Ernst wrote: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. :cry:


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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Debtor Aged Balance Summary not working 65.10

Postby Danny C » Tue Jul 22, 2008 4:54 pm

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
User avatar
Danny C
Occasional Contributor
Occasional Contributor
 
Posts: 19
Joined: Tue Mar 04, 2008 3:25 pm

Re: Debtor Aged Balance Summary not working 65.10

Postby Mike.Sheen » Wed Jul 23, 2008 7:19 pm

Danny C wrote: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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755


Return to Core Product Support

Who is online

Users browsing this forum: No registered users and 0 guests

cron