Data Access and Transactions

Avoiding Query Time-outs

It’s not uncommon to find database tables of 10 million to 50 million records. On these systems, even stored procedures can sometimes take longer than a few minutes to run. A user running a report knows that the query may take several minutes and this is acceptable. However, a Web page doesn’t normally wait that long before it will time out.

To solve this problem, you first need to set the CommandTimeout property of the Connection object. This property indicates how long to wait for a query to execute and applies to the Connection.Execute and Recordset.Open methods when the Source property is not a Command object. If the Recordset.Open call is using a genuine Command object, you’ll need to set the CommandTimeout property of the Command object instead. This property establishes the length of time the application should wait for a Command object to execute.

You will also need to increase the values of the ScriptTimeout property of the ASP Server object. If this property isn’t set, it doesn’t matter how long the ADO query takes; the script will have stopped executing in the meantime. Both the CommandTimeout and ScriptTimeout properties accept values indicating the number of seconds to wait before canceling the operation.