ha*****@yahoo.com (ha*****@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,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx