Re: need to speed up query

PFC wrote:
>
>> i've had to write queries to get trail balance values out of the GL
>> transaction table and i'm not happy with its performance The table
>> has 76K rows growing about 1000 rows per working day so the
>> performance is not that great it takes about 20 to 30 seconds to get
>> all the records for the table and when we limit it to single
>> accounting period it drops down to 2 seconds
>
> What is a "period" ? Is it a month, or something more "custom" ?
> Can periods overlap ?
No periods can never overlap. If the periods did you would be in
violation of many tax laws around the world. Plus it you would not know
how much money you are making or losing.
Generally yes a accounting period is a normal calendar month. but you
can have 13 periods in a normal calendar year. 52 weeks in a year / 4
weeks in month = 13 periods or 13 months in a Fiscal Calendar year.
This means if someone is using a 13 period fiscal accounting year the
start and end dates are offset from a normal calendar.
To make this really funky you can have a Fiscal Calendar year start
June 15 2008 and end on June 14 2009

http://en.wikipedia.org/wiki/Fiscal_year
>
>> COALESCE(( SELECT sum(gltrans.gltrans_amount) AS sum
>> FROM gltrans
>> WHERE gltrans.gltrans_date < period.period_start
>> AND gltrans.gltrans_accnt_id = accnt.accnt_id
>> AND gltrans.gltrans_posted = true), 0.00)::text::money AS
>> beginbalance,
>
> Note that here you are scanning the entire table multiple times,
> the complexity of this is basically (rows in gltrans)^2 which is
> something you'd like to avoid.
>
For accounting purposes you need to know the Beginning Balances,
Debits, Credits, Difference between Debits to Credits and the Ending
Balance for each account. We have 133 accounts with presently 12
periods defined so we end up 1596 rows returned for this query.

So period 1 should have for the most part have Zero for Beginning
Balances for most types of Accounts. Period 2 is Beginning Balance is
Period 1 Ending Balance, Period 3 is Period 2 ending balance so and so
on forever.