Calling a Stored Proc from Excel or .NET Returing Very Slow

[Eric Renken]

We are having a very strange problem. When I execute a specific stored
procedure from SQL Management Studio I get all 7700 rows in seconds, but
when I call the same procedure with the same parameter settings from Excel
or from a .NET 3.5 application it takes about 3.5 minutes to return. This
happens consistently and running "sp_updatestats" doesn't help.

What does fix this is ALTERing the stored procedure. Like adding/removing
SET NOCOUNT ON and then pressing F5 to run the alter statement. Once you do
that Excel and the .NET application all start returning/displaying data in
seconds. It is like after time something in cache for that stored procedure
gets corrupt and no longer returns data quickly and when you ALTER the
procedure the cache gets cleared and it starts working for a while. Then
after x amount of time it starts to fail again.

Anyone have any thoughts on this?

Eric R.

[SetonSoftware]

Eric

Try running the stored proc with the WITH RECOMPILE options as
suggested here:

http://articles.techrepublic.com.com/5100-10878_11-5662581.html

Does that make a difference?

Carl

[Eric Renken]

Thanks. I am going to try that. My SP isn't as complicated as the one he
is talking about but you never know.

Thanks again. I hope this fixes it.

Eric

"SetonSoftware" wrote in message
news:2957cf34-1a05-4312...@l6g2000yqb.googlegroups.com...

[Eric Renken]

Thanks for the information. This may do it. Mine is a pretty straight
forward procedure, but it is having this problem.

I hope this works.

Eric

"SetonSoftware" wrote in message
news:2957cf34-1a05-4312...@l6g2000yqb.googlegroups.com...

On Aug 25, 12:26 pm, "Eric Renken" <i...@yaseb.com> wrote:

[Erland Sommarskog]

Eric Renken (in...@yaseb.com) writes:
> We are having a very strange problem. When I execute a specific stored
> procedure from SQL Management Studio I get all 7700 rows in seconds, but
> when I call the same procedure with the same parameter settings from Excel
> or from a .NET 3.5 application it takes about 3.5 minutes to return. This
> happens consistently and running "sp_updatestats" doesn't help.
>
> What does fix this is ALTERing the stored procedure. Like adding/removing
> SET NOCOUNT ON and then pressing F5 to run the alter statement.

Or just say "sp_recompile yourproc".

This issue is most likely related to parameter sniffing: when SQL Server
compiles a procedure it looks at the input parameters and use their
values when estimating which is the best plan. This means that if these
parameters are atypical, for instance because the first time you make
a big initial load, and the rest of the time you only get increments,
you will get a less optimal plan for the latter case.

The reason you see different performance in the application and SSMS
is that SSMS by default runs with the setting SET ARITHABORT ON, whereas
a plain application doesn't. The net effect of this setting is about
zero, but nevertheless it's a "cache key". That is, a process with
ARITHABORT ON cannot use a plan compiled for ARITHABORT OFF and vice
versa.

Next time it runs slow in the application, do this in SSMS:

SET ARITHABORT OFF

and then run the procedure. It should be slow now. Inspect the query plan.
And not only the graphical plan, but open the XML version of the plan,
and look at the bottom of the plan. Here you find the parameter values
that were sniffed for the plan - maybe that can give a clut.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Eric Renken]

Erland,

Thanks for the detailed information. I will check it out.

Eric

"Erland Sommarskog" wrote in message
news:Xns9DE02725...@127.0.0.1...