Re: Designing database tables for performance?

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 18 Feb 2007 15:25:05 -0800
Message-ID: <1171841103.170918@bubbleator.drizzle.com>

Walt wrote:

> "Timasmith" <timasmith_at_hotmail.com> wrote in message
> news:1171303358.093021.11570_at_j27g2000cwj.googlegroups.com...

>> 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.

>>
> 
> If you are good enough with the relational model, and you understand your
> data and your tools,  then we reached this point about 13 years ago.

That really is the truth. Most people still design their schemas based on what is to be stored ... how many times can you insert a record? Rather than how the information will be utilized ... how many times can you SELECT and aggregate a record?

Not every table should be a heap table.
Not every index should be a B*Tree.
Almost never is a cursor loop the best answer.

And yet these three statements describe 90+% of what is being produced.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sun Feb 18 2007 - 17:25:05 CST