-2

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?

  • 1
    Can you add the table definition and execution plan to your question? – Tom V - try topanswers.xyz Feb 13 at 6:39
  • If there are any indices or triggers defined on the table, please add their definitions as well. – Andriy M Feb 13 at 7:39
2

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)
|improve this answer|||||
1

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.