SQLServerWiki

“The Only Thing That Is Constant Is Change”

Best way to Delete from Large table using date range or row count!

Posted by database-wiki on August 27, 2013

=> From my analysis, I found using Top clause in conjunction with delete statement – significantly quicker than other methods.

=> Modify the database name and delete statement with correct table name also i have assumed the table has got a date column.

=> We can use the SP in report mode to know the rows deleted for date range and date range for a given rows deleted.

USE [TEST_DATABASE]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create Procedure [dbo].[sp_LargeTableCleanup]

@RowCount BIGINT,

@DateMin DateTime,

@DateMax DateTime,

@Report char

AS

BEGIN

SET NOCOUNT ON

DECLARE @maxOfAType bigint = 100000,

@delayLength char(14) = ’00:00:00.010′,@TempDelRows INT =0,

@rowsDeleted bigint =0;

SET @rowsDeleted = @maxOfAType;

SET @TempDelRows = 0;

DECLARE @Terminator INT  =0

If (@RowCount is null AND  @DateMin is not null AND @DateMax is not null AND @DateMin <= @DateMax and @Report=’Y’)
BEGIN
SET @Terminator =999
                  Select @rowsDeleted=COUNT(*) from test_database..LargeTable  
                  And InsertUTC >= @DateMin and InsertUTC < @DateMax+1
                  Print cast(@rowsDeleted as varchar(10)) + ‘ rows will deleted.’
            END

If (@RowCount >= 0 AND @DateMin is null AND @DateMax is null AND @Report=’Y’)
BEGIN
SET @Terminator =999
      SET @maxOfAType = @RowCount;
            select top (@maxOfAType) @DateMin= MIN(InsertUTC),@DateMax=MAX(InsertUTC)
            from test_database..LargeTable 
            SET @rowcount = @@ROWCOUNT;  
            SET @TempDelRows = @TempDelRows + @rowcount    
            Print ‘Date range between ‘ + cast(@Datemin as varchar(12)) + ‘and ‘ + cast(@Datemax as varchar(12)) + ‘ will be deleted.’
      END

If @RowCount IS NULL AND  @DateMin is not null AND @DateMax is not null AND @DateMin <= @DateMax

BEGIN

SET @Terminator =999

WHILE @rowsDeleted > 0

Begin

delete top (@maxOfAType)  from test_database..LargeTable

Where InsertUTC >= @DateMin and InsertUTC < @DateMax+1

SET @rowsDeleted = @@ROWCOUNT;

SET @TempDelRows = @TempDelRows + @rowsDeleted

WAITFOR DELAY @delayLength;

END

IF @TempDelRows >=  0

BEGIN

Print cast(@TempDelRows as varchar(10)) + ‘ rows deleted’

END

END

If (@RowCount >= 0 AND @DateMin is null and @DateMax is null )

BEGIN

SET @TempDelRows  = 0

SET @Terminator =999

SET @maxOfAType = @RowCount;

WHILE @RowCount > 0

Begin

delete top (@maxOfAType)

from test_database..LargeTable

SET @rowcount = @@ROWCOUNT;

SET @TempDelRows = @TempDelRows + @rowcount

END

IF @TempDelRows >=0

BEGIN

Print cast(@TempDelRows as varchar(10)) + ‘ rows deleted’

END

END

if @Terminator=0

BEGIN

Print ‘Please Enter Valid RowCount or Valid DateRange’

END

END

GO

Testing:

Reporting:

Exec [dbo].[uspPub_DimAddressCleanup] 50,NULL, NULL,’Y’
Exec [dbo].[uspPub_DimAddressCleanup] NULL,’2012/12/01′,’2013/03/29′ ,’Y’

Actual Delete:

Exec [dbo].[uspPub_DimAddressCleanup] 50,NULL, NULL,’N’
Exec [dbo].[uspPub_DimAddressCleanup] NULL,’2012/12/01′,’2013/03/29′ ,’N’

Exec [dbo].[uspPub_DimAddressCleanup] 50,’2013/01/02′,’2013/01/01′ — should not execute

Erroneous input:

Exec [dbo].[uspPub_DimAddressCleanup] NULL,’2013/01/02′,’2013/01/01′ ,’Y’– should not execute

Exec [dbo].[uspPub_DimAddressCleanup] 50,’2013/01/01′,’2013/01/02′,’Y’ — should not execute

Exec [dbo].[uspPub_DimAddressCleanup] 50,’2013/01/01′, NULL,’N’ — should not execute

Exec [dbo].[uspPub_DimAddressCleanup] 50,NULL,’2013/01/02′,’N’ — should not execute

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: