Posted by David Portas on 03/21/06 23:24

hallpa1@yahoo.com wrote:
> Hi All,
> I am trying to delete rows from a table using a SQL statement similar
> to this:
>
> DELETE FROM TableA where ID1 IN
> (Select ID1 From TableB where ID2>= @min and ID2<=@max)
>
> Basically I want to delete all rows from TableA that have an ID in a
> range in TableB. This is done in a stored proc.
> When I look at the execution plan, it is using the indexes as I would
> hope for. The problem is that it is doing a sort which accounts for
> 73% of the cost. I do not need to sort the results. I don't care what
> order they are deleted in.
>
> How can I prevent the sort from occuring? I need this delete to occur
> as fast as possible.
>
> Thanks In Advance

This is just a guess. Try:

DELETE FROM TableA
WHERE EXISTS
(SELECT *
FROM TableB
WHERE id2 BETWEEN @min AND @max
AND TableB.id1 = TableA.id1) ;

That's untested. Make sure you have a backup and test it out before you
try it against real data.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]