Speed up selecting from joining table

22 Jul 2005 12:34 AM

Shawn

I have two tables, Orders & Order_Detail.

I was running the following query:

select o.num, o.amount, d.code, d.rate, d.order_date
from Orders o inner join Order_Detail d
on o.id = d.order_id
where d.order_date < 'Jan 1, 2005'

The query was much faster if I don't have the "where" part. Should I create
an index on d.order_date? What's the most efficient way to speed up the
query?

Thanks,

22 Jul 2005 1:03 AM

Shawn

Here is another issue:
If I just run

select o.num, o.amount
from Orders o inner join Order_Detail d
on o.id = d.order_id
where d.order_date < 'Jan 1, 2005'

The query was much faster without selecting columns from the Order_Detail
table.

Thanks,

22 Jul 2005 1:14 AM

Robbe Morris [C# MVP]

If you don't already have an index on that column and this
query is used frequently, then yes, I'd create an index on it.

Also check to be sure that you have primary and foreign
key indexes on your id columns.

--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net

Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp

Show quote

"Shawn" <Sh***@discussions.microsoft.com> wrote in message
news:74F91D05-0085-41EC-824B-9FBF1835FCCF@microsoft.com...
>I have two tables, Orders & Order_Detail.
>
> I was running the following query:
>
> select o.num, o.amount, d.code, d.rate, d.order_date
> from Orders o inner join Order_Detail d
> on o.id = d.order_id
> where d.order_date < 'Jan 1, 2005'
>
> The query was much faster if I don't have the "where" part. Should I
> create
> an index on d.order_date? What's the most efficient way to speed up the
> query?
>
> Thanks,
>
>