Massive data deleting issue | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Massive data deleting issue

Hi, All There is a massive data deleting job I need to do in my company, there is huge table named report in our main sql2000 database server, the total records number is more than 27,000,000. We need to delete 100,000 records which matches specific query criteria, when I run the delete statement, I’ll run a very long time, and CPU utility of the database server always reach at a peak level. That big table just contains one PK index. I run the following script to do the deleting:
— // Declare Variables
declare @CampaignID int — Campaign Working On
declare @RowDeleted int — Number of Rows Deleted
declare @RowCount int — Number of Rows to delete in 1x batch — // Set Defaults
set @CampaignID = 1120 — Get Campaign To be Archived
set @RowCount = 1000 — Set Number of rows to delete at once
set @RowDeleted = -1 — // Assume some records will be deleted first time — // Create Temp Table to hold ReportID’s to be deleted
create table #tblTemp (ReportID int) — // Create index on tblreport
CREATE INDEX [IX_CAM_CREATIVE] ON [dbo].[tblReport]([CreativeID]) ON [PRIMARY] — // Insert Report ID’s into temp Table
insert into #tblTemp (ReportID)
select TRP.ReportID
from tblReport as TRP with (nolock)
inner join tblCreative as TCR with (nolock) on (TRP.CreativeID = TCR.CreativeID)
where TCR.CampaignID = @CampaignID
— // Delete index
DROP INDEX tblReport.IX_CAM_CREATIVE CREATE INDEX ix_tmp_report_id ON #tblTemp(reportid) — // Set the number of rows to delete at once
set rowcount @RowCount while @RowDeleted <> 0
begin — // Delete Records that have previously been "marked"
delete TRP
from tblReport as TRP
inner join #tblTemp as T on (TRP.ReportID = T.ReportID) — // Get the number of records just deleted
set @RowDeleted = @@RowCount — // Force the database server to delay by x seconds to allow background to catch-up waitfor delay ’00:00:01′ — Wait for 1 second — // Display results on screen
print GetDate()
print @RowDeleted end
— // Drop temp table
drop table #tblTemp set rowcount 0 Can anyone share me a good method I can quickly delete those data? Best Regards and thank you for your helps. Denzel

Hi,
this topic is discussed earlier please go through it : http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=7818 Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

those suggestion will be helpful to you —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

Try this:
— // Declare Variables
declare @CampaignID int — Campaign Working On
declare @RowCount int — Number of Rows to delete in 1x batch
declare @Startint — First row in current iteration
declare @Lastint — Last row to be deleted — // Set Defaults
set @CampaignID = 1120 — Get Campaign To be Archived
set @RowCount = 1000 — Set Number of rows to delete at once
set @RowDeleted = -1 — // Assume some records will be deleted first time — // Create Temp Table to hold ReportID’s to be deleted
create table #tblTemp (tID int identity(1,1) primary key clustered, ReportID int not null) — // Create index on tblreport
CREATE INDEX [IX_CAM_CREATIVE] ON [dbo].[tblReport]([CreativeID]) ON [PRIMARY] — // Insert Report ID’s into temp Table
insert into #tblTemp (ReportID)
select TRP.ReportID
from tblReport as TRP with (nolock)
inner join tblCreative as TCR with (nolock) on (TRP.CreativeID = TCR.CreativeID)
where TCR.CampaignID = @CampaignID set @last = scope_identity() — // Delete index
DROP INDEX tblReport.IX_CAM_CREATIVE
— // Set the number of rows to delete at once
set @Start = 1 while @Start <= @last
begin — // Delete Records that have previously been "marked"
delete TRP
from tblReport as TRP
inner join #tblTemp as T on (TRP.ReportID = T.ReportID)
where t.tId between @start and @start + @RowCount – 1 — // Get the number of records just deleted
set @Start = @Start + @RowCount — // Force the database server to delay by x seconds to allow background to catch-up waitfor delay ’00:00:01′ — Wait for 1 second — // Display results on screen
print GetDate()
print @Start end
— // Drop temp table
drop table #tblTemp set rowcount 0
The problem with your code is that entire temp table is scanned in each iteration.
]]>