Link to home
Start Free TrialLog in
Avatar of srionline2k6
srionline2k6Flag for United States of America

asked on

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

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,
Avatar of EvilPostIt
EvilPostIt
Flag of United Kingdom of Great Britain and Northern Ireland image

Avatar of srionline2k6

ASKER

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.
use TOP

e.g.


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

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

 it been on the depreciated list since 2005

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
Avatar of mkobrin
mkobrin

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
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
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
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.
@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

SOLUTION
Avatar of mkobrin
mkobrin

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Totally agree with the transaction point. Missed that one as I normally set this up as a job to run every minute.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial