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.
