connection timeout

[mjweiner]

I have a web applicaiton that functions as a search engine that also
serves custom information to users based on their past searches and
clicks. I have several stored procedures that produce this custom
information that I have spent a lot of time optimizing.

When I run the sprocs through query analyzer, they run in 1-2 seconds.
However, on my application (which receives relatively little traffic -
10-20 visitors per hour), I receive several timeout errors daily on
the site. I have the timeout set at 30 seconds. When I run the exact
same query, using the exact same parameters through query analyzer, it
runs perfectly in 1-2 seconds.

I have reviewed my code and think that my connecitons are opened and
closed appropriately. Does anyone have any suggestions on evaluating
this problem further to determine the cause of the timeout? Any
thoughts on what may cause an sproc to run fine through query analyzer,
but slow to a grinding halt once it's deployed?

I'm running an ASP.NET 1.1 applicaiton, on a shared hosting platform
with a shared SQL server database.

thanks

Matt Weiner

 

[Guest]

It is probably not the Connection Timeout. The Command Timeout will apply to
the running of the stored procedure...

Are you using Query Analyser against the same server? If not, perhaps the
live server is missing indeces on key fields. This would certainly cause this
problem if the amount of data was significant.

 

[William \(Bill\) Vaughn]

There are a about four reasons that cover 99% of the connection timeout
issues--especially in ASP. Once you're sure you've checked that you've
closed all of your connections as you should, you next need to make sure
that your queries are efficient enough to get done in the time available. If
they take too much time or compete with each other (as they usually do),
they might not be done soon enough to reuse the connection, See my
whitepaper on
this...http://www.sqlmag.com/Article/ArticleID/38356/sql_server_38356.html


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________