Thread: flattened tables with normalized tables

flattened tables with normalized tables

From
Dennis Gearon
Date:
If I want to set up a dbase with normalized tables for inserts,and a
flattened table for selects, am i going in the right direction for
speeding up a busy site?

Also, if some of you are also doing this, how and how often do you do
the SELECT from the normalized tables to the flattened table?
And, do you have to write a post trigger to get all the references to
match up in the flattened table?

Re: flattened tables with normalized tables

From
Michael Fuhr
Date:
On Thu, Oct 07, 2004 at 10:07:47AM -0700, Dennis Gearon wrote:
> If I want to set up a dbase with normalized tables for inserts,and a
> flattened table for selects, am i going in the right direction for
> speeding up a busy site?

Are you familiar with views?  If so, is there a reason not to use
them?  What kinds of queries are you making?  Are you experiencing
performance problems with queries on the normalized tables?  Have
you investigated whether those queries can be sped up?

> Also, if some of you are also doing this, how and how often do you do
> the SELECT from the normalized tables to the flattened table?
> And, do you have to write a post trigger to get all the references to
> match up in the flattened table?

General Bits had an article on materialized views a while back:

http://www.varlena.com/varlena/GeneralBits/64.php
http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html

Before deciding on a solution, be sure you fully understand the
problem you're trying to solve.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: flattened tables with normalized tables

From
Dennis Gearon
Date:
Michael Fuhr wrote:

> On Thu, Oct 07, 2004 at 10:07:47AM -0700, Dennis Gearon wrote:
>
>>If I want to set up a dbase with normalized tables for inserts,and a
>>flattened table for selects, am i going in the right direction for
>>speeding up a busy site?
>
>
> Are you familiar with views?  If so, is there a reason not to use
> them?  What kinds of queries are you making?  Are you experiencing
> performance problems with queries on the normalized tables?  Have
> you investigated whether those queries can be sped up?
>
>
>>Also, if some of you are also doing this, how and how often do you do
>>the SELECT from the normalized tables to the flattened table?
>>And, do you have to write a post trigger to get all the references to
>>match up in the flattened table?
>
>
> General Bits had an article on materialized views a while back:
>
> http://www.varlena.com/varlena/GeneralBits/64.php
> http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html
>
> Before deciding on a solution, be sure you fully understand the
> problem you're trying to solve.
>
I am just planning ahead. I hope to have the site I'm building mushroom into a high traffic site, and I want to be
prepared.In the order that is necessary, with help fromt he list and probably the manual/books, I will throw: 
    tuning,
    hardware selection
    dedicated hardware,
    materialized views
    whatever else is appropriate at the right time,

at the problem.

I will also do all the things that you suggest as well, before I try materialized views. I found the same article that
youquoted at a different location, so I'm up to speed on that. 

About regular views, how does that speed things up, other than the initial SQL interpretation of the view not needing
tobe done? 

Re: flattened tables with normalized tables

From
Michael Fuhr
Date:
On Thu, Oct 07, 2004 at 09:08:18PM -0700, Dennis Gearon wrote:
>
> About regular views, how does that speed things up, other than the initial
> SQL interpretation of the view not needing to be done?

I didn't mean to imply that views would speed things up -- I was
merely suggesting them as an alternative to your "flattened table"
if part of its purpose would be to simplify queries.  You might
want to perform some experiments to see if the performance gains
from a materialized view are worth the extra complexity.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: flattened tables with normalized tables

From
Dennis Gearon
Date:
Michael Fuhr wrote:

> On Thu, Oct 07, 2004 at 09:08:18PM -0700, Dennis Gearon wrote:
>
>>About regular views, how does that speed things up, other than the initial
>>SQL interpretation of the view not needing to be done?
>
>
> I didn't mean to imply that views would speed things up -- I was
> merely suggesting them as an alternative to your "flattened table"
> if part of its purpose would be to simplify queries.  You might
> want to perform some experiments to see if the performance gains
> from a materialized view are worth the extra complexity.
>
I'll do the experiments! It'll probably be about 6 months out. I plan ahead :-)

Re: flattened tables with normalized tables

From
Bruno Wolff III
Date:
On Thu, Oct 07, 2004 at 22:35:50 -0600,
  Michael Fuhr <mike@fuhr.org> wrote:
> On Thu, Oct 07, 2004 at 09:08:18PM -0700, Dennis Gearon wrote:
> >
> > About regular views, how does that speed things up, other than the initial
> > SQL interpretation of the view not needing to be done?
>
> I didn't mean to imply that views would speed things up -- I was
> merely suggesting them as an alternative to your "flattened table"
> if part of its purpose would be to simplify queries.  You might
> want to perform some experiments to see if the performance gains
> from a materialized view are worth the extra complexity.

It may even turn out there aren't any performance gains from having a
materialized view. That will depend on the mix of operations in production.

Re: flattened tables with normalized tables

From
Dennis Gearon
Date:
Bruno Wolff III wrote:

> On Thu, Oct 07, 2004 at 22:35:50 -0600,
>   Michael Fuhr <mike@fuhr.org> wrote:
>
>>On Thu, Oct 07, 2004 at 09:08:18PM -0700, Dennis Gearon wrote:
>>
>>>About regular views, how does that speed things up, other than the initial
>>>SQL interpretation of the view not needing to be done?
>>
>>I didn't mean to imply that views would speed things up -- I was
>>merely suggesting them as an alternative to your "flattened table"
>>if part of its purpose would be to simplify queries.  You might
>>want to perform some experiments to see if the performance gains
>>from a materialized view are worth the extra complexity.
>
>
> It may even turn out there aren't any performance gains from having a
> materialized view. That will depend on the mix of operations in production.
>
Well, one particular query / view will probably draw from 11-15 tables. Several of those tables should have millions
andmillions of rows. However,as normalzed as all the data is, and having used surrogate, integer primary keys, the
tablesshouldn't be that big, most of them.