Problem with slowly delete many filtered rows in table.

I use cursor for delete 20 milions rows from one table, but it's very slowly. I want use delete rows in a non-resource consuming manner. I don't need from transaction log, but don't know how turn off it.

Please help me!

10x

Why are you using a cursor for this? Can't you form the DELETE statement into a SET-oriented operation? Cursors are going to be much less efficient than a straight-DELETE DML operation. (FYI, TRUNCATE is the only logging-optimized DELETE operation available in the server.)

Thanks,
--R

|||

The basic process that most people do is something like the following:

declare @.row int

set @.row = 1

while @.row < somevalue

begin

delete from table where primary key between @.row and @.row + 1000

set @.row = @.row + 1000

end

This basically walks the table and allows you to delete rows in chunks which minimizes the impact of the delete operation. If this is SQL Server 2005, I would use partitioning to switch the rows you want to delete out of the table and then truncate the staging table you moved the data to.

|||

I use the cursore because i want remove filtered data from this table. I have relation with another table and result to deleted rows is round 21 milions records from 41 milions. The big table have 9 indexes. Is it a problem to slowly execute? I probe to not use the cursor. Write where clause with operator IN, but this not solve the problem. Now delete indexes, but problem stay. For cursor i use option FORWARD_ONLY, and delete records with

DECLARE abc CURSOR FORWARD_ONLY FOR
SELECT * FROM MyTable INNER JOIN

OtherTable ON(OtherTable .MyTable_ID=MyTable .ID)

OPEN abc

FETCH NEXT FROM abc

DELETE MyTable WHERE CURRENT OF abc

CLOSE abc

DEALLOCATE abc

Can you offer some idea?

|||

Hi, maybe I am wrong but do you this this could work ?

Delete MyTable where exists (select * from OtherTable where OtherTable.MyTable_ID = MyTable.ID)

|||

Thanks knuti,

I solve this problem with part delete of N records with ROWCOUNT and WAITFOR. This is slowly but tables stay unlocked.