T-SQL Query performance

Question

We are using SSIS package to execute the following statement.

SQL statement

delete from ABC
            OUTPUT DELETED.[ABC_Id], 5
                           into XYZ (OId, OCode)
where ([ABC_Id] = ?)

Column Types

ABC_Id: uniqueidentifier
OCode:  int
OId:    uniqueidentifier

Currently the above query is taking 2 min for 100'000 records. Can anybody provide me with any hints on how to speed up the above statement?

Answer 1

I’m assuming you have a Data Flow with 100,000 records and an OLEDB Command transform which executes that update statement. That results in 100,000 individual update statements running.

Instead use an OLEDB destination with fast load enabled to write to a stage table (some table which you have previously truncated in an Execute SQL task) to insert the 100,000 rows. Then do an Execute SQL task which does:

delete a
from ABC a
join StageTable s on s.ABC_Id = a.ABC_Id
            OUTPUT DELETED.[ABC_Id], 5
                           into XYZ (OId, OCode)

Answer 2

First advice: you can speed up query by applying index to columns in where clause, i.e. ABC_Id.

Second advice: indexes slow down deletion and insertion of rows in a table, so if you abused indexes (and defined many on one table), deletion also may be slowed down by these.

Generally, there is no obvious answer: delete ... where can be thought as steps:

1. Find rows to delete - it can benefit from indexes

2. Delete found rows - this suffer from indexes

So you can benchamrk, which step is more important to your query.

For reference: More indexes, slower DELETE, there you can read:

In theory, we would expect the best delete performance for a table without any indexes—as it is for insert. If there is no index, however, the database must read the full table to find the rows to be deleted.