2

I have a table named Product and a table named ProductPropertiesCountry.

Product is the definition of a product (description, price, productId, etc)

ProductPropertiesCountry contains restrictions on the product by country, for example IsBlocked, and attributes such as Special Price.

Product
 - ProductId
 - Description
 - Price

ProductPropertiesCountry
 - ProductId (PK)
 - CountryId (PK)
 - SpecialPrice
 - IsBlocked

I'm doing a LEFT JOIN between Product and ProductPropertiesCountry with a WHERE clause like this:

SELECT P.ProductId
FROM Product P
    LEFT JOIN ProductPropertiesCountry PPC ON 
        P.ProductId = PPC.Product AND PPC.CountryID = 1
WHERE (IsBlocked IS NULL OR IsBlocked = 0)

The problem is that the execution plan does not handle IsBlocked IS NULL efficiently and it gives me Estimated number of rows = 1 after that filter. As result, the query is slower.

This is much faster:

;WITH CATALOG
AS 
(
   SELECT P.ProductId, ISNULL(IsBlocked, 0) AS IsBlocked
    FROM Product P
        LEFT JOIN ProductPropertiesCountry PPC 
            ON P.ProductId = PPC.Product AND PPC.CountryID = 1
)
SELECT ProductId, IsBlocked
FROM CATALOG
WHERE IsBlocked = 0;

Do you have any idea for the reason of this behavior?

Any suggestion to change it to get the right estimated number of rows in execution plan?

I'm using SQL Server 2008.

  • 1
    Do you have to use left outer join, why not an inner join since both the tables share the same productid (pk) , and i think you can try replacing this WHERE (IsBlocked IS NULL OR IsBlocked = 0) with this Where isnull(IsBlocked,0) = 0 – AmmarR Sep 11 '12 at 11:07
  • I did try that, same result. Yes, I need to do a left join, because the product can have no properties for an specific country. – Bruno Costa Sep 11 '12 at 11:20
  • IsBlocked is not nullable. If I do the left join like that I would have the IsBlocked nullable for those items that are blocked or without any property on that country. Right? – Bruno Costa Sep 11 '12 at 11:31
  • @MartinSmith: I'm assuming you meant removing the IsBlocked = 0 condition from WHERE and leaving the WHERE IS NULL part only. (After all, we are dealing with a left join.) That would still result in a different output. Presently, the queries retrieve Products that have no match in PPC or those that have PPC.IsBlocked = 0. With your suggestion, the results would contain Products that are absent from PPC or have PPC.IsBlocked other than 0. However, ON P.ProductId = PPC.Product AND PPC.CountryID = 1 AND PPC.IsBlocked = 1 and leaving the WHERE IS NULL check should do the trick. – Andriy M Sep 12 '12 at 6:10
  • @AndriyM, leaving WHERE IS NULL and IsBlocked=1 will not give me the information I need from ProductPropertiesCountry. Like SpecialPrice for example. – Bruno Costa Sep 12 '12 at 14:07
1

Because you need to pull a value from PPC, you can't do a proper Semi / AntiSemi Join. If you put a unique index on PPC (CountryID, Product) INCLUDE (IsBlocked, SpecialPrice), you should get a better cardinality estimate, as it'll either be there or not, and the multi-column statistics from this index will give a better answer.

|improve this answer|||||
  • I already did that. Still not working as good as it should. – Bruno Costa Sep 17 '12 at 16:18

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.