Simple change causes SQL query execution time to dramatically increase

Question

I run the following SQL query on my Microsoft SQL Server (2012 Express) database, and it works fine, executing in less than a second:

SELECT
  StringValue, COUNT(StringValue)
FROM Attributes
WHERE
  Name = 'Windows OS Version'
  AND StringValue IS NOT NULL
  AND ProductAssociation IN (
    SELECT ID
    FROM ProductAssociations
    WHERE ProductCode = 'MyProductCode'
  )
GROUP BY StringValue

I add a filter in the inner query and it continues to work fine, returning slightly less results (as expected) and also executing in less than a second.

SELECT
  StringValue, COUNT(StringValue)
FROM Attributes
WHERE
  Name = 'Windows OS Version'
  AND StringValue IS NOT NULL
  AND ProductAssociation IN (
    SELECT ID
    FROM ProductAssociations
    WHERE ProductCode = 'MyProductCode'
    AND ID IN (
      SELECT A2.ProductAssociation
      FROM Attributes A2
      WHERE A2.Name = 'Is test' AND A2.BooleanValue = 0
    )
  )
GROUP BY StringValue

But when I add a flag variable to enable me to "turn on/off" the filter in the inner query, and set the flag to zero, the query seems to execute indefinitely (I left it running about 5 minutes and then force cancelled):

DECLARE @IsTestsIncluded bit
SET @IsTestsIncluded = 0

SELECT
  StringValue, COUNT(StringValue)
FROM Attributes
WHERE
  Name = 'Windows OS Version'
  AND StringValue IS NOT NULL
  AND ProductAssociation IN (
    SELECT ID
    FROM ProductAssociations
    WHERE ProductCode = 'MyProductCode'
    AND (
      @IsTestsIncluded = 1
      OR
      ID IN (
        SELECT A2.ProductAssociation
        FROM Attributes A2
        WHERE A2.Name = 'Is test' AND A2.BooleanValue = 0
      )
    )
  )
GROUP BY StringValue

Why? What am I doing wrong? I swear I've used this pattern in the past without a problem.

(When I set @IsTestsIncluded = 1 in the final query above, the filter is skipped and the execution time is normal - the delay only happens when @IsTestsIncluded = 0)

---

EDIT

As per Joel's request in the comments, here is the execution plan for the first query:

And here is the execution plan for the second query:

(I can't post an execution plan for the 3rd query as it never completes - unless there is another way to get it in SSMS?)

Answer 1

Good answer from Joel +1

OR is hard to optimize

Going back to the second
Where in is hard for the optimizer to optimize
Consider JOIN over all those where in
This still has an OR that may cause bad query plan but it gives the optimizer a better chance at minimizing the OR

SELECT A1.StringValue, COUNT(A1.StringValue)
 FROM Attributes A1
 JOIN ProductAssociations PA
   ON PA.ID = A1.ProductAssociation
  AND A1.Name = 'Windows OS Version'
  AND A1.StringValue IS NOT NULL
  AND PA.ProductCode = 'MyProductCode'
 JOIN Attributes A2 
   ON A2.ProductAssociation = A1.ProductAssociation 
  AND (     @IsTestsIncluded = 1
        OR (A2.Name = 'Is test' AND A2.BooleanValue = 0)
      )
GROUP BY A1.StringValue  

if you refactor @IsTestsIncluded you can maybe do this

SELECT A1.StringValue, COUNT(A1.StringValue)
 FROM Attributes A1
 JOIN ProductAssociations PA
   ON PA.ID = A1.ProductAssociation
  AND A1.Name = 'Windows OS Version'
  AND A1.StringValue IS NOT NULL
  AND PA.ProductCode = 'MyProductCode'
 LEFT JOIN Attributes A2 
   ON A2.ProductAssociation = A1.ProductAssociation 
  AND A2.Name = 'Is test' 
  AND A2.BooleanValue = 0
WHERE ISNULL(@IsTestsIncluded, A2.ProductAssociation) is NOT NULL
GROUP BY A1.StringValue 

Answer 2

Why? What am I doing wrong?

You are trying to compile a query that needs to satisfy multiple distinct conditions, based on the variable. The optimizer must come up with one plan that works in both cases.

Try to avoid this like the plague. Just issue two queries, one for one condition one for the other, so that the optimizer is free to optimize each queries separately and compile an execution plan that is optimal for each case.

A lenghty discussion of the topic, with alternatives and pros and cons: Dynamic Search Conditions in T‑SQL

Answer 3

Try this:

SELECT
  a.StringValue, COUNT(a.StringValue)
FROM Attributes a
INNER JOIN ProductAssociations p ON a.ProductAssociation = p.ID
    AND p.ProductCode = 'MyProductCode'
LEFT JOIN Attributes a2 ON a2.ProductAssociation = p.ID
    AND a2.Name = 'Is Test' AND a2.BooleanValue = 0       
WHERE
  Name = 'Windows OS Version'
  AND StringValue IS NOT NULL
  AND COALESCE(a2.ProductAssociation, NULLIF(@IsTestsIncluded, 1)) IS NOT NULL
GROUP BY a.StringValue

The coalesce/nullif combination is not the easiest-to-follow thing I've ever written, but it should be functionally equivalent to what you have as long as the join conditions match 0 or 1 record on the joined table.