Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Archiving...INSERT & DELETE Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 9:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:52 PM
Points: 27, Visits: 153
It actually took me 47 minute to delete 13 684 records in batches of 100. I am there's got to be a faster way, and one of you good folks know it!!
Post #1357513
Posted Tuesday, September 11, 2012 9:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:52 PM
Points: 27, Visits: 153
I am using 2008R2
Post #1357514
Posted Tuesday, September 11, 2012 9:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:52 PM
Points: 27, Visits: 153
I am using SQL Server 2008R2
Post #1357515
Posted Tuesday, September 11, 2012 9:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:52 PM
Points: 27, Visits: 153
How do I make it a smaller transaction?
Post #1357516
Posted Tuesday, September 11, 2012 9:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:52 PM
Points: 27, Visits: 153
Oops..My apologies...Didn't mean to put that up there too many post that so many time!...
Post #1357524
Posted Tuesday, September 11, 2012 9:36 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:38 PM
Points: 22,511, Visits: 30,234
You could try using something like this:


declare @BatchSize int = 10000;
while @BatchSize <> 0
begin
begin try
begin transaction
delete top (@BatchSize)
from dbo.SPTLog
output
deleted.LogID, deleted.LogTime, getdate()
into
dbo.SPTLogArchive(LogID,LogTime,ArchiveTime)
where
LogTime < @dCutOffDate;

set @BatchSize = @@ROWCOUNT;
commit transaction;
end try
begin catch
rollback transaction;
set @BatchSize = 0; -- if you want to abort the delete, or set to 10000 if to continue
end catch
end





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1357526
Posted Tuesday, September 11, 2012 9:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:38 PM
Points: 22,511, Visits: 30,234
You might want to put a DELAY in the TRY part of the code after the commit to allow other activities to access the table from which you are deleting rows.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1357530
Posted Tuesday, September 11, 2012 9:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
oscarooko (9/11/2012)
It actually took me 47 minute to delete 13 684 records in batches of 100. I am there's got to be a faster way, and one of you good folks know it!!


SELECT [LogID]
,[LogTime]
,GetDate()
FROM [dbo].[SPTLog]
WHERE [LogTime] < @dCutOffDate;

How long does it take for this query to run with a suitable value for @dCutOffDate? Are you sure there's an index on [LogTime], table [dbo].[SPTLog]?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1357531
Posted Tuesday, September 11, 2012 9:43 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:38 PM
Points: 22,511, Visits: 30,234
ChrisM@Work (9/11/2012)
oscarooko (9/11/2012)
It actually took me 47 minute to delete 13 684 records in batches of 100. I am there's got to be a faster way, and one of you good folks know it!!


SELECT [LogID]
,[LogTime]
,GetDate()
FROM [dbo].[SPTLog]
WHERE [LogTime] < @dCutOffDate;

How long does it take for this query to run with a suitable value for @dCutOffDate? Are you sure there's an index on [LogTime], table [dbo].[SPTLog]?


In fact what indexes are defined on the table dbo.SPTLog?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1357534
Posted Tuesday, September 11, 2012 9:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
Lynn Pettis (9/11/2012)
ChrisM@Work (9/11/2012)
oscarooko (9/11/2012)
It actually took me 47 minute to delete 13 684 records in batches of 100. I am there's got to be a faster way, and one of you good folks know it!!


SELECT [LogID]
,[LogTime]
,GetDate()
FROM [dbo].[SPTLog]
WHERE [LogTime] < @dCutOffDate;

How long does it take for this query to run with a suitable value for @dCutOffDate? Are you sure there's an index on [LogTime], table [dbo].[SPTLog]?


In fact what indexes are defined on the table dbo.SPTLog?


Is LogID a foreign key in another table?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1357541
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse