"Script timed out" error
Justin Doh
I am still not able to fix my issue.
"Script timed out"
I think if I query data at SQL2005 directly, it takes about 2 min 38 sec.
and I am able to get data.
So, I am trying to fix at ASP coding.
First, I set up
<% server.ScriptTimeout = 10000 %>, but still getting error.
Second,
I have this set up this during the retrieval of recordset using a stored
procedure.
.ConnectionTimeout = 4800
.CommandTimeout = 4800
Thirdly,I had placed "Response.Flush" at the end of looping of data.
But I am still getting error.
If it runs fine at the SQL server directly, what can I do at ASP page?
I would appreciate for any help.
Thanks.
Bob Barrows [MVP]
> I am trying to retrieve a data over web using SQL2005 and ASP page.
>
How about responding in the original thread with the results of the profiler
trace I asked you to run?
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Justin Doh
Sorry that I did not indicate the results of finding.
Actually I was not sure how I should use the Profiler.
It appears that it was hitting the stored procedure, and then it gives this
stats.
CPU: 145569
Reads 76727637
Writes 125593
Duration 214490
On the web, it stills give the error still.
"Active Server Pages error 'ASP 0113'
Script timed out
The maximum amount of time for a script to execute was exceeded. You can
change this limit by specifying a new value for the property
Server.ScriptTimeout or by changing the value in the IIS administration
tools."
Bob Barrows [MVP]
Having multiple threads on the same topic can get very confusing, especially
if someone else decides to join the conversation. Please reply in the first
thread you started.
Is there any chance of optimizing the stored procedure so it doesn't take so
long to run? It's doing an insane amount of reads and writes.
ASP is not really suited to running processes that take this long to run.
Mark McGinty
"Justin Doh" <Just...@discussions.microsoft.com> wrote in message
news:A9032936-519C-469A...@microsoft.com...
> Hi Bob,
>
> Sorry that I did not indicate the results of finding.
> Actually I was not sure how I should use the Profiler.
> It appears that it was hitting the stored procedure, and then it gives
> this
> stats.
>
> CPU: 145569
> Reads 76727637
> Writes 125593
> Duration 214490
So it took the proc 214 seconds to execute... I don't know about you, but 3
1/2 minutes is way longer than I like to wait for a web page. If one or
more of the server's CPU cores is pegged the entire time, this stored proc
is going to scale like hell. If a good part of that time is spent waiting
on a lock, it likewise will tend to scale negatively, but at least the
server won't be on its knees everytime a dozen clients hit this stored proc
at once.
In any case there is no mystery here, default timeout for ASP script is 90
seconds, you could change that by setting Server.ScriptTimeout to a large
enough value, but that will not *solve* your *problem*, it will merely
circumvent the error condition that [rather appropriately] occurs under
default settings.
Your *problem* is that your stored procedure takes far too long to execute,
either your indexes are inadequate, your data structure is poorly designed,
you're incurring an extreme amount of i/o overhead, you're returning way too
many rows to the recordset and/or browser, or some combination of those.
The operative point here is that if a stored proc doesn't return in a
sub-second under light load, there needs to be a truly compelling reason for
that. You need to take a hard look at the query plan[s] your stored proc
invokes, figure out where all that overhead is coming from, and do anything
you can to make it much, much, much more efficient.
You may need to rethink your design, there's no way I can say, sight unseen,
but I can virtually guarantee you there are any number of ways it could be
improved.
What you should specifically NOT do, is crank the ScriptTimeout up to a
couple hours, and color the problem solved; here's why: human nature
dictates that web surfers become impatient inside of 30-40 seconds, it is
rare for a user to wait much more than a minute for a page to load. Beyond
that the typical user will close the browser, open a new one and try again
to load the same page.
Problem is, the ADO object, running synchronously in your ASP script,
doesn't know the browser it thought it would return data to, is gone, and
SQL Server processes your ungodly stored proc to the end -- for nothing. At
the same time the new browser window invokes another instance of the same
long process. Each new instance incurs more load, making the stored proc
take even longer to run! A single user could invoke several zombie
processes like that; they all run until either they complete, or one of them
errors.
Result: an inefficient/badly-designed stored procedure problem becomes a
sluggish and crash-happy server problem.
Good Luck.
-Mark