Stored procedure takes longer time to execute

Hi,

I have a stored procedure which is used in a ASP page. The execution of the
procedure from query analyser takes approximately 5 minutes. But, when it is
executed thr an ASP page, it takes very long to respond and i get a "Page
cannot be displayed" error. I am not able to figure out where the problem
is, IIS, ASP or SQL Server.

Thanks in advance

Saurabh

Re: Stored procedure takes longer time to execute

Saurabh wrote:
> Hi,
>
> I have a stored procedure which is used in a ASP page. The execution
> of the procedure from query analyser takes approximately 5 minutes.
> But, when it is executed thr an ASP page, it takes very long to
> respond and i get a "Page cannot be displayed" error. I am not able
> to figure out where the problem is, IIS, ASP or SQL Server.
>

You are likely to be falling victim to a behavior known as "parameter
sniffing" in SQL Server. See if this helps:
http://tinyurl.com/f9r2

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Stored procedure takes longer time to execute

If the stored procedure already takes 5 minutes, you need to work on getting
that time down before trying to execute it from an ASP page. Most browser
will quit trying after 5 minutes. Can you show the stored procedure code
and help explain why it should take 5 minutes from any tool?

--
http://www.aspfaq.com/
(Reverse address to reply.)




"Saurabh" wrote in message
news:E3CD0AE4-C701-4472-B9FB-C4531C000B20@microsoft.com...
> Hi,
>
> I have a stored procedure which is used in a ASP page. The execution of
the
> procedure from query analyser takes approximately 5 minutes. But, when it
is
> executed thr an ASP page, it takes very long to respond and i get a "Page
> cannot be displayed" error. I am not able to figure out where the problem
> is, IIS, ASP or SQL Server.
>
> Thanks in advance
>
> Saurabh

Re: Stored procedure takes longer time to execute

Hi bob,

Thanks for the reply.. I tried your solution it has increased my SP
performance, But problem with the ASP page remains the same. Please guide....

thanks,
Saurabh Birla

"Bob Barrows [MVP]" wrote:

> Saurabh wrote:
> > Hi,
> >
> > I have a stored procedure which is used in a ASP page. The execution
> > of the procedure from query analyser takes approximately 5 minutes.
> > But, when it is executed thr an ASP page, it takes very long to
> > respond and i get a "Page cannot be displayed" error. I am not able
> > to figure out where the problem is, IIS, ASP or SQL Server.
> >
>
> You are likely to be falling victim to a behavior known as "parameter
> sniffing" in SQL Server. See if this helps:
> http://tinyurl.com/f9r2
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Stored procedure takes longer time to execute

You might consider looking at some of the suggestions here.
http://www.aspfaq.com/2424#db

Or, post the code that is taking so long to run, and we might be able to
give you specific suggestions.

Imagine calling a mechanic and saying, "Why is my car not running?"

"I don't know; I can't even see your car. Shall I start guessing at
potential reasons, or do you want to show me the car now?"

--
http://www.aspfaq.com/
(Reverse address to reply.)