Posted by Erland Sommarskog on 03/22/06 00:22

hallpa1@yahoo.com (hallpa1@yahoo.com) writes:
> 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.

Probably the sorting occurs, because it is used for something, presumably
a merge join.

I don't know the exact rules for your purge, but I think you should get
all ids for TableA into one table with an IDENTITY column likes:

INSERT PurgeA (id1)
SELECT Id1
FROM TableA
WHERE ....
ORDER BY Id1

Add an index on the identity column as well as on id1. Then:

SELECT @last = 0
SELECT @first = min(ident) FROM PurgeA
WHERE ident > @last
SELECT @firstid = id1 FROM PurgeA WHERE ident = @first
SELECT @last = @firstid + 100000
SELECT @lastid = id1 FROM PurgeA WHERE ident = @last
IF @@rowcount = 0
SELECT @lastid = MAX(ident) FROM PurgeA

DELETE TableA
FROM TableA T
WHERE id1 BETWEEN @firstid AND @lastid
AND EXISTS (SELECT *
FROM PurgeA p
WHERE p.id1 AND T.id1)

I think it is important to have the chunk condition on the target
table, and not on a second table.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]