List:General Discussion« Previous MessageNext Message »
From:Greg Patterson Date:September 22 1999 4:57pm
Subject:Re: Q: For large database, SUM(amount) as balance not practical
View as plain text  
On Wed, 22 Sep 1999, John Millaway wrote:

> As the table grows, the SUM() will become unwieldy, especially when part
> of a larger query. Should I periodically condense the balance
> information to a single entry for each customer? How is this problem
> normally handled? 

First make sure your SQL select is using an index (on the customer id). If
your application doesn't require a complete history of transactions from
the beginning of time, you can consolidate the transactions say 6 months
back (make it an automatic process for all customers or even selected
customers) and store your consolidated transactions with the customer
record. 

Or you might consider consolidating to another history table by some
period (e.g. every month or every 2 months or something). Then when you
need to look at balances, you can check the single (or smaller subset
table) for the really old history balances as well as the smaller set of
"recent" transactions.

================================================================
|    Greg Patterson      | EMAIL: gomer@stripped        |
|                        |        gomer@stripped              |
| Linux: The choice of a | IRC:   xed (Irc.OpenProjects.Net)   |
| GNU generation!        | WWW:   http://www.linuxhelp.org/    |
================================================================

Thread
Q: For large database, SUM(amount) as balance not practicalJohn Millaway22 Sep
  • Re: Q: For large database, SUM(amount) as balance not practicalGreg Patterson22 Sep
  • Re: Q: For large database, SUM(amount) as balance not practicalScott Perkins22 Sep