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 ?
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.
