Webmaster Forum / ASP / Database Access / August 2008

I would still rather we continue this conversation in the original thread.
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.

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