Sql-server – LEFT JOIN / WHERE clause with is null and false Estimated Number Of Rows

optimizationsql serversql-server-2008

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.

Best Answer

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.

Related Question