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