When faced with a procedure that looks like this:
CREATE PROCEDURE dbo.TestProc (@TestValue INT) AS BEGIN IF @TestValue = 1 BEGIN SELECT * FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID WHERE soh.SalesOrderID = @TestValue END ELSE BEGIN SELECT * FROM Production.Product AS p JOIN Production.ProductDocument AS pd ON p.ProductID = pd.ProductID WHERE p.ProductID = @TestValue END END
I used to suggest creating a wrapper procedure in order to avoid the recompiles that occur when the different paths through the IF statement are taken by the optimizer. I mentioned that recently on a post over at SQL Server Central. Gail Shaw (blog | twitter) asked me why I thought there would be a recompile. She said that the optimizer took the query as a whole and created plans for it. I never seem to learn my lesson, so I suggested that she might be wrong about that. Gail being who she is, immediately went and made up a quick little test with simple queries. Sure enough, no recompiles. Ah, but I figured she was benefiting from trivial plans or something, so I created the procedure above to test the theory out. Each query, while relatively simple, goes through a full optimization process, so no trivial plans involved…
Short answer, Gail’s right and I was wrong. I don’t know where I got the idea that this type of query caused recompiles. I have now tested it on 2000, 2005 and 2008, no recompiles anywhere. I also cleared the cache, ran the procedure once, and then checked the cache using this query:
DBCC freeproccache ; EXEC dbo.TestProc @TestValue = 1 ; SELECT deqp.query_plan FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
This is what I saw:
 
I swear, I’d never seen a plan like this before. I guess partly because I tend to only look at the actual execution plan rather than the estimated plan. It’s pretty clear that the optimizer just walked through and determined that there was more than one query involved and built a plan for them, including the conditional IF statement. If I’d just bothered to look at the estimated plan one time, I could have avoided my error of understanding.
To all those I’ve suggested wrapper procs in order to avoid recompiles… oops, sorry.
Wrapper procs are often a good idea though. Not because of the recompiles though, because of the lack thereof. http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Well, I too thought that it could cause problems if you have different queries being executed based on an IF condition. This is what I thought, since the stored proc can have multiple plans based on the path the query takes, it could cause in getting a bad query plan when getting executed.
Believe me, I’m not dismissing the idea of a wrapper proc. If nothing else, it can act to reduce complexity and increase readibility. But I really did think it affected recompiles. I do love learning new stuff.
So the different paths don’t cause a recompile, but can suffer from bad cached plans?
However they would benefit from a recompile!
Yeah, they can suffer from bad cached plans, just like anything else. With statement recompiles now, you don’t need to sweat the isssue that one will cause problems for the other.
Why not?
Well, that said, yes, it can. While that statement is recompiling, other processes will be waiting…. but now I’m thinking. My initial thought was, with a statement recompile, you wouldn’t need to worry about a different plan for the other statement, but, is that completely true? If these are linked through the IF clause, does that make them a single statement?
Ah, I see what you’re saying. A recompile of one statement won’t cause problems for someone running the other branch.
Maybe…. Needs testing. I remember some nasty cache contention problems on SQL 2000, where connections had to wait for the compile to complete before they could run, but don’t know how 2005’s statement-level changed that.
Heh. A person could lose a lot of money betting against Gail on stuff like this … 🙂
Especially since I never take a bet unless I know I’m going to win.
Some developers at my previous company learnt that the hard way, and I got free lunch on a number of occasions.