IN Clause causes Execution plan to change from Nested Loops to Hash Match

Question

I'm tuning a query and have discovered some behaviour I'm not clear about.

If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes.

There only 7 rows returned in the result since there are only 7 items in the IN clause.

Thinking this was a bit odd, I started trying to get both parts of the query behaving individually, however no matter what I've tried the IN clause causes the long execution time.

Looking at the execution plan I can see that it's taking some joins and converting them from Nested Loops to Hash Match.

I've diced up the SQL so the single SELECT is now split between a View, CTE and SELECT. I would have expected the IN clause to not impact what is happening inside of the View, but it is.

The execution plans are: with the IN clause and without the IN clause. Please be gentle regarding the quality of the query; it's a work in progress on an inherited project and I'm by no means a proper DBA.

Selecting all 1.8 million rows from the View takes ~1 minute; executing the CTE by itself takes ~3 seconds; executing the full query takes ~3 minutes.

How can I convince SQL Server to maintain the Nested Loops join instead of switching to Hash Match when I add in the IN clause? Or is there something else that I should be trying?

Answer 1

Well, you can force nested loops by using OPTION(LOOP JOIN) however, I would advise using OPTION(LOOP JOIN, MERGE JOIN) this is working with the optimiser rather than against it and is saying do what you want as long as its not a hash match.

But I would also dig a little deeper. With the IN clause it is possible that SQL is performing multiple index scans as opposed to a single table scan, so you may need to use an WITH(INDEX(0)) instead.

If you add SET STATISTICS IO at the top of the batch and run it with and then without the IN clause and check the number of physical and logical reads for each, as well as the read-ahead reads, this will prove or disprove my theory.

Answer 2

Put an index on: (HasAnswered, AnIDColumn) from the appropriate underlying table and INCLUDE any other columns you reference.

Presumably you meant to group by AnIDColumn inside your CTE. So without better indexing options, it's likely to calculate all the aggregates first and then filter them. You want to persuade the filter on AnIDColumn to happen first, and that's best done by adding your ideal index.

You're asking about joins, when your query doesn't show any - the comments are right that your question is incomplete.