1

Possible Duplicate:
DELETE Command is too slow in a Table with Clustered Index

I have the following DELETE query running on my SQL Server 2005 database:

DELETE
    pa
FROM
    dbo.ProductAttribute pa
INNER JOIN
    dbo.Product p ON pa.ProductID = p.ProductID
INNER JOIN
    @UpdatedProducts up ON p.ProductID = up.ProductId
LEFT JOIN
    dbo.ProductOption pos ON pa.ProductOptionID = pos.StartProductOptionId
LEFT JOIN
    dbo.ProductOption pof ON pa.ProductOptionID = pof.FinishProductOptionId
WHERE
    p.ProductTypeID = 1
AND
    pos.StartProductOptionId IS NULL
AND
    pof.FinishProductOptionId IS NULL

This query is taking a long time to execute for my data set (~ 18 minutes).

Here's some context:

  • @UpdatedProducts is a table variable with ~90K rows
  • All of the columns I am joining on are indexed
  • The DELETE query is affecting ~3K rows

Here are my findings so far:

  • Replaced @UpdatedProducts with a temp table (due to its size) -- this made little difference.
  • When I convert the query to a SELECT, it runs very quickly (a few seconds). So I'm not sure there is a problem with my JOIN, as presumably this would run the same way when doing a SELECT or DELETE?
  • There are several FK constraints that would need to be validated to allow the DELETE to succeed -- essentially we need to ensure that there are no more entries in dbo.ProductOption which are referencing the rows we are trying to delete in dbo.ProductAttribute (this is what the IS NULL checks in the WHERE clause are checking for).
  • I looked at the query plan, and the most significant cost is "Clustered Index Delete" with a cost of 59% -- most of the other steps have a cost between 0%-3%.

Any recommendations about how I could speed up this query?

Community
  • 1
  • 1
LeopardSkinPillBoxHat
  • 28,915
  • 15
  • 75
  • 111
  • Do you have any triggers on delete? – Matthew Jan 02 '13 at 23:43
  • @MatthewPK - there are no triggers on the `dbo.ProductAttribute` table. – LeopardSkinPillBoxHat Jan 02 '13 at 23:48
  • 1
    table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan. Temporary tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices. http://msdn.microsoft.com/en-en/library/ms175010.aspx – bummi Jan 02 '13 at 23:56
  • If you could post the Actual Query Plan, that would be very helpful. Also, how many rows are in the ProductAttribute table, and what it the Clustered Index on that table? – RBarryYoung Jan 03 '13 at 00:05
  • @RBarryYoung - The `ProductAttribute` table has ~58K rows. The clustered index is the primary key `ProductAttributeID` which is not referenced in the query above. I will re-run the query and post the Actual Query Plan. – LeopardSkinPillBoxHat Jan 03 '13 at 00:12
  • @bummi . . . If that is the case, why is the `select` running so much faster than the `delete`? – Gordon Linoff Jan 03 '13 at 01:20
  • @LeopardSkinPillBoxHat : Most recent or required data is inserted into a new temp table. Then drop this legendary table and rename the temp table...Ugly but works faster than keep on deleting on conditions.... – bonCodigo Jan 03 '13 at 05:23

0 Answers0