Deleting records from table

Question

Post reply

Deleting records from table

antonio.reyes

Old Hand

Points: 387
More actions
August 24, 2011 at 9:20 pm

#100414

We run on a Windows 2000 Advance Server SP4/ SQL 2000. I'm writing a scripts (below) to delete records from worktables (guestmap_a, guest_t_a) based on a master table (guest_0_a).
Question:
1. Can I run the two scripts (as stored procedures) at the same time.
2. Will the same records (top 100000) be selected on the declared cursor.
Script1:
--------
declare guest_cur cursor
for select top 100000 guest_key, guest_id
from guest_0_a
where date_modified >= ‘2002-01-01’ and
date_modified < ‘2002-04-01’
for read only
declare @guest_key int, @guest_id varchar(15)
open guest_cur
fetch next from guest_cur into @guest_key, @guest_id
while @@fetch_status <> -1
begin
delete guestmap_a where guest_key = @guest_key
fetch next from guest_cur into @guest_key, @guest_id
end
close guest_cur
deallocate guest_cur
GO
Script2:
-------
declare guest_cur cursor
for select top 100000 guest_key, guest_id
from guest_0_a
where date_modified >= ‘2002-01-01’ and
date_modified < ‘2002-04-01’
for read only
declare @guest_key int, @guest_id varchar(15)
open guest_cur
fetch next from guest_cur into @guest_key, @guest_id
while @@fetch_status <> -1
begin
delete guest_t_a where guest_key = @guest_key
fetch next from guest_cur into @guest_key, @guest_id
end
close guest_cur
deallocate guest_cur
GO

Viewing 7 posts - 1 through 6 (of 6 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Step 1: get rid of the c.ur.s.o.r. *cough*

Script 1: get the data in a temp table to beused by the two separate scripts

select top 100000 guest_key--, guest_id

INTO #to_delete

from guest_0_a

where date_modified >= '2002-01-01' and

date_modified < '2002-04-01'

Script 2: Delete from guestmap_a

DELETE guestmap_a

from guestmap_a

INNER JOIN #to_delete on guestmap_a.guest_key = #to_delete.guest_key

Script 3:

DELETE guest_t_a

from guest_t_a

INNER JOIN #to_delete on guest_t_a.guest_key = #to_delete.guest_key

This will definitely outperform any c.u.r.s.o.r approach...

For parallel processing, if ever needed, I would either use ServiceBroker or SSIS.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

antonio.reyes Old Hand Points: 387 More actions · Answer 2

Thanks.. I will ty and test this scripts. Very much appreciated.

antonio.reyes Old Hand Points: 387 More actions · Answer 3

Hi Lutz,

It works. It's fast. I tried it for two tables deleting 2 million records for each table. Below is my final stored procedure in which I wanted to delete about 4 million records for each table. Is there a limitation on the number of records? Please advise.

Thanks.. Antonio

select guest_key, guest_key_real from guest_0_a where date_modified >= '2002-01-01' and date_modified <'2003-01-01'

into #todelete from guest_0_a

delete guest_t_a from guest_t_a inner join #todelete on guest_t_a.guest_key = #todelete.guest_key

delete guest_t_b from guest_t_b inner join #todelete on guest_t_b.guest_key = #todelete.guest_key

delete guestmap_a from guestmap_a inner join #todelete on guestmap_a.guest_key = #todelete.guest_key

delete guestmap_b from guestmap_b inner join #todelete on guestmap_b.guest_key = #todelete.guest_key

delete guest_0_s2_a from guest_0_s2_a inner join #todelete on guest_0_s2_a.guest_key = #todelete.guest_key

delete guest_0_s2_b from guest_0_s2_b inner join #todelete on guest_0_s2_b.guest_key = #todelete.guest_key

delete guest_ah2_a from guest_ah2_a inner join #todelete on guest_ah2_a.guest_key = #todelete.guest_key

delete guest_ah2_b from guest_ah2_b inner join #todelete on guest_ah2_b.guest_key = #todelete.guest_key

delete guest_tah2_a from guest_tah2_a inner join #todelete on guest_tah2_a.guest_key = #todelete.guest_key

delete guest_tah2_b from guest_tah2_b inner join #todelete on guest_tah2_b.guest_key = #todelete.guest_key

delete guest_all_a from guest_all_a inner join #todelete on guest_all_a.guest_key_real = #todelete.guest_key_real

GO

LutzM SSC Guru Points: 107049 More actions · Answer 4

Depending on the percentage of rows to delete it might be more effcient to copy the remaining rows into a new table and rename it. It depends...

As an alternative you could delete the rows in batches as described in Lynn Pettis article .

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

antonio.reyes Old Hand Points: 387 More actions · Answer 5

Hi Lutzm,

Thanks for the tips on deletion.

Wishing you a Merry Christmas and a Spirit-filled Happy Prosperous New Year!

God bless you...

Antonio

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

LutzM (9/2/2011)
Depending on the percentage of rows to delete it might be more effcient to copy the remaining rows into a new table and rename it. It depends...
As an alternative you could delete the rows in batches as described in Lynn Pettis article .

And thanks for the plug on the article. Always welcome.

Merry Christmas and a Joyous New Year!