Posted by Ed Murphy on 12/07/06 16:38

meeraguna@gmail.com wrote:

> We have 20 -30 normalized tables in our dartabase . Also we have 4
> tables where we store the calculated data fron those normalised tables.
> The Reason we have these 4 denormalised tables is when we try to do
> the calcultion on the fly, our site becomes very slow. So We have
> precalculated and stored it in 4 tables.

Can you post the table definitions and on-the-fly calculations? Perhaps
they can be optimized more.

> The Process we use to do the precalcultion, will get do the
> calculation and and store it in a temp table. It will compare the the
> temp with denormalised tables and insert new rows , delte the old one
> ans update if any changes.This process take about 20 mins - 60mins. It
> takes long time because in this process we first do the calculation
> regardless of changes and then do a compare to see what are changed and
> remove if any rows are deleted, and insert new rowsand update the
> changes.
>
>
> Now we like to capture the rows/columns changed in the normalised
> tables and do only those chages to the denormalised table , which we
> are hoping will reduce the processing time by atleast 50%

First populate the temp table with the non-calculated fields for just
the rows that aren't already in the denormalized table, then perform
the calculations on just those rows.

[Back to original message]