Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    99

    Unanswered: 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 Torres

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When 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

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My guess is that this will do quite well

    Code:
    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)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Aug 2004
    Posts
    99
    Thank you Pat and Brett for your answer , The inner join makes a lot of sense now that I see it

    Luis Torres

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •