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

Default Printing Option for Crystal Report

I have developed a PDF web report which opens in a browser by using Crystal Report for VS.NET 2003. Is there any way to capture the printer settings of the users (who view this report) and change the Printer Paper Size to Legal? I understand that this is a security issue and this is not permitted under normal circumstances...but is there a round about way? My OS is Windows 2003.
Thanks for your helpIf I understand your question correctly, here's how I do it in VB 6 and CR 8.5 using RDC (maybe you can manipulate it to fit your needs?).

Report.PaperSize = crPaperLegal
Report.PrintOut

This way, you don't have to change the user's settings.