Re: counting rows

From: Brian Selzer <brian_at_selzer-software.com>
Date: Tue, 04 Apr 2006 06:30:06 GMT
Message-ID: <OnoYf.11520$tN3.9742@newssvr27.news.prodigy.net>

If you're not concerned about performance, then the answer to your question is clear: don't store the count because it's redundant.

From a theoretical standpoint, redundancy is always bad. A fact can either be known or not, so it makes no sense for the same fact to be known more than once. In addition, redundancy shifts the responsibility for maintaining integrity from the database to the application. Since there can be more than one application for any given database, it's best to maintain integrity within the database.

Even from a practical standpoint, redundancy is almost always bad. Redundancy increases the amount of procedural code required to implement a solution. This reduces reliability: more code = more bugs. The additional lock duration combined with the increased query complexity leads to more blocking and a higher probability for deadlocks, which further reduces reliability.

Only in very rare instances would you ever want to store the result of an aggregate function in an OLTP database that also contains the unaggregated information. If the performance of a particular query is a stated requirement, and if the only way to meet that requirement (without busting the budget) is to denormalize or to otherwise introduce redundancy, then do it. But only after every other avenue has been exhausted.

<bucaliman_at_gmail.com> wrote in message
news:1144101219.154253.176920_at_j33g2000cwa.googlegroups.com...

>I would like to thank you all for the feedback, I see the question is

> more then just this or that way. Not entering in the detailed level of
> the history, the users/pictures were only an example of a situation I
> frequently found myself in. I'm not too concerned with performance at
> this time, since I even don't know if the project will succed. It was
> much more an issue of right db designing than anything else (although
> db designing is tied to performance). I think I will store the count,
> keep things in their places, (what would be searching without
> sorting?). Despite the need of increment/decrement, the count will be
> immediately available in the object's result set.
>
Received on Tue Apr 04 2006 - 01:30:06 CDT