JAhlen (11/4/2009)

---

Hi Rob,

Thanks for your feedback! Sure you can write the closing balances calculations in much shorter ways (like your example), but performance would be severely reduced. The purpose of the recursive query is not to complicate things ;-), but to reuse previous closing balances so the underlying rows are not needed to be read again.

Calculating closing balance for day n.

Plain Query: CB_n = day₁ + day₂ + ... + day_n (n reads)

Recursive Query: CB_n = CB_n-1 + day_n (2 reads)

Let's consider an example where you have data for 1000 days. The plain query would then do about 500 000 reads, while the recursive query would do about 2 000.

Actually my query can be further optimized quite a bit, but I chose to go for readibility rather than absolutely maximum performance.

/Johan