February 22, 2008 at 4:25 am
Why does it take longer and longer for the same code to run very simply I have 8,0000,000 records I want to delete from a table . I have tried a few options
Option 1 a while loop which deletes 10,000 rows per loop starting from the earliest until it hits the cut of number I have set. THIS TOOK 5 HOURS
Option 2 created an SP which found the oldest 100,000 records then deleted them. If I run this SP manually it takes 30 – 60 secs. Which I thought was much better than above. So I put this SP in a while loop to run 80 odd times thinking the time it would take would be 80 mins a huge improvement.
But every time this SP is called it takes longer and longer (36,30,32,39,37,37,123,163,155,182…and so on(In seconds)).
All the sp is doing is as follows(8860000 is just to insure I don’t delete to much)
set @recnumber = (select top 1 recnumber from
(select top 100000 recnumber from TabletodeleteFROM where recnumber < 8860000
order by recnumber asc ) TabletodeleteFROM
order recnumber desc)
delete TabletodeleteFROM where recnumber < @recnumber
please help why is it taking longer and longer
February 25, 2008 at 3:55 am
Edward,
some thoughts.....
Option1 took 5 hours - how were you looping? please don't say you used an incrementing counter?
In Option2
instead of getting the Top 1 recnumber from (top 100000 recnumbers ascending) descending
why not get the max(recnumber) from (top 100000 recnumbers ascending)
The sp is deleting the items in order,but presumably you are not bothered about which order the rows are deleted in??
If not, just limit by SET ROWCOUNT and loop. You could commit your changes after each batch with a checkpoint.
If there are indexes on the table, the regeneration of these after each delete will also affect performance (especially if there is a clustered index) - consider dropping the indexes, running the delete and then recreating the indexes.
Kev
February 25, 2008 at 4:22 am
Can you post your looping code? Chances are that you're doing the delete in one BIG transaction rather than lots of little transactions.
Try something like
SET ROWCOUNT 10000
while exists(select * from myTable)
begin
begin transaction
delete from myTable
commit transaction
end
SET ROWCOUNT 0
This will delete 10000 rows at a time from your table. The transactions will be smaller than doing it in one hit. You'll see your transaction log GROW if you don't use smaller transactions 🙂
Also, check to see if you have any foreign key constraints - SQL will check each of these to ensure they're not violated by your deletion. You could drop and recreate the constraints (if you merely disable then enable the constraints then MS SQL won't trust them in future leading to possibly poorer join performance).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply