> delete from Orders where OrderNbr in (select OrderNbr from DeleteOrders)

"Matthew" <Matthew@discussions.microsoft.com> wrote in message
news:D40E2A3E-D971-47DB-B278-8AD9852D35FD@microsoft.com...
>I have two tables:
>
> Orders (8million records)
> OrderNbr int,
> OrderDate datetime,
> ItemID int,
> .....
> There's clustered index on OrderNber.
>
> DeleteOrders (1000 records)
> OrderNbr int
> There's no index on DeleteOrders table.
>
> When I use the below sub query,
> delete from Orders where OrderNbr in (select OrderNbr from DeleteOrders)
> it took me 14 mins,
>
> while I use inner join,
> delete Orders
> from Orders,DeleteOrders
> where Orders.OrderNbr = DeleteOrders.OrderNbr
> or
> delete Orders
> inner join DeleteOrders
> on Orders.OrderNbr = DeleteOrders.OrderNbr
> it took me 20 mins.
>
> What could be the problem? based on my experience, inner join should be
> much
> faster than sub query.