111 reputation
5
bio website
location London, United Kingdom
age
visits member for 1 year, 11 months
seen Nov 8 '12 at 11:00

Sep
27
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
I have now supplied the execution plans with the question. Sorry about the poor initial pseudo query.
Sep
27
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
@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.
Sep
27
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
@SQLKiwi FYI, I've added the execution plans now.
Sep
27
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
@MartinSmith the "joins twice" thing is text translations, once for the users desired language and again for the default language. In the case of this query they both happen to be the same. I've removed those columns and joins previously with no noticeable impact.
Sep
26
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
@MartinSmith I've updated the question with the additional query plan. And you were right, the TimeOut is still present.
Sep
26
revised IN Clause causes Execution plan to change from Nested Loops to Hash Match
added 150 characters in body
Sep
26
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
@MartinSmith I reformatted the query and have uploaded the new execution plan with the full query now included. Removing the IN clause makes the query complete in 3 seconds, so I presume the StatementOptmEarlyAbortReason="TimeOut" doesn't happen in that case.
Sep
26
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
Actual execution plan is now provided.
Sep
26
awarded  Editor
Sep
26
revised IN Clause causes Execution plan to change from Nested Loops to Hash Match
Added actual execution plan for reference and removed faux query.
Sep
26
awarded  Quorum
Sep
26
awarded  Analytical
Sep
26
awarded  Supporter
Sep
26
awarded  Student
Sep
25
asked IN Clause causes Execution plan to change from Nested Loops to Hash Match