CODEWITHSUNDEEP
sqlsql-server-2008
Alex Gordon
Alex Gordon

Reputation: 60811

doing a large delete from sql server

i have two tables that are joined on a field rowid

i need to delete rows from calibration that match a specific condition based on a different table

there are approximately 50000 rows in batchinfo and 3 million rows in calibration

here is my sql statement:

delete from calibration where rowid in (

select calibration.rowid from batchinfo 
join calibration on batchinfo.rowid=calibration.rowid
where reporttime not like '%2011%')

this is taking hours!!

what can i do to speed this up?

Upvotes: 1

Views: 235

Answers (5)

Josh Darnell
Josh Darnell

Reputation: 11433

Could you use WHERE EXISTS instead?

DELETE 
FROM calibration 
WHERE EXISTS 
    (SELECT batchinfo.rowid 
     FROM batchinfo  
     WHERE 
         batchinfo.rowid = calibaration.rowid AND
         reporttime NOT LIKE '%2011%')

Note: This is just another option, it looks like the JOINs above should work for you. But, you know...variety is the spice of life =)

Upvotes: 1

Jake Feasel
Jake Feasel

Reputation: 16955

Why do you need the join? Won't this work?

delete from calibration where rowid in (
select batchinfo.rowid from batchinfo 
where reporttime not like '%2011%')

Or, if reporttime is part of calibration:

delete from calibration where rowid in (
select batchinfo.rowid from batchinfo ) and reporttime not like '%2011%'

Upvotes: 2

Matthew
Matthew

Reputation: 10444

You should simply delete using the JOIN and forget the IN statement.
See this question for details:
T-SQL: Selecting rows to delete via joins

Upvotes: 3

Matt Smucker
Matt Smucker

Reputation: 5244

delete c
from batchinfo b
join calibration c
    on batchinfo.rowid=calibration.rowid
where reporttime not like '%2011%'

Upvotes: 6

John Hartsock
John Hartsock

Reputation: 86882

Handle it in batches by adding a Top 1000 to your select statment then simply run again and again until there is nothing else to delete.

DELETE FROM calibration 
WHERE rowid IN (SELECT TOP 1000 
                  calibration.rowid 
                FROM batchinfo 
                JOIN calibration ON batchinfo.rowid=calibration.rowid
                WHERE reporttime NOT LIKE '%2011%')

Upvotes: 3

Related Questions