April 27, 2010 at 9:01 am
I want to know if a stored proc with a IF within will re-execute the execution plan or use the saved execution plan?
IF @language = 1
SELECT NAME_L1
FROM Table1
ELSE
SEECT NAME_L2
FROM Table1
Let say this stored proc is call 10 consecutive time with @language = 1 before a call is made with @language = 2 and then after another 10 consecutive calls with @language = 1.
Will the saved execution plan will be used?
Thank you
Martin
April 27, 2010 at 9:12 am
According to the MCSE Database Design and Implementation:
Before a stored proc is created, the command syntax is checked for accuracy. If no errors are returned, the proc's name is stored in the SysObjects table and the proc's text is stored in the SysComments table. The first time the stored proc is run, an execution plan is created and the stored proc is compiled. Subsequent processing of the compiled stored proc is faster because sql server does not recheck the command syntax, re-create an execution plan, or recompile the procedure. The cache is checked first for an execution plan before a new plan is created. An attempt to use the existing execution plan is made before creating a new one.
Hope that helps,
Chieko
April 27, 2010 at 9:39 am
The cached execution plan will be reused, providing nothing else has happened to throw it out of cache1. This may or may not be a good thing.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
1) Schema change, stats update, aging of plan out of cache, alter database statement, explicit cache flush etc.
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply