Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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. –  Aaron Bertrand Sep 25 '12 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. –  Martin Smith Sep 25 '12 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. –  Aaron Bertrand Sep 25 '12 at 17:20
    
Actual execution plan is now provided. –  nosilleg Sep 26 '12 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. –  Martin Smith Sep 26 '12 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.

share|improve this answer
    
@SQLKiwi FYI, I've added the execution plans now. –  nosilleg Sep 27 '12 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 '12 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.

share|improve this answer
    
I have now supplied the execution plans with the question. Sorry about the poor initial pseudo query. –  nosilleg Sep 27 '12 at 11:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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