Results 1 to 4 of 4

Thread: Slow delete

  1. #1
    Join Date
    Jul 2005

    Unanswered: Slow delete


    I have a very large table which I want to clean. I have tried to do it with :

    delete from largetable
    where largetable.ID not IN(select smallertable.ID from smallertable)

    The large table contains circa 6 millions posts.

    The delete takes a very long time and the performance curves on the DB server peaks.

    Is there any way to speed up a delete clause? Is there any way to not log a delete?

    The DB is well indexed.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    there are lots of ways

    for example, you could run a JOIN query to select all columns for all rows of largetable which DO match some row in smalltable, save these rows to a separate new table, then drop largetable and rename new table | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2005
    Some others ways...
    • DELETE a 1000 rows at the time (don't know the optimum), this keeps the transactions small: make a WHILE-loop that checks if there are rows to delete and then do a DELETE TOP 1000. I think this is even recommendation in a MS article somewhere.
    • With your select determin the PK's that need to be deleted, place them in a temp-table (maybe index this table) and then join the temp-table in the delete

  4. #4
    Join Date
    Jan 2007
    Putting an INNER JOIN with smalltable on id field will surely boost performance.

Posting Permissions

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