Prev: Manipulating complex joined query in a view
Next: Please help with Select Statement (Zip File)
From: Eric Russell on 23 Jul 2008 12:21
I can't see the whole picture from here, and sometimes performance
optimization involves data modeling compromises, but if the [natural key] on
a table is composed of only one column, then I typically use that as the
[primary key]. At first glance, it seems there is really no need for the
[surrogate key] OrderID.

"Omid Golban" wrote:

> Consider a variation of the classical order header and detail example:
>
> CREATE TABLE OrderHeader (
> OrderID INT, <- PK
> OrderNumber VARCHAR(10), <- uniqe (alternate key)
> CustomerID INT, <- FK
> ...)
>
> CREATE TABLE OrderDetail (
> OrderDetailID INT, <- PK
> OrderID INT, <- FK
> ItemID INT, <- FK
> ...)
>
> The unique column OrderNumber in OrderHeader is indexed for quick access.
> Let's say that I often want to select all OrderDetails for a specific OrderNumber, but I don't want anything else from OrderHeader.
> My dilema would be that I would like to create an index for OrderNumber (not OrderID) in OrderDetail but I don't want the column in
> OrderDetail.
>
> What would you suggest?
>
> Thank you,
> Omid
>
From: Eric Isaacs on 23 Jul 2008 12:23
My assumption is that you ultimately want to know the orders that are
involved. If you use your different criteria to nail down which
OrderIDs are required, then you can do your final select on just
OrderID.

Create a sproc with a temp table that contains OrderID only.

Create a separate SQL statement for each criteria you want to search
on that just determines the OrderIDs and inserts them into the temp
table. Use IF's to determine if the statement needs to be executed
(if the criteria has a value.)

So from OrderHeader, just get the OrderID by OrderNumber and/or
OrderDate. Then from OrderDetail, just get the OrderID from other
criteria in that table, such as PartNumber. The optimizer should
optimize each of those statements based on the criteria you're
providing.

When searching other tables, you have a choice as to whether you
include the OrderID in those tables (which would be redundant, but
would also be faster.)

In the end you'll have a list of OrderIDs. You can then find the
distinct values in that table and join that table to the tables in
your final select that gives you just the results you want.

This is just one other option that you may not have considered. I'm
not suggesting that it's your best option, but it is a likely
contender.

Also, make sure you consider indexes on the foreign keys in your
tables as well as the primary keys.

-Eric Isaacs
First  |  Prev  | 
Pages: 1 2 3
Prev: Manipulating complex joined query in a view
Next: Please help with Select Statement (Zip File)