January 23, 2012 at 6:03 am
HI,
Option1: WITH RECOMPILE - only Stored Procedure
Option2: Option (Recompile) - only SQL statement
Generally Recompile features SP and TSQL batch does not looking existing Execution plan in query optimizer it will create new plan at every time whenever execute SP or SQL Statements. Should I use WITH RECOMPILE option in existing stored procedure which were migrate from SQL Server 2000 database? if I am using SP level with recompile option does really getting performance improvement?
January 23, 2012 at 7:32 am
Just migrating stored procedures from an older version to new does not require (nor benefit) from with recompile option.
Generally the idea of a stored procedure re-using the compiled plan is a good one, provided that the plan it generates on first call is suiotable for later calls.
You can hit situations where the parameters passed to a stored proc on first call vary (with respect to howe many rows they identify) widely from the first call to l;ater calls, thus the plan could be less than ideal on later calls.
In my sexperioence the vast majority of the time you shopuld not be specifiying with recompile, however you do find cases where for example the parameter values on first call are VERY restrictive (maybe you allow a wildcard value and on the first call it matches .001% of rows in the table(s). Then later calls pass avalue that matches say 50% of rows you may benefit.
Stored procs called frequently and on each call processing similar numbers of rows tend not to benefit.
MIke John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply