CODEWITHSUNDEEP
sqlsql-serverssisetloledbcommand
Aleksandar
Aleksandar

Reputation: 55

Which SSIS executable is best practise to use for delete command

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.

Upvotes: 3

Views: 118

Answers (1)

Hadi
Hadi

Reputation: 37348

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

Upvotes: 1

Related Questions