Re: Designing database tables for performance?

"Timasmith" <timasmith_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. Received on Sun Feb 11 2007 - 20:54:25 CST