Why Is This Query Plan So Much Different Than Previous Query Plan

DBNewbie_2007 2016-11-30 17:10:44

[link text][1]I have two queries… "Inner_DivisionOnly" and this one, "ProblemArea". The only difference between the two queries is the inner select on Schema1.Object7. The "faster" query looks like this:

SELECT DISTINCT Column24
FROM Schema1.Object7
WHERE Column25 = ?

and the slower query ("ProblemArea") has this:

SELECT DISTINCT Column24
FROM Schema1.Object7
WHERE Column25 = ?
AND Column26 = ?

Also, the "Inner_DivisionOnly" has a predicate on Object11, while "ProblemArea" does not, but I do not see how Object11 is affected by Object7 and why SQL Server would make such drastic differences in the plans. (This is repeatable on both SQL Server 2008 and 2014).

DBNewbie_2007 2016-11-30 19:41:49
OK… still playing with this. The difference between the two queries appears to be the logical join type.

• "Inner_DivisionOnly" is using a HASH MATCH.

• "ProblemArea" is using a NESTED LOOP, which is causing the Object11 to return the huge number of records.

In the "ProblemArea" query:

• Removing the following in the final WHERE clause caused the query to use a HASH MATCH.

    AND Object10.Column27 IN (
    SELECT DISTINCT
            Column24
    FROM    Schema1.Object7
    WHERE   Column25 = ?
        AND Column29 = ?)

• Adding a new non-clustered index also helped resolve the NESTED LOOP.