I have a table (Period) linked to about 6-7 other tables. When I DELETE a period, a CASCADE DELETE is used to delete the related records in the other tables.

Two of the tables (Result, Amount) have about 90 million rows. They are almost identical in nature. They both have:

  1. A composite PK and a single decimal 'Value' column.
  2. Both are joining tables where Period is one side of the relationship.
  3. In addition to the PK clustered index, both have a NC index based on PeriodId and including the 'Value' column.

SELECTs that filter on PeriodId are very fast. I find however that DELETEs take too long (2-3 seconds) and that the main culprit is the Amount table.

I can't understand, given their identical setups, why one table takes up ~75% of the DELETE time. You can see from the picture of the query plan that the culprits are the two sorts. What would cause the Result table to DELETE so quickly, but the AMOUNT table to use these 'SORT's?

enter image description here

[Part of the query plan showing the RESULT delete on the top row and the AMOUNT delete on the bottom. Igore the middle part]

  • 1
    Can you post the statement that you are using for the delete? – Aaron Oct 27 '15 at 5:52
  • 1
    Also post the CREATE TABLE statements, for all 3 tables. – ypercubeᵀᴹ Oct 27 '15 at 7:13
  • Deleting tuples may require locating the tuple (if a WHERE predicate is used), deleting the tuple, deleting referencing tuples, and updating any indexes on the table. So it really depends heavily on the schema and indexes in your database. – beldaz Oct 27 '15 at 7:34
  • Do you know for a fact that it is taking up 75% of the DELETE time, or are you basing that on the estimated costs of this query plan? – Mark Sinkinson Oct 27 '15 at 7:39
  • You can refer to this post. It has greater performance. if you are not using it already. dba.stackexchange.com/questions/1750/… – kostas patsikas Oct 27 '15 at 11:22

Your Answer

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

Browse other questions tagged or ask your own question.