3

I have 2 DB's on same SQL 2016 std. server. I need to delete Prod table data that matches ID's in the Arh DB table. I am doing this in batches of 10000 rows. Table's on Prod and Arh DB are with same schema. I need to delete a large amount of data (aprox. 3000000 rows). I use OLE DB command executable with SQL statement but it is slow as hell. Is there chance to speed up this.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Aleksandar
  • 55
  • 1
  • 7
  • In general one should soft-delete 1st and then eventually delete all as a whole at quite times because I think a lot of locking occurs. There are also other things to be done: https://stackoverflow.com/questions/955435/optimizing-delete-on-sql-server – Arthur May 10 '19 at 20:55

1 Answers1

1

Instead of using OLEDB Command within a data flow task (which performs delete operation row by row), you can simply use an Execute SQL Task and write a delete command with join:

DELETE T1
FROM Prod T1 INNER JOIN Arch T2
ON T1.id = T2.id

If you are handling millions of rows you can delete rows in batches:

WHILE 1=1
BEGIN

    DELETE TOP(100000) 
    FROM Prod 
    WHERE EXISTS(SELECT 1 From Arch WHERE Prod.id = Arch.id)

    IF @@ROWCOUNT < 1 BREAK

END

References

Hadi
  • 36,233
  • 13
  • 65
  • 124