Re: Client/Server query strategy

nraden_at_aol.com (NRaden) wrote:

>> 2) If the data is not stable or bounded, there is a high
>> potential for data integrity problems. Say that you
>> calculate the sum of the values in a particular column
>> and store the value but rows are still being added/deleted
>> /updated to the table. The sum you calculate will cease
>> to match the current value of the sum the moment anything changes.
>
>These are OLTP issues. One would not put aggregated data in the same table
>with rapidly changing detail.

Same table, different table -- doesn't matter. Doesn't affect just OLTP systems either.

If Joe User expects the aggregated total he reads out of whatever table to accurately represent the total of items meeting certain critera, the two had damn well better match or Joe could be up a creek without a paddle if he makes a bad decision based on not knowing the difference. ("Gee, I wouldn't have written that $2,000,000 check if I'd known the checking balance didn't show the check Jane wrote that left a balance of $0.49.")

I'm no dogmatist, but in nearly 30 years of programming, from real-time control systems to material cost forecasting systems, the worst sins I've seen were all committed in the name of "performance." Yeah, performance IS important, but data integrity is even MORE important. If a given situation is such that there is NO data integrity risk involved in storing aggregated data and the performance gain is significant, then by all means store the aggregated table. Otherwise, don't bet your job or your company's future on it.

A young bull and an old bull are standing on a hill overlooking a herd of cows in the valley.

Says the young bull, "Let's _run_ down into the valley and enjoy the favors of one of those lovely cows!"

Says the old bull, "Let's _walk_ down and enjoy them all."

Moral: speed isn't everything.

--
Bill Meahan,  Senior Developer  |        wmeahan_at_ef0424.efhd.ford.com
Electrical & Fuel Handling Division, Ford Motor Company
Opions expressed herein are those of the author and in no way represent
any official statement or opinion of Ford Motor Company