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.

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.

Problem with Slowly Changing Dimension-transformation

Hi,

I have a problem with the SCD-transformation in SSIS. I have a variable that holds the batchid for the current batch and I want to add this variable to the datapipline in the Data Flow Task.

This is done by using a Derived Column, so far so good. The problem occurs in the Slowly Changing Dimension transformation where I do som evaluations of changed columns BUT I don′t want to do any evaluation of the batchid-variable because then all historical batchid will be updated.

I only want to update the batchid for row that have changed in the current batch.

Is it possible to do this in any way without adding the Derived Column after the SCD transformation?

Thank for any help!!

Patrick

I am not sure I fully understand your scenario, anyway SCD by itself does not evaluate/update data, it is actually a change detection transform, meaning it detects changes on the incoming rows by comparing them with those in the dimension, and then routes them to various outputs accordingly.

From what I see you'll need derived column after SCD (e.g. to hook with SCD's ChangingAttributeOutput & HistoricalAttributeOutput), so as to update your batchID only on changed rows, it is also to add this new col info into pipeline.

let me know if I do not answer your question.

wenyang

|||When setting up the Slowly Changing dimension just don't choose set the batchIDs in the incoming and dest objects... then it won't do a lookup on them.

Problem with Slowly Changing Dimension-transformation

Hi,

I have a problem with the SCD-transformation in SSIS. I have a variable that holds the batchid for the current batch and I want to add this variable to the datapipline in the Data Flow Task.

This is done by using a Derived Column, so far so good. The problem occurs in the Slowly Changing Dimension transformation where I do som evaluations of changed columns BUT I don′t want to do any evaluation of the batchid-variable because then all historical batchid will be updated.

I only want to update the batchid for row that have changed in the current batch.

Is it possible to do this in any way without adding the Derived Column after the SCD transformation?

Thank for any help!!

Patrick

I am not sure I fully understand your scenario, anyway SCD by itself does not evaluate/update data, it is actually a change detection transform, meaning it detects changes on the incoming rows by comparing them with those in the dimension, and then routes them to various outputs accordingly.

From what I see you'll need derived column after SCD (e.g. to hook with SCD's ChangingAttributeOutput & HistoricalAttributeOutput), so as to update your batchID only on changed rows, it is also to add this new col info into pipeline.

let me know if I do not answer your question.

wenyang

|||When setting up the Slowly Changing dimension just don't choose set the batchIDs in the incoming and dest objects... then it won't do a lookup on them.sql