?
Solved

very large joining delete

Posted on 2006-05-31
5
Medium Priority
?
1,980 Views
Last Modified: 2006-11-18
Hello,
  We've been having trouble cleaning up some of our tables in MSSQL 2k.  These tables are always having data put in them and I need the db to be up at all times, so I can't do some kind of bulk insert into a temp table and truncate.  Basically I need to delete everyday about 3 million rows from a 70 million row table.  Normally, no problem, I would just do a regular set rowcount = 1000, and a while rowcount <> 0 and do chunks at a time.  The problem is to pick the 3 million I need to delete I'm having to join another table....

story, 70,000,000 rows
keymatch, 70,000,000 rows

DELETE FROM story WITH (NOLOCK)
      LEFT OUTER JOIN keymatch WITH (NOLOCK) ON
      story.linkid = keymatch.linkid
      AND story.keymatchid = keymatch.keymatchid
      WHERE keymatch.linkid IS NULL

So if I do the rowcount trick, it takes quite a while to make the join and then zips off and deletes a few thousand rows and would have to make the join again.  The work around we have now is to select into a cursor:

SELECT story.storyid AS thisstoryid FROM story WITH (NOLOCK)
      LEFT OUTER JOIN keymatch WITH (NOLOCK) ON
      story.linkid = keymatch.linkid
      AND story.keymatchid = keymatch.keymatchid
      WHERE keymatch.linkid IS NULL

And then loop through the cursor.  The problem is this is very slow deleting one at a time but at least it doesn't lock the table up.  Is there some other kind of trick I can use to do a big join and delete fast w/o locking the tables?  It's almost like I need to have it delete from big blocks of the cursor but I don't know of a way to do that.
0
Comment
Question by:skacore
  • 3
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
ram2098 earned 2000 total points
ID: 16804745
I think you can get all the storyid and linkids you have into a temp table and then delete the records directly joining with the temp table...

SELECT story.keymatchid,story.linkid into #temp
 FROM story WITH (NOLOCK)
     LEFT OUTER JOIN keymatch WITH (NOLOCK) ON
     story.linkid = keymatch.linkid
     AND story.keymatchid = keymatch.keymatchid
     WHERE keymatch.linkid IS NULL


DELETE FROM story WITH (NOLOCK)
INNER JOIN #temp (NOLOCK) ON
on    story.linkid = #temp.linkid
     AND story.keymatchid = #temp.keymatchid
0
 
LVL 1

Author Comment

by:skacore
ID: 16807165
Would it be a good idea to do:

SET ROWCOUNT 10000
WHILE 1 = 1
BEGIN
      DELETE story
      FROM story WITH (NOLOCK)
      INNER JOIN #temp ThisTempTable WITH (NOLOCK)
      on    story.linkid = #temp.linkid
                AND story.keymatchid = #temp.keymatchid
      
      IF @@rowcount = 0 BEGIN BREAK END
END
SET ROWCOUNT 0
0
 
LVL 11

Expert Comment

by:ram2098
ID: 16814053
I guess this is the only way you can do in SQL 2000, if you want to delete in batches.
0
 
LVL 1

Author Comment

by:skacore
ID: 16815804
So far so good.  I have just 1 follow up question.  You say it's the only way in SQL 2000, does 2005 offer some other solution?  We are planning an upgrade in the future and it would be great to know!
0
 
LVL 11

Expert Comment

by:ram2098
ID: 16831356
Yes...
You can use "TOP" key word with DML statements as well in SQL 2005. Also, you can access the deleted rows from a deleted statement.

like,


DELETE TOP 5000 ...from table1
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question