Timeline for How can this larger subquery be (much) faster?
Current License: CC BY-SA 4.0
14 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 27, 2020 at 20:42 | comment | added | Charlieface | Can I suggest you edit your answer to show that? I'm not about to try with that screenshot. | |
| Dec 27, 2020 at 16:15 | comment | added | J.D. | @Charlieface Yes, true. I'm used to SentryOne's Plan Explorer which does the math for you and displays it in a little more intuitive manner. | |
| Dec 27, 2020 at 16:10 | comment | added | Charlieface | For Nested Loops the Actual and Estimated are different. Actual Rows is the total actual for the query, but Estimated Rows is just per execution, you need to multiply by the number of Estimated Executions. Admittedly, that is equal to 1 in this case so it makes little differences | |
| Dec 17, 2020 at 14:00 | history | edited | Hannah Vernon♦ | CC BY-SA 4.0 |
moved instructions around the execution plan to the bottom of the answer since it's only of tangential value.
|
| Dec 16, 2020 at 15:28 | comment | added | J.D. | @dietervdf Also any other questions, feel free, and I'll do my best to explain. I probably clean up this answer and structure it a little better now that we got through the end of the tunnel. :) | |
| Dec 16, 2020 at 15:27 | comment | added | J.D. | @dietervdf Great! No problem! If you want to update your post with the new execution plan, I'd be curious to look it over to see how it changed and could explain why it improved. But if not, no biggie. You can try using a CTE too if you want something a little "cleaner" and it might help because any query rewrite can result in a different execution plan, but it's possible it might not help too because of your Cardinality Estimate issue. CTEs are almost exactly like subqueries in nature for the fact that they don't materialize the data where as TempTables does materialize the data. | |
| Dec 16, 2020 at 14:21 | vote | accept | dietervdf | ||
| Dec 15, 2020 at 23:56 | history | edited | J.D. | CC BY-SA 4.0 |
added 1744 characters in body
|
| Dec 15, 2020 at 23:38 | history | edited | J.D. | CC BY-SA 4.0 |
added 1389 characters in body
|
| Dec 15, 2020 at 23:29 | history | edited | J.D. | CC BY-SA 4.0 |
added 1389 characters in body
|
| Dec 14, 2020 at 23:27 | history | edited | J.D. | CC BY-SA 4.0 |
added 288 characters in body
|
| Dec 14, 2020 at 23:16 | history | edited | J.D. | CC BY-SA 4.0 |
added 193 characters in body
|
| Dec 14, 2020 at 23:10 | history | edited | J.D. | CC BY-SA 4.0 |
added 193 characters in body
|
| Dec 14, 2020 at 23:04 | history | answered | J.D. | CC BY-SA 4.0 |