Delete cursor (must change it !)

Hi all,

The db that I took over is full of !@.##$@., unnormalized tables, cursors, you name it and it has it :(.

There is this cursor that opens a temp table, fetches the key and then deletes from the production table using that key for every row in the temp table.

I want to change it to something like

delete from A
where exists (select 1
from B
where B.ID1 = A.ID1 and
B.ID2 = A.ID2)

Now, I'm thinking that this query would secuentially scan A and compare the key to what B has and that is a waste of time. Is there a way to do it the other way around ? Scan the rows on table B and then delete them from table A ?

I haven't really played with sql in some time, maybe the answer is trivial but I can't see it right now.

Thanks in advance

Luis TorresWhen you submit the query, SQL Server will generate a plan. That plan will often automagically turn the query "inside out" as you've described if the optimizer determines that is more efficient.

-PatP|||My guess is that this will do quite well

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable00(ID1 int, ID2 int, Col3 char(1), PRIMARY KEY (ID1, ID2))
CREATE TABLE myTable99(ID1 int, ID2 int, Col3 char(1), PRIMARY KEY (ID1, ID2))
GO

INSERT INTO myTable00(ID1, ID2, Col3)
SELECT 1 , 1, 'a' UNION ALL
SELECT 33 , 1, 'a' UNION ALL
SELECT 555, 1, 'a' UNION ALL
SELECT 777, 1, 'a'

DECLARE @.x int
SELECT @.x = 1
WHILE @.x < 1000
BEGIN
INSERT INTO myTable99(ID1, ID2, Col3)
SELECT @.x, 1, 'a'
SELECT @.x = @.x + 1
END
GO

SET SHOWPLAN_TEXT ON
GO

SET NOCOUNT OFF
GO

DELETE FROM l
FROM myTable99 l
INNER JOIN myTable00 r
ON l.ID1 = r.ID1
AND l.ID2 = r.ID2
GO

SET SHOWPLAN_TEXT OFF
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

And produces

StmtText
--------------------------------------------------------
|--Clustered Index Delete(OBJECT:([Northwind].[dbo].[myTable99].[PK__myTable99__39A43435]))
|--Table Spool
|--Top(ROWCOUNT est 0)
|--Nested Loops(Inner Join, OUTER REFERENCES:([r].[ID2], [r].[ID1]))
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[myTable00].[PK__myTable00__37BBEBC3] AS [r]))
|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[myTable99].[PK__myTable99__39A43435] AS [l]), SEEK:([l].[ID1]=[r].[ID1] AND [l].[ID2]=[r].[ID2]) ORDERED FORWARD)

(6 row(s) affected)|||Thank you Pat and Brett for your answer :), The inner join makes a lot of sense now that I see it :)

Luis Torres