meta user
network profile
| bio | website | |
|---|---|---|
| location | London, United Kingdom | |
| age | ||
| visits | member for | 8 months |
| seen | Nov 8 '12 at 11:00 | |
| stats | profile views | 7 |
|
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 |
