Re: Denormalize or not?

Z. Martinez wrote:

> Things to note:
> 1. SUM_ALL is just VAL1 + VAL2 + VAL3 + . . . + VAL20
> 2. SUM_1_5 is just VAL1 + . . . + VAL5
> 3. SUM_6_10 is just VAL6 + . . . + VAL10
> 4. SUM_11_15 is just VAL11 + . . . + VAL15
> 5. SUM_16_20 is just VAL16 + . . . + VAL20
> 6. Most queries on this table are full table scans.
>
> I want to get rid of the SUM fields to reduce the table size, reduce
> DISK I/O, and hopefully reduce execution time.
>

As a general rule of thumb, you don't want to store "calculated fields" in a table if the calculation is from the table itself. If calculation is done using multiple tables, i.e., from different tables, then you may want to store it. If on the other hand, your users are querying this table throughout the day and need these SUM values, you don't want to remove the columns; it's better to leave them in.

I can't see how getting rid of these columns will save you that much space. You may consider buying additional disk space to handle your situation. Remember that disks are pretty cheap nowadays. You will probably be stuck with your CPU longer than your disks i.e., it will be harder to justify buying a new computer than disks when your load gets large enough that "speed" does become a problem.

• I would first try to determine how the data is being used.
• Check if disk space is in the budget.
• Then decide on a course of action.

Good Luck!
-Mir. Received on Thu Jan 02 1997 - 00:00:00 CST