Re: Designing database tables for performance?

On Feb 11, 9:54 pm, Matt Atterbury <m..._at_virgo.contecint.com.au> wrote:
> "Timasmith" <timasm..._at_hotmail.com> writes:
> > It seems to me very difficult to optimize this, obviously we leave it
> > to the database and cross our fingers that the optimizer will pick the
> > right query in the right situation but I cant help thinking there must
> > be *something* one can do to help out. The fact the items are a
> > hierarchy doesnt help - difficult to avoid the 'in' statement without
> > knowing how many levels there are in the hierarchy.
>
> If performance is critical (and space and software maintenance aren't)
> you will need to flatten the item hierarchy into something like an
> item_equivalence table:
>
> item_equivalence (item_id1 number, item_id2 number)
>
> and add logic to generate and maintain this table. Then, given an item
> id, you can write a join to the item_equivalence instead of using "in".
>
> You should be able to use triggers to auto-maintain the
> item_equivalence table. Messy but, AFAIK, doable.
>
> m.

Its a funny old thing,performance is more important than space and software maintenance (nothing to maintain if you cant perform) but not at the cost of stability which is more damaging than running slow - and replicating data is always adding risk to stability (due to more complex maintenance... ha full circle).

I am hoping in a few years hardware will be so fast that this is no longer an issue - just write a good relational model. Received on Mon Feb 12 2007 - 19:02:38 CET