Faster Deletes

Question

Post reply

Faster Deletes

SQLRO

SSCrazy

Points: 2923
More actions
November 18, 2008 at 9:13 am

#124949

Hello,
I have a table of 3.7mn records. I need to delete 37,000 records from it.
I have created a temp. lookup table (not # or ## table) which contains the primary key values for the records to be deleted.
But to delete 100 records it takes 1.2 minutes. I have nonclustered index on the primary key.
Is there anyway to delete the records quikly? Using inner join in place of subquery too did not make much of the difference.
My query is as given below:
Delete from
where
)
Please suggest.
Thanks in advance
Rohit

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

Try to truncate table

TRUNCATE TABLE name_of_your_table

couz the DELETE command is logged and every deleted action will recorded on log file and if you have million of records sure that you will wait for long time!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

delete from

dbo.MainTable

from

dbo.MainTable mt

inner join dbo.DeleteTable dt -- table of primary keys to be deleted

on (mt.pk_id = dt.pk_id) -- what ever the primary keys are for the tables

This code is untested. I would test it in a test environment before proceeding to a production environment.

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 3

Dugi (11/18/2008)
Try to truncate table
TRUNCATE TABLE name_of_your_table
couz the DELETE command is logged and every deleted action will recorded on log file and if you have million of records sure that you will wait for long time!

OH!!! NO, NO, NO! Truncate will delete ALL the rows from the table! DO NOT DO THIS FOR THIS PROBLEM. DEATH BY SQL!!!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

krayknot SSCertifiable Points: 7998 More actions · Answer 4

Rohit Chitre (11/18/2008)
Hello,
I have a table of 3.7mn records. I need to delete 37,000 records from it.
I have created a temp. lookup table (not # or ## table) which contains the primary key values for the records to be deleted.
But to delete 100 records it takes 1.2 minutes. I have nonclustered index on the primary key.
Is there anyway to delete the records quikly? Using inner join in place of subquery too did not make much of the difference.
My query is as given below:
Delete from
where
)
Please suggest.
Thanks in advance
Rohit

d o you have to delete the records on the frequent basis or its a one time task. if it is a one time task. speed should not matters.

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

SQLRO SSCrazy Points: 2923 More actions · Answer 5

Hi Thanks for the reply. It is one time task. But I have to delete the records before users start using it i.e. before putting it on production.

Thanks & regards,

Rohit

SQLRO SSCrazy Points: 2923 More actions · Answer 6

The query is running for more than 7 hours and still the task is not completed.

krayknot SSCertifiable Points: 7998 More actions · Answer 7

Rohit Chitre (11/18/2008)
Hi Thanks for the reply. It is one time task. But I have to delete the records before users start using it i.e. before putting it on production.
Thanks & regards,
Rohit

ok i got the point. gimme some time as i have to test the same at my own. just 10 min

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

krayknot SSCertifiable Points: 7998 More actions · Answer 8

Rohit Chitre (11/18/2008)
The query is running for more than 7 hours and still the task is not completed.

Yes , it takes time

first query processor tried to find out where your targeted data rows are, per the SQL profiler results, this causes huge disk-reads if no appropriate index can be used for large table.

secondly, sql server takes action to 'delete' your data rows, for heap and table with clustered index, the DELETE action is different.

I followed the three ways:

1. same as yours [takes time]

2. use while loop do delete 1000/5000 at a time.

while 1 =1

begin

set rowcount 1000

DELETE FROM MYTABLE WHERE MyDateField <= '2006/01/01'

if @@rowcount = 0 break

end

3.Create a new table, insert into it only the rows you want to keep.

truncate and drop the old table. Rename the new table to be your old one. and recreate all the required Primary Keys, Indexes, Foreign Key Constraints etc.

now you can choose wht u have to do. I will prefer the while loop as sql gets exclusive locks on 1000 rows only not the entire table...so that you can avoid blocking.

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

ChiragNS One Orange Chip Points: 26137 More actions · Answer 9

Hi

Delete the records in batches. Look up this site for more info.

"Keep Trying"

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 10

If you want to delete only 37,000 rows, it should take only six seconds. Do you have any triggers on the table? Does anyone have an uncommitted transaction on the table causing non-deadlocking blocking?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dugi SSCoach Points: 17998 More actions · Answer 11

Jeff Moden (11/18/2008)
Dugi (11/18/2008)
Try to truncate table
TRUNCATE TABLE name_of_your_table
couz the DELETE command is logged and every deleted action will recorded on log file and if you have million of records sure that you will wait for long time!
OH!!! NO, NO, NO! Truncate will delete ALL the rows from the table! DO NOT DO THIS FOR THIS PROBLEM. DEATH BY SQL!!!

wOOOps I didn't see the nr of records for deletion! Sorry for that! This is the reason that the suggestion must tested on the test environment to be sure if it works or not ... Sorry again !?

:w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

SQLRO SSCrazy Points: 2923 More actions · Answer 12

No we don't have any triggers on it. Also there are no locks. I tried it when no users were working. Still it takes a long time.

Dugi SSCoach Points: 17998 More actions · Answer 13

Do you have active constraints ... try to disable constraints and after that try deletion then enable constraints again !

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

SQLRO SSCrazy Points: 2923 More actions · Answer 14

I tried disbling the constraints. But it did not work