Why is calling a stored procedure slower than running the code within the stored procedure?
I have a stored procedure that takes over a minute to run. If I take the code within the stored procedure and just run that directly, it takes about 20 seconds. I can't think of anything that would cause that...
If I look at the execution plans they are different, but getting the execution plan on the query itself increases the time to be on par with the stored procedure call.
I tried creating a new sproc with that query, but it was just as slow as the old one...
I'm totally stealing this from Grant Fritchey, but at least I'm giving him proper credit:
Parameter sniffing is usually the cause of something like this. When you run the query as just a query, all the parameters are local, so SQL Server can look at them, sniff them, and determine an execution plan based on the values. As soon as you put parameters in a stored procedure, SQL Server assumes an unknown value in the parameter, correctly, and creates a different execution plan. In most cases, this works well. In some cases it doesn't.