WITH RECOMPILE

  • 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 ?

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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