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

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.