执行查询时出现超时错误

发布于 2024-12-31 22:27:53 字数 342 浏览 1 评论 0原文

我正在存储过程中执行以下查询，将一年的事务从一个表传输到另一个表：

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

当我使用 VB6 调用存储过程时，它超时。

我尝试将 RDO 连接上的 QueryTimeout 属性设置为五秒，如下所示：

RdoConn.QueryTimeout = 5000

但它仍然超时。

如何防止查询执行超时？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

只涨不跌 2025-01-07 22:27:53

如果您使用 Ado 连接，则可以使用以下代码

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

，然后使用此连接来执行查询。

您的查询需要多长时间才能正常运行？

更新：
使用 RdoConnection，您可以尝试设置 LoginTimeout 事件

RdoConn.QueryTimeout = 5000
RdoConn.LoginTimeout = 5000

或如所指出的奥列格·多克

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

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

回复收藏 0 原文

つ低調成傷 2025-01-07 22:27:53

您的两个选项（不更改数据库模式）是：

1）增加超时时间，例如：

RdoConn.QueryTimeout = 30000

2）如果您的查询执行时间超过 30 秒，那么您应该尝试将 select 语句分解为更小的部分：

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

如上所述您还应该查看数据库中的索引和数据类型以提高性能

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

回复收藏 0 原文

~没有更多了~