Unwanted Sort

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

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
--

I thought about this.  I went ahead and tested it.  No real difference
in either the execution plan or the actual results.

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

the first thing the engine will do is grab all the valid id1's from
tableb, adn stuff them into a "temp table."

Then, because Table A has an index on ID1, it makes sense to sort that
temp table so you can go through TableA efficiently.

A question. Do you have a clustered index on TableA?  Try getting rid
of the clustered index, and just having a non-clustered index.

regards,
doug