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.

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.