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...

Answers


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.


Need Your Help

Bitmap.compress results in too large of file

java android

I have an app that needs to resize an image and then save it to jpg. My test image is a photo with very smooth gradients in the sky. I'm trying to save it to jpeg after a resize using this code:

Localization - First Steps

asp.net localization

I'm pretty much after people opinions/best practices and nuggets of experience here.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.