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
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
I've diced up the SQL so the single
SELECT is now split between a
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?