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?

  • 3
    Can you post the actual (not estimated) execution plan somewhere? Trying to reverse engineer a query plan from a word problem is not very fun. Sep 25, 2012 at 17:11
  • 2
    What is AnIDColumn? This doesn't exist as a column in your CTE so the query you posted would be invalid. Please give your actual query. Also definition of [AView] would be good. Sep 25, 2012 at 17:18
  • 2
    Agreed with Martin. Post what you have, not some dumb-downed version. We're smart people and we can read code. We can't read the things you've edited out for brevity. Sep 25, 2012 at 17:20
  • Actual execution plan is now provided.
    – nosilleg
    Sep 26, 2012 at 16:18
  • 1
    Could you also supply the entire query text? It is truncated in the plan. I would probably divide this query up into several smaller ones using a temporary table to materialize the intermediate results though. Sep 26, 2012 at 17:21

2 Answers 2


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.

  • @SQLKiwi FYI, I've added the execution plans now.
    – nosilleg
    Sep 27, 2012 at 10:59
  • @PeteCarter Adding the OPTION increased the non-IN from 3 to 7 seconds, and the IN query was closer to 4 minutes. STATISTICS IO is reporting 10s of thousands of logical reads without IN and millions with IN. Putting the WITH(INDEX(0)) on the VIEW doesn't seem to help.
    – nosilleg
    Sep 27, 2012 at 11:36

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.

  • I have now supplied the execution plans with the question. Sorry about the poor initial pseudo query.
    – nosilleg
    Sep 27, 2012 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.