Обсуждение: flattened tables with normalized tables
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?
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/
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?
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/
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 :-)
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.
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.