SProc takes MUCH longer than raw code 1

[RiddleMeThis]

Could someone please explain to me the difference in how a stored proc runs compared to just raw sql outside of a stored proc?

I created a query which creates 2 temp tables and loads them with data, then creates a 3rd temp table, inserts some rows with titles in the first column, and populates the 2nd and 3rd columns with counts and sums from the first 2 temp tables.

This query takes 1 second to run, but when put into a SProc with the date being passed as a parameter it takes 1 minute and 16 seconds.

Obviously this is a significant difference, especially given both methods use the EXACT same code.

Any ideas?

 

[RiverGuy]

Do a Google search for "Parameter Sniffing." If I were a betting man, I'd wager this is your issue.

 

[RiddleMeThis]

It certainly was, thanks very much riverguy!

I renamed the parameter, declared a variable with the old name of the parameter, set the variable to = the parameter, and hey presto!

 

[jbenson001]

you can just use WITH RECOMPILE in your proc as well