Solved

Where can we specify the range of records lets say after 1 million records stop deleting the records

Posted on 2011-02-16
14
593 Views
Last Modified: 2013-11-16
I want to delete milliions of  the records b/w sept to dec. there are about 100 million records. I want to delete them in batches rather than bulk to avoid locking the tables for longer time.

I came up with the query in which we can delete the records on the basis of id starting from id = 3370376. But the problem with the query is I can't able to specify  the range of records in the query and the requirement is it should be deleting the records till it reaches the id=  342609235. there is insertTime column in the following format (2010-02-11 01:27:00.197) but it doesn't have index so its taking longer time to delete the records.

In short I want to delete the records between 3370376 and 342609235 and the condition is deleting them in batches say 50,000 records/ batch.

Below is the query: (COuld you please specify where I can put the break for deleting the records say after id 342609235 stop deleting the records)
-------------------------------------------------------------------------------------
DECLARE @BatchSize INT,
            @id int

SET @BatchSize = 50000  --- no of records to be deleted in  a batch
SET @Id = 3370376            --- Starting id no. of the record      

WHILE EXISTS(SELECT 1 FROM dbo.Error WHERE Id > @id)
BEGIN
      DELETE  (@BatchSize)
      FROM dbo.Error
      WHERE Id > @id and
            OriginalMessage = 'Unknown UAC:0'

      --perform some other operations or wait
      WAITFOR DELAY '00:00:05';--HH:MM:SS
END
---------------------------------------------------------------------
Appreciate if somebody could help me out.

Thanks,
0
Comment
Question by:srionline2k6
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34904450
Hi, you can use...

SET ROWCOUNT

Open in new window


http://msdn.microsoft.com/en-us/library/ms188774.aspx
0
 

Author Comment

by:srionline2k6
ID: 34904516
Where can i specify the rowcount. (lets say I want to delete 111,238,859 rows)
IS it in the set statement.

DECLARE @BatchSize INT,
            @id int,
             @rowcount int

SET @BatchSize = 50000  --- no of records to be deleted in  a batch
SET @Id = 3370376            --- Starting id no. of the record
SET @rowcount= 111,238,859 -- rows to be delete totally      

WHILE EXISTS(SELECT 1 FROM dbo.Error WHERE Id > @id)
BEGIN
      DELETE  (@BatchSize)
      FROM dbo.Error
      WHERE Id > @id and
            OriginalMessage = 'Unknown UAC:0'

      --perform some other operations or wait
      WAITFOR DELAY '00:00:05';--HH:MM:SS
END

Could you please correct me if I am wrong, where should I specify Please point me out. thanks in advance.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34904532
use TOP

e.g.


DELETE  top 50000
      FROM dbo.Error
      WHERE Id > @id and
            OriginalMessage = 'Unknown UAC:0'
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34904545
@evilpostit

 the set rowcount method will not work in future versions of SQL server

 it been on the depreciated list since 2005
0
 

Author Comment

by:srionline2k6
ID: 34904578

Thanks for the quick response to you both (evil and lowfact)
Appreciate. your response.
while statement keeps on deleting the records in batches and we need to specify the condition to stop deleting the records.  i want to stop deleting the records say after deleting 111,238,85 records. Please give me reply for that particular query (Where i have to specify the no of records to be deleted???) or if you have any other query could you please pass it on.


Thanks waiting for your reply
0
 
LVL 7

Expert Comment

by:mkobrin
ID: 34905002
DECLARE @BatchSize INT,
            @id int

SET @BatchSize = 50000  --- no of records to be deleted in  a batch
SET @Id = 3370376            --- Starting id no. of the record      
SET ROWCOUNT = 50000  
WHILE EXISTS(SELECT 1 FROM dbo.Error WHERE Id > @id)
BEGIN
      DELETE  (@BatchSize)
      FROM dbo.Error
      WHERE Id > @id and
            OriginalMessage = 'Unknown UAC:0'

      --perform some other operations or wait
      WAITFOR DELAY '00:00:05';--HH:MM:SS
END
SET ROWCOUNT = 0
0
 
LVL 7

Expert Comment

by:mkobrin
ID: 34905007
DECLARE @BatchSize INT,
            @id int

SET @BatchSize = 50000  --- no of records to be deleted in  a batch
SET @Id = 3370376            --- Starting id no. of the record      
SET ROWCOUNT 50000  
WHILE EXISTS(SELECT 1 FROM dbo.Error WHERE Id > @id)
BEGIN
      DELETE  (@BatchSize)
      FROM dbo.Error
      WHERE Id > @id and
            OriginalMessage = 'Unknown UAC:0'

      --perform some other operations or wait
      WAITFOR DELAY '00:00:05';--HH:MM:SS
END
SET ROWCOUNT 0

Sorry you can not set reowcount with an = sign
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 7

Expert Comment

by:mkobrin
ID: 34905083
Sorry I misunderstood your question.

What you need to do is create a counter @count int.

set the value to 0
after each delete increment the counter by the number of records deleted in your batch
when the total number of records to delete - incemented amount < incremented amount
set batchsize to the total number of records to delete - incemented amount
and then return out of your loop
0
 

Author Comment

by:srionline2k6
ID: 34905112
Sorry i couldn't get you. I am quite new to this. Could you please specify in the code if you don't mind. This is urgent. I am waiting for your reply. Appreciate.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34905141
@mkorbin,

I dont think doing 111 millions individual delete statements will perform particlarly well or do it in a reasonable amount of time. Especially considering the user is trying to cause less of an impact to the system.

These sort of queries are known as RBAR queryies. (Row By Agonising Row)

Based on LowFatSpreads response probably best to go with the TOP suggestion. So your query would be...

DECLARE @BatchSize INT,
            @id int

SET @BatchSize = 50000  --- no of records to be deleted in  a batch
SET @Id = 3370376            --- Starting id no. of the record       

WHILE EXISTS(SELECT 1 FROM dbo.Error WHERE Id > @id)
BEGIN
      DELETE  TOP (@BatchSize)
      FROM dbo.Error 
      WHERE Id > @id and 
            OriginalMessage = 'Unknown UAC:0' 

      --perform some other operations or wait
      WAITFOR DELAY '00:00:05';--HH:MM:SS
END

Open in new window

0
 
LVL 7

Assisted Solution

by:mkobrin
mkobrin earned 62 total points
ID: 34905239
DECLARE @count INT,
            @id int

SET @count = 0 --- no of records already deleted
SET @Id = 3370376            --- Starting id no. of the record      

WHILE EXISTS(SELECT 1 FROM dbo.Error WHERE Id > @id)
BEGIN
      if @count >= 111200000
      BEGIN
          DELETE  top 38859
           FROM dbo.Error
          WHERE Id > @id and
                OriginalMessage = 'Unknown UAC:0'
          RETURN
     END
     ELSE
     BEGIN
          DELETE  top 50000
          FROM dbo.Error
          WHERE Id > @id and
                OriginalMessage = 'Unknown UAC:0'
           set @count = @count + 50000
      --perform some other operations or wait
      WAITFOR DELAY '00:00:05';--HH:MM:SS
      END
END
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34905276
like this....

also you probably want to put the delete  into a transaction...

it is a bad idea to have the waitfor loop unless you have committed the transaction...
(and even then its not really a good idea to have one)
declare @loop char(1)
SET @loop-'Y' 
while @loop='Y' 
BEGIN
begin transaction
      DELETE  TOP (@BatchSize)
      FROM dbo.Error 
      WHERE Id > 3370376 and id <= 342609235
            OriginalMessage = 'Unknown UAC:0' 

      if @@rowcount=0
       begin
           set @loop-'N'
       end

 --perform some other operations       

commit transaction
 --perform  wait if you must
 --    WAITFOR DELAY '00:00:05';--HH:MM:SS
END

Open in new window

0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34905289
Totally agree with the transaction point. Missed that one as I normally set this up as a job to run every minute.
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 63 total points
ID: 34906754
if you are going to use WHERE Id > 3370376 and id <= 342609235
as Lowfatspread's comment (it's good code, indeed!)

there is one point you must carefully check.
take a look on Execution Plan, if operation perform is clustered index seek or index seek, it's ok.
but if it's clustered index scan or index scan, then you should put index hint

because when working with huge table, sql sometimes decide to scan table instead of using index.
(I face this when I'm cleanup message log on my system)

here is modified version of code with index hint
please feedback if index scan still choosen in execution plan

DECLARE @BatchSize int

SET @BatchSize = 50000

declare @loop char(1)
SET @loop = 'Y'
WHILE @loop = 'Y'
BEGIN
	BEGIN TRANSACTION

	DELETE TOP (@BatchSize) dbo.Error
	FROM dbo.Error WITH (INDEX=PK_Error) -- input your index name here
	WHERE Id >= 3370376 AND Id <= 342609235
	AND OriginalMessage = 'Unknown UAC:0'

	IF @@ROWCOUNT = 0 SET @loop = 'N'

	COMMIT TRANSACTION

	WAITFOR DELAY '00:00:05'
END

Open in new window


0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now