Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Question Unanswered: Which DELETE will be faster?

    A little background on my database structure:

    I have a live database and an archive database. I update the archive tables once a day from the live tables using:
    Code:
    INSERT INTO arc_table
    SELECT * FROM cur_table AS cur
    WHERE NOT EXISTS
    (SELECT * FROM arc_table AS arc
    WHERE arc.key = cur.key)
    
    GO
    This inserts newer records into the archive tables from the live tables.


    I have two different methods to clean the live tables once a week but keep data from the previous week. Both methods have been verified to delete the same rows.

    Code:
    DELETE cur_table
    WHERE EXISTS
    (SELECT key FROM arc_table AS arc
    WHERE arc.key = cur_table.key)
    AND date_time < GetDate() - 7
    
    GO
    Second method modified from BOL - deletes identical rows

    Code:
    DELETE cur_table
    FROM (SELECT key FROM arc_table) AS arc
    WHERE arc.key = cur_table.key
    AND date_time < GetDate() - 7
    
    GO
    I read that "WHERE [NOT] EXISTS" is faster than "WHERE [NOT] IN" but this is the first time I have seen DELETE xx FROM (SELECT ----)

    I'd like to know which procedure will be faster and/or better.
    Last edited by wey97; 02-23-06 at 15:54.

  2. #2
    Join Date
    Nov 2003
    Posts
    167
    I'm unsure of the logic behind the inline view in option b, so I would suggest option c:

    Code:
    delete	cur_table 
    from 	cur_table c inner join
    	arc_table a on c.key = a.key and c.date_time < GetDate() - 7
    Of course, the sql will only speed up the process so much since logging may be the bottleneck in your delete job.

    Query Analyzer shows the execution paths of all 3 statements to be identical.
    Last edited by onansalad; 02-23-06 at 18:19.
    Kit Lemmonds

Posting Permissions

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