List:General Discussion« Previous MessageNext Message »
From:Arthur Fuller Date:September 3 2010 3:32pm
Subject:Re: Best method to keep totals
View as plain text  
While I agree with the general take on this subject ("Never store totals
without a good reason" and "where there is duplication there is the
opportunity for skew"), I must say that there are exceptions. A couple of
years ago I worked on an inherited database in which the operant principle
was "sum don't store"; the problem was that many of the rows summed dated
back a year or two or more, and as an accountant friend of mine loved to
say, "A paid transaction is history; an unpaid transaction is fiction." In
other words, repeatedly summing amounts dating from last year or the year(s)
before is a waste of time and energy. An approach much superior in
performance is to store History in one summary table and Current in the
actual transaction table. Then all no sums or other calculations are
required for the History portion of the final calculation or presentation or
whatever it is. You grab and sum this (fiscal) year's rows, and then look up
the numbers for previous year(s). When you're dealing with say a million
rows per year, this change can dramatically improve performance.

Just my $0.02".

Arthur

Thread
Best method to keep totalsTompkins Neil1 Sep
  • RE: Best method to keep totalsJerry Schwartz1 Sep
    • Re: Best method to keep totalsTompkins Neil1 Sep
  • Re: Best method to keep totalsJangita3 Sep
    • Re: Best method to keep totalsArthur Fuller3 Sep
      • Re: Best method to keep totalsMark Goodge3 Sep
        • Re: Best method to keep totalsArthur Fuller4 Sep
          • Re: Best method to keep totalsNeil Tompkins5 Sep
            • Re: Best method to keep totalsTompkins Neil6 Sep
RE: Best method to keep totalsJan Steinman2 Sep
  • RE: Best method to keep totalsJerry Schwartz2 Sep
    • Re: Best method to keep totalsJan Steinman3 Sep