Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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
1  
If you can provide it, a statistics-only copy of the two tables would be very useful. Also try recompiling the query with session trace flag 4199 on just in case it is a cardinality-estimation issue that is already fixed. –  Paul White Sep 26 '12 at 14:13
show 2 more comments

1 Answer 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.

share|improve this answer
    
Thanks, let me try and I'll tell you later. –  Bruno Costa Sep 12 '12 at 14:07
    
I already did that. Still not working as good as it should. –  Bruno Costa Sep 17 '12 at 16:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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