inetserver asp general: Timeouts -- inetserver asp general

You're in the

inetserver asp general

group:

Timeouts

Jim in Arizona
12/29/2004 11:23:12 AM

inetserver asp general:

I'm doing a query on a webproxy log, which is a pretty large table. I'm only
doing a query on a single days worth, but could be upwards of 250000
records. Originally I got a timeout error saying to increase the time using
Server.ScriptTimeout so I put Server.ScriptTimeout = "900" at the top of my
script. This didn't seem to help. Now I got this error:

a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver]Timeout expired
/suntest.asp, line 39

This time it didn't mention what type of timeout I'm experiencing, at least,
not that I can see. I've seen the ASPFAQ on timeouts
(http://www.aspfaq.com/show.asp?id=2066) but that didn't help me even when I
tried pretty much all of them (on another test a few months back). I'm using
IIS 5. The database I'm using is SQL server 2K. Here's my code.

<%@ Language=VBScript %>

<%
Server.ScriptTimeout = "900"
Dim Conn, sql, daterange, RS

daterange = Request.Form("daterange")

sql = "SELECT * FROM [webproxylog]"
sql = sql & " WHERE (logdate=convert(datetime," & "'" & daterange & "'" &
",102))"

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={SQL
Server};Server=sunspark;Database=ISALogs;Uid=sa;Pwd=slinky;"

Set RS1 = Conn.Execute(sql)

%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Testing</title>
</head>

<body>

<%

If RS1.EOF and RS1.BOF Then
Response.Write("<nobr>There Are No Records</nobr>")
Else
Response.Write("<nobr><h3><u>WebProxyLog Query</u></h3></nobr>")
Response.Write("<table border=""1"" align=""center"" bordercolor=""maroon""
cellspacing=""0"" cellpadding=""7"" width=""95%"">")
Response.Write("<tr><td width=""5%""><h3>Test Column</h3></td></tr>")

RS1.MoveFirst
While Not RS1.EOF
Response.Write("<tr><td> ")
Response.Write(RS1.Fields("ClientUserName") & "</td></tr>")
RS1.MoveNext
Wend
Response.Write("</table>")
End If

%>

</body>
</html>

Thanks.
Jim

Re: Timeouts

Jim in Arizona
12/29/2004 1:46:07 PM

Whoa Aaron. That's a lot to soak in. I will hit each article and do my best
to comprehend it all. As I progress and hit walls, you all will be hearing
form me!

Thanks for your help.

Jim

[quoted text, click to view]

Re: Timeouts

Dave Anderson
12/29/2004 2:01:47 PM

[quoted text, click to view]

In addition to Aaron's suggestions, take a look at this:

http://msdn.microsoft.com/library/en-us/ado270/htm/mdproconnectiontimeout.asp

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

Re: Timeouts

Aaron [SQL Server MVP]
12/29/2004 2:19:45 PM

- stop using SELECT *
see http://www.aspfaq.com/2096

- use CreateObject instead of Server.CreateObject
see http://www.aspfaq.com/2336

- use the OLEDB provider instead of {SQL Server}
see http://www.aspfaq.com/2126

- make sure there is an index on logdate

- use if RS1.EOF then ... no need to check RS1.BOF

- drop the RS1.MoveFirst call... where else do you expect to be?

- use do while / loop, not while / wend

- create a stored procedure!
see http://www.aspfaq.com/2201

I think the index will be the big one here. I suggest changing daterange to
be in the format YYYYMMDD, so you can drop the convert in the where clause.
If not, and you're sure the result will never be ambiguous (see
http://www.aspfaq.com/2023), then use convert(SMALLDATETIME instead. If the
logdate column only stores dates at midnight with no time component, then
the column should be SMALLDATETIME, not DATETIME. If there is a time
component, then you are going to need to change the where clause slightly
(see http://www.aspfaq.com/2280).

You might want to visit the timeouts article again, if the last time you
tried any of them was "months ago." You might also see this article for
general efficiency suggestions: http://www.aspfaq.com/2424

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

[quoted text, click to view]

Re: Timeouts

io
12/31/2004 4:48:22 PM

Hi Jim,

In your case it is a ADO query timeout that expires. The default value for
it is 30 secs. If you only want to increase that timeout you can use the
following:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={SQL
Server};Server=sunspark;Database=ISALogs;Uid=sa;Pwd=slinky;"
Conn.CommandTimeout = <your value in seconds>

If you don't want a query to timeout at all set it to zero.

Cheers

[quoted text, click to view]

Re: Timeouts

Jim in Arizona
1/3/2005 12:36:25 PM

Perfect! Thanks!

Jim

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the inetserver asp general group.

View Other Months
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007

home · blog · groups

Questions? Comments? Contact the dn