Time out error while executing the query

Question

I am executing the following query in a stored procedure, transferring one year's transactions from one table to another:

insert into 
    table2 
select
    * 
from 
    table1 
where 
    dates between '20110101' and 20111231'

When I use VB6 to call the stored procedure, it times out.

I've tried to set the QueryTimeout property on my RDO Connection to five seconds like so:

RdoConn.QueryTimeout = 5000

But it's still timing out.

How can I prevent the execution of the query from timing out?

Answer 1

Your two options (without changing the database schema) are:

1) Increase your timeout time eg:

RdoConn.QueryTimeout = 30000

2) If your query is taking longer than 30 seconds to execute then you should probably try breaking down your select statement into smaller parts:

Insert into table2 select * from table1 where dates between '20110101' and 20110131'
Insert into table2 select * from table1 where dates between '20110201' and 20110228'

As mentioned though you should also look at indexes and data types in your database to improve performance

Answer 2

If you use an Ado connection you can use the following code

dim myConn 
Set myConn = CreateObject("ADODB.Connection") 
myConn.CommandTimeout = 3600
myConn.ConnectionTimeout = 3600

and then use this connection to execute your query.

How much time does your query take to run without issue?

Update:
Using RdoConnection, you can try to set event the LoginTimeout

RdoConn.QueryTimeout = 5000
RdoConn.LoginTimeout = 5000

or as pointed out by Oleg Dok

RdoConn.QueryTimeout = 0 'disable timeout
RdoConn.LoginTimeout = 0 'disable timeout