March 11, 2009 at 6:28 pm
Hi all,
It is possibe to optimize the following delete?
As soon as i remove "OR (PI.IsBit = 0x01 AND PI.ProductId = 145 )" it used an index seek, otherwise clustered index scan is used.
Thanks
DELETE PI
FROM dbo.ProductPicture AS PI
INNER JOIN dbo.Product AS P WITH (NOLOCK) ON PI.ProductId = P.ProductId
WHERE (PI.IsBit = 0x00 AND PI.ProductId = 145 )
OR (PI.IsBit = 0x01 AND PI.ProductId = 145 )
March 11, 2009 at 6:43 pm
What index(es) do you have on those tables? which index is used for the seek? which for the scan? It looks as though the difference in the query is scanning for specific IsBit values and that the Product table is joined only to limit the delete to Picture rows associated with a Product. One can only guess without the index definitions.
March 11, 2009 at 8:57 pm
There's a couple of things I can suggest:
1. Why is the join to the product table there? That's not used anywhere in the statement but will be forcing SQL Server to consider the implications of that join in arriving at a execution plan (or is this an excerpt of the complete statement, in which case please show the whole statement).
2. I'm guessing the IsBit field is a bit, so it can only have a value of 0 or 1 (or maybe null, but I'd expect that to be a "not null" field). When either of the "if IsBit ..." statements is there then SQL Server expects half the records to be omitted (depending on the stats), but when you're checking for both 0 and 1 it means all records are to be returned with that productid, so it decides it's cheaper to do a scan.
3. How recently were the stats updated? Does it still happen after updating all the stats on this table.
4. The number of records in the table will be a big factor. If the table has many thousands/millions of rows then it's far more likely to use an index, but if it's only a few pages then it may decide it's cheaper to do a scan, seeing as it's a delete which means it has to access the data pages anyway.
March 11, 2009 at 9:21 pm
You can also drop the NOLOCK hint. It is ignored any way on INSERT, UPDATE, and DELETE statements.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply