July 11, 2011 at 9:17 am
will do 😀
The Fastest Methods aren't always the Quickest Methods
July 11, 2011 at 10:46 am
The plan compiled for that will have to have a check the second condition. That's because the plan has to be safe for reuse and if the plan had no filter (because the first condition happened to be true on the first execution) it would give incorrect results for other parameter values.
Now whether the execution engine can simplify at runtime is another matter, but I would think not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2011 at 10:51 am
GilaMonster (7/11/2011)
The plan compiled for that will have to have a check the second condition. That's because the plan has to be safe for reuse and if the plan had no filter (because the first condition happened to be true on the first execution) it would give incorrect results for other parameter values.Now whether the execution engine can simplify at runtime is another matter, but I would think not.
I had forgotten about that one. Most of my reports have to use the WITH RECOMPILE because the date ranges vary too much.
Because of that I'm able to use short-circuits in the plans to ignore "non" filters.
July 11, 2011 at 12:47 pm
Ninja's_RGR'us (7/11/2011)
I had forgotten about that one. Most of my reports have to use the WITH RECOMPILE because the date ranges vary too much.Because of that I'm able to use short-circuits in the plans to ignore "non" filters.
Do note that the recompile for unsafe plans is SP2+ only (it has a nasty bug RTM-SP1, was removed in SP1, added back in SP2)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2011 at 2:08 pm
GilaMonster (7/11/2011)
Ninja's_RGR'us (7/11/2011)
I had forgotten about that one. Most of my reports have to use the WITH RECOMPILE because the date ranges vary too much.Because of that I'm able to use short-circuits in the plans to ignore "non" filters.
Do note that the recompile for unsafe plans is SP2+ only (it has a nasty bug RTM-SP1, was removed in SP1, added back in SP2)
Wow didn't know that.
Does it apply only to statement level recompile or proc level? Since my recompile hit are pretty insignificant I always use it at the proc level...
July 11, 2011 at 2:22 pm
Statement level.
To get the plan optimisation for queries like the following, the recompile has to be a hint on the query: OPTION(RECOMPILE)
SELECT ...
WHERE (Col1 = @var1 OR @var1 IS NULL)
AND (Col2 = @var2 OR @var2 IS NULL)
...
In SQL 2005 that always got a crap plan (technical term), because the plan had to be safe for reuse and hence could not seek on any of the columns (if it did and the next run passed the parameter as NULL it would return incorrect results)
SQL 2008 changed the optimiser's safe plans rule so that if OPTION(RECOMPILE) was added on the query the optimiser could produce a plan that was not safe for reuse, because it would never be reused. However there was a bug. If there were two different instances of the query running at the same time, one could incorrectly use the other one's plan. A bad thing I'm sure you can see.
In 2008 SP1 that was fixed by reverting the behaviour back to what 2005 did.
2008 SP2 reintroduced the optimisation, correctly we hope.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply