November 20, 2009 at 3:29 am
I have a stored procedure that was created with the 'WITH RECOMPILE' option
only one parameter is passed into it (an integer) as I wasnt doing anything that (i thought) would change its execution plan, my understanding is that if i take out the 'with recompile' option, it should execute faster, but it actually executes slower
with recompile duration was 570
without recompile duration was 750
is my general understanding of this option wrong ?
November 20, 2009 at 5:56 am
My guess is that you have parameter sniffing problems. Post the proc?
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
November 23, 2009 at 3:04 am
I tried running it several times with different integer vales passed in, the first 2 were slower, but the third was faster. The integer values i passed in were pretty close in value and they are likely to be in the 'real world' situation, but to be honest we're only talking 100 ms or so, and i managed to get the overall execution time of the query from around 6 seconds to well under 1 second anyway, so im happy with that level of improvement. However, I did think that leaving out the WITH RECOMPILE option would be slightly faster as its not recompiled upon each execution
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply