Recently, We had a database grow in tremendous amounts. The growth of the database began to cause timeouts on the web site.
This is the error that we continually received:
Microsoft OLE DB Provider for ODBC Drivers error '80040e31' [Microsoft][ODBC SQL Server Driver]Timeout expired page.asp, line 25
I searched google groups and found different ideas on fixing it. One solution was to increase the script timeout setting in IIS.
I found some reliable information on how to change that in IIS. However, the script would still time out at 30 seconds, even when I set that higher.
I kept searching, this time on the SQL side. In my search, I found a google groups answer that pointed me towards the SQL side. I increased the query timeout based on the instructions on the thread.
So now I had the IIS script timeout at 3 minutes and the SQL Query timeout at 3 minutes, but I'll be doggoned if that script didn't still time out in 30 seconds.
I kept thinking about what other components would be involved and finally it hit me. ADO!
I found out that there were two specific settings that would affect an ADO script timeout. Using the Connection Object for ADO, I saw Command Timeout and Connection Timeout. I manipulated my code to look like this:
dim myConn
Set myConn = Server.CreateObject("ADODB.Connection")
myConn.CommandTimeout = 0
myConn.Open "Provider=MSDASQL;Driver={SQL Server};Server=server;Database=database;UID=uid;PWD=pwd;"
The line myConn.CommandTimeout = 0 sets the CommandTimeout strictly for ADO. Setting it to 180 would be three minutes. Setting it to 0 tells it to work to infinity.
I then created my recordset object:
str_Select = "Select * from tblTableName"
SET adoRS = Server.CreateObject("ADODB.Recordset")
adoRS.Open str_Select, myConn, adOpenKeyset, adLockOptimistic
And this time, Yahoo! It ran perfectly, and quite quickly I might add. I hope this helps others in the same situation!
94% of the people that access this page leave in less than 30 seconds without visiting any other pages on the site.