query optimization - Why does a stored procedure run faster with "WITH RECOMPILE" even after it was just recompiled?

Question

I had a stored procedure that took 40 seconds to run. So I ran

exec myproc WITH RECOMPILE

and it took less than a second. I tried running exec myproc directly after that and it took 2 seconds. I ran it several times like this and it consistently took 2 seconds. I then added WITH RECOMPILE back into my query and it once again took under a second to run.

I ran the stored procedure with the exact same parameters every time. I would think that, after having used WITH RECOMPILE the first time, it would just use the plan that was just created for subsequent runs that did not specify WITH RECOMPILE. If that assumption is correct, then it would seem like it takes longer to look up the execution plan that it does to just create a new one from scratch.

So what's really going on here?

EDIT: I would still love to know the answer to my question, which was how running the statement with the hint could possibly be faster than running it without the hint. But I took Aaron's advice and switched to using OPTION (RECOMPILE) on the select statement and it runs even faster so that's clearly my best option.

question from:https://stackoverflow.com/questions/65835738/why-does-a-stored-procedure-run-faster-with-with-recompile-even-after-it-was-j

Answer 1

Waitting for answers