Creating a Balances Summary from a transaction table

[jjme88]

Hi all I could do with some advice.

I have a transactional table containing lots of transactions but want to keep a running balnce of certain values based on several key fields in this table.

I'll then want to display the inforamation to a user such that they can select one of the balances and see a list of the transactions that make up that balance.

My question is how best to keep track of my balances. Is it more efficent to use a #Temp table as part of a stored prcedure to build the balances as and when i need them or should i have a specific balances table that holds the summary information. If the latter is so should i be using triggers on my transaction table to update the Balances table??

Any advice is much appreciated...
jj

 

[r937]

calc the balances on the fly, don't store them

well, unless you're a bank

;-)

r937.com | rudy.ca

 

[jjme88]

hmm the problem i have is that in order to bulid all the data that I need for the balances i have a complicated view behind t that contains lots of joins . This view is then used to retrieve the balances and transactions in a single Stored Proc.

This strikes me as inefficet and it already takes up to 10 seconds to run with only a few 100 transactions.

I was wondering if using triggers on the transaction table to build / manage the balances in another might be more effient as when it comes to retieve the balance to dsiplay them it would be far easier. But is that a reliable thing to do? the Balances and transactions tables need to remain in sync.

ta,
jj

 

[r937]

> 10 seconds to run with only a few 100 transactions

i would fix this "complicated view" stored proc first, before extracting balances from it

r937.com | rudy.ca