TIMEOUT Driving me nuts !

[Chris Wertman]

I have an ASP.NET page that does a query to my Sql2000 Server Running
on Windows 2003 Server Enterprise. I have 3 nearly Identiacl pages
that all execute different Queries on the DB it is a fairly complex
set of queries , Now a week ago all 3 worked fine.

BUT this week only 1 the simplelest works.

On the others I get a System.Data.OleDb.OleDbException: Timeout
Expired

I have Connection timeout set to 0 in the connection string, and like
I said it all worked a week ago, there is no difference in the data it
worked with a week ago and now,Ive tried it with an absurdly long
timeout and with no timeout set at all. Iv rebooted checked the
machine for process that may be consuming resources that werent. and
on and on

I dont even really care WHY its happening I ONLY care about HOW TO
RESOLVE it.

Unfortunatley I looked through usenet archive and while i have seen
people with the same problem I see no response on workarounds.

Sql and Windows 2003 are all patched up to current.

Previously I was getting timeouts on the ASP page itself as as part of
the process it writes about 50,000 pages out based on the data, then I
increased the timeout in the machine.config and that solved that, as
it should but NOW Im getting SQL timeouts on everything but the
simplest query.

Someone PLEASE help me on this otherwise Im going to have to scrap all
this and rewite it in PHP using MySQL and I dont really feel like
doing that. I just spent a fair bit of time converting it and
upgrading (if thats what it can be called) to .Net

The batch import of the data into MySQL was painfull thats why I went
to MSSQL and .Net to say the least and what really get me is IT
FRIGGIN WORKED FINE LAST WEEK ! None of that matters much I guess if
it wont run right under .Net and SQL

HELP HELP HELP !

Chris

 

[William \(Bill\) Vaughn]

The ConnectionString timeout applies to the connection, not the Command. Is
the failure occurring on Open (or Fill)?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________

 

[MikeP]

Sounds like your table sizes have been increasing and you haven't properly
optimized your queries. I would recommend you try some of these queries in
Query Analyzer and see how long they take. You could also have locking
problems if your transactions are not handled correctly.

Other place to look (after optimization):
1) Connection timeout only handles the connection times not query times.
Take a look at CommandTimeout in the Command object.
2) Global settings on your SQL Server (not ado.net).

Mike P

 

[Chris Wertman]

No the tables are the same size 47,000 records same as they were last
week, I checked before I submitted this the first time. I have a view
that I am selecting all on and even that dosent work, while it works
very snappily in the query analyzer.


Chris

 

[Angel Saenz-Badillos[MS]]

Chris,
Are you setting the Connection Timeout connection string keyword or the
CommandTimeout SqlCommand property? The first only affects connection open
and this could very well be the reason you are seeing timeouts.

As far as Query Analyzer working fast: Every time I have seen this there has
been an issue with the TSQL Properties set, can you try setting arithabort
on and off on both QA and SqlClient? You can also check to see if there are
any other differences in the properties these two are using by checking DBCC
USEROPTIONS and SELECT @@OPTIONS. See the sp_configure 'user options'
topic in BOL for the description of which bits in the @@options bitmask
correspond to each set option.

 

[Chris Wertman]

I am using it in the connection string ,

But I tried adding it (I am using myCommand.CommandTimeout = 60
with OleDbCommand) and it still throws the timeout expired

 

[Chris Wertman]

Right now I dont know.

I nuked everything out of the script except a while loop thats supposed
to iterate out just the book title (these are books I am dealing with)
and even that gives me a connection timeout.

Like I said there is one script that still works, but it is way slow
compared to what it was last week, it only returns about 50 results (as
opposed to 50k for the other scripts) and it does work though.

It shouldnt be on fill I wouldnt think as I am iterating through the
datareader like so, but hey I could be missing this completly.

While objDataReader.Read()
Response.Write(objDataReader("title") & "<br><br>")
End While

I am doing that because I need to write a page for each record, I also
need in other items to nest results. Worked GREAT last week.

It sits forever before I get the OleDbException : Timeout Expired.

As suggested by another I tried pulling the connection timeout form the
connection string and adding a objCommand.CommandTimeout=60 (an tried up
to like 500 or so) and still the same result.

 

[MikeP]

Have you tried using the Sql objects instead of the OleDb objects?

 

[Angel Saenz-Badillos[MS]]

I missed that, you are using Oledb so Connection Timeout in the connection
string is just ignored.

Setting the CommandTimeout to 60 should mean that you do not get a timeout
from ado.net before 60 seconds, is this the case? ie. Are you getting a
timeout _before_ 60 seconds?
If so I would start looking at other reasons for the timeout, are you doing
anything on your TSQL query that may be throwing an error? did you try
changing the arithabort property

 

[William \(Bill\) Vaughn]

Did the timeout message say anything about the connection pool?
Did you check sp_lock to see if there were lock contentions that prevented
the queries from executing?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________

 

[s b]

hello did anyone manage to solve this issue, ive got the exact same and
its a realy pain.

thanks in advance.

Steve