Timeline for Fixing parameter-sniffing of an embedded query via Recompile
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 7, 2020 at 22:23 | history | edited | CommunityBot |
added [query-performance] to 2412 questions - Shog9 (Id=1924)
|
|
| Jun 26, 2017 at 13:04 | vote | accept | Brian | ||
| Jun 25, 2017 at 22:01 | answer | added | Joe Obbish | timeline score: 3 | |
| Jun 24, 2017 at 15:29 | comment | added | Brian | @MichaelGreen: No, that gives the same result. | |
| Jun 24, 2017 at 10:01 | comment | added | Michael Green |
Do you get a better plan with set @cnt = (select ...)?
|
|
| Jun 23, 2017 at 20:51 | comment | added | Brian | @JoeObbish: Only 3 filters. My current fix is to just use if statements (in my question, this corresponds to "conditionally replacing it with Query C.") However, I'd still love to know exactly how to convince SQL to figure this stuff out on its own. I was expecting to be able to use the Sommarskog's Static SQL - The Basic Technique. However, as soon as I tried to store intermediate results, the technique failed. | |
| Jun 23, 2017 at 15:30 | comment | added | Brian | @sp_BlitzErik: I try to avoid dynamic SQL unless absolutely necessary. It's far more annoying to maintain. This query isn't executed often enough for me to mind paying for a recompile. As an aside, one not-so-obvious way to fix this (which makes me cringe) is to use Query A in its own stored procedure. | |
| Jun 23, 2017 at 15:14 | comment | added | Erik Reasonable Rates Darling | Why not use Dynamic SQL? sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries | |
| Jun 23, 2017 at 15:09 | history | asked | Brian | CC BY-SA 3.0 |