Re: Designing database tables for performance?

On 8 Feb 2007 21:04:08 -0800, "Timasmith" <timasmith_at_hotmail.com> wrote:

>I am trying to get my head around who to best design a couple of
>tables for performance. The entities are fake so ignore that, I am
>just focusing on the table sizes and values.
>
>Suppose I had a table called items with 50,000 rows
>
>items (item_id number, parent_item_id number, item_name string)
>
>and I had a table called orders with 10,000,000 rows
>
>orders (order_id number, order_date timestamp, order_status number,
>customer_id number, item_id number)
>
>
>Now items are a hierarchy (hence the parent_item_id) and your average
>custom has 1000 rows in the orders table.
>The 1000 rows represents 100 different items on average.
>
>There are two primary use cases for querying. One is like this:
>
>select i.item_name, o.order_date, o.order_status
>from orders o, items i
>where o.customer_id = ?
> and o.order_date > sysdate-1
> and o.item_id = i.item_id
> and i.item_id in (?....?);
>
>Now ?...? represents a potentially very long list of items -
>essentially a subtree of the item hierarchy.

Are you sure about the use-case?

If the item_id list is so long, it sounds like you only want to exclude a few very rare orders. That sounds like a very odd business case to me.

You'd probably want to build some kind of association table and store the various subtrees in some implicit or explicit form, then the last clause would just be   

  and i.item_type = 12

plus or minus whatever the predecessor function is,

and maybe you can make time in your busy day to learn the ANSI join forms:

from order o inner join items i on o.item_id = i.item_id.

Doesn't look like a horrible query, should go in a fraction of a second on a big server with lots of RAM cache.

>
>The other query is searching for a few items across a longer timeframe
>
>from orders o, items i
>where o.customer_id = ?
> and o.order_date > sysdate-30
> and o.item_id = i.item_id
> and i.item_id in (?,?,?);
>
>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.
>
>Any ideas?

Make sure you have a nice covering index on customer_id + order_date, and this should crank just fine.

J. Received on Fri Feb 09 2007 - 00:09:42 CST