Stored Procedure fast in SSMS but times out in ADO.NET app

[michael]

I have stored procedure which runs in a few seconds from within Microsoft SQL
Server Management Studio. The stored procedure has a bunch of joins, all to
indexed keys.

However, if I try to use the same stored procedure from within a VS2008 VB
app, the procedure either crawls or times out. I've tried extending the time
out period, but too often the app just hangs.

I'm certain that this stored procedure runs correctly (and fast in SSMS). Is
it possible that the stored procedure is executed differently on SSMS than it
is from my app? Any other possible causes?

 

[sloan]

Google
"Parameter Sniffing"

 

[Cor Ligthert[MVP]]

Michael,

This needs more information, it sounds now to me like: "As I go from Times
Square to the public library by metro it goes quite fast, but as I take a
plane it takes days".

Cor

 

[Frans Bouma [C# MVP]]

I have stored procedure which runs in a few seconds from within Microsoft SQL
Server Management Studio. The stored procedure has a bunch of joins, all to
indexed keys.

However, if I try to use the same stored procedure from within a VS2008 VB
app, the procedure either crawls or times out. I've tried extending the time
out period, but too often the app just hangs.

I'm certain that this stored procedure runs correctly (and fast in SSMS). Is
it possible that the stored procedure is executed differently on SSMS than it
is from my app? Any other possible causes?

without your code which executes the proc, it's impossible to check.

Also, does the proc return values? If the proc does return a lot of
data, and for example image/text/varchar(max) kind of columns, the total
amount of data to consume on the client is HUGE, which will take a lot
of time. SSMS cuts off these resultsets in a more efficient matter.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[michael]

Who knew!!

You got this one correct.

Fixed the problem with local variables assigned to the parameters passed to
the StoredProcedure.

Thanks!!

 

[sloan]

Yeah.

My fixes have included...sometimes using the "local variable" trick.
And sometimes using the OPTION RECOMPILE.

I had to do some testing to figure out which one worked best for a
particular usp (user stored procedure).

........

And I'm thankful to the first person who told me about it.


Ask for a raise now! You "da man" with this nugget.