advice on how to delete a large recordset

[DanC]

I've got a client database with several tables in in. I have an additional table of about 5 million records with a unique member ID that I need to link to the existing tables and delete the existing records based on that ID. I'm mainly concerned with 4 tables: address, individual, individual_group, individual_udf. All of them, except for the individual_group table, have about 5 million unique records in them (one row per id). The individual_group table has about 50 million records in it (multiple rows per ID).

I've set up a query as follows to do the delete:

SET rowcount 100000
Declare @rc int
Set @rc=1

While @rc>0
Begin
Begin Transaction
delete db1.dbo.address
FROM db1.dbo.address a
inner join member_delete m
ON a.ind_id = m.ind_id
SELECT @rc=@@rowcount
Commit
End
go

It's still taking an extremely long time, even without any indexes on the table being deleted from. Any suggestions on ways to speed this up would be appreciated.

thanks

 

[cheyney]

try this:

delete from
db1.dbo.address
where ind_id IN
(select distinct ind_id
from member_delete)


repeat for each table

cheyney

 

[tlbroadbent]

Lack of indexes will slow the query. Update and Delete queries benefit from indexing. Make sure the Ind_id column is indexed on both tables. Continue to use the JOIN rather than WHERE IN. It will be much faster.

You can also use the TABLOCKX and HOLDLOCK hints to improve performance by locking the table immediately as the delete starts.

delete db1.dbo.address (TABLOCKX, HOLDLOCK)


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[DanC]

thanks for the help, I appreciate it.