January 18, 2012 at 3:43 am
CELKO (1/17/2012)
As an aside, a few other SQLs have optimization for the patternWHERE COALESCE (@param, some_column) = some_column. I one case, the compiler keeps multiple plans and pulls out the appropriate one from a list of 16.
I do not know if T-SQL 2012 is planning anything like this.
Nothing new in 2012 for this, no.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 18, 2012 at 4:00 am
Rayven (1/17/2012)
I don't want to use the option RECOMPILE.
Don't rush to judgement on this. Unless the queries in question are being executed thousands of times per second, and execute for only a few milliseconds each, the overhead of recompiling will typically be negligible. Simplification occurs before trivial plan and full optimization, so the redundant sections (where @param = 0) will be removed completely. The parameter embedding optimization does require at least 2008 SP1 CU5, but it is very often the optimal solution for reporting-type queries.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 19, 2012 at 7:41 am
Rayven (1/18/2012)
Brendan: The only thing which makes multiple procedures for parameters a pain is where I have 5 or 6 optional parameters with multiple combinations - it would mean hundreds of procedures, lol. 😀
Oh...lol...makes sense.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply