ADO组件CommandTimeout [英] ADO components CommandTimeout

问题描述

使用TADOQuery，TADOCommand或TADODataSet（我已经尝试过每一个），查询执行超时的设置有问题。我有一个微小的应用程序，它连接到数据库，定期执行存储过程，返回数据集作为结果。
我的目标是保持此应用程序始终在线，但我的问题是，当连接丢失时，刚刚执行的命令（通过上述组件之一）的超时时间将默认为30秒。我一直在寻找解决方案，但没有任何效果。
你能给我一个建议，如何设置CommandTimeout，例如5秒或更好地说如何修改ADODB.pas以尊重我自己的超时？

I have a problem with settings of the query execution timeout with TADOQuery, TADOCommand or TADODataSet (I've tried it with each one). I have a tiny application, which connects to the database and periodically executes stored procedures, which returns dataset as a result. My aim is to keep this application always online, but my problem is that when the connection is lost, the timeout of just executed command (through the one of the mentioned components) takes the default 30 seconds. I've been looking for the solution, but nothing works. Could you give me an advice, how to set the CommandTimeout e.g. to 5 seconds or better to say how to modify ADODB.pas for respecting my own timeout, please ?

有很多解决方案，就像设置DataComponent.Connection.CommandTimeout：= 1;但真的没有什么工作。我正在使用D2009，MSSQL2005，连接数据组件是在线程中动态创建的。

There were many "solutions" for this, like set DataComponent.Connection.CommandTimeout := 1; but really, nothing works. I'm using D2009, MSSQL2005 and the connection along with the data component is created dynamically in the thread.

最后，我试过的是这个

// protected variable owned and created in the thread with its own connection
var Query_Object: TADODataSet; 

// connection timeout is set to 3 seconds
Query_Object.Connection.ConnectionTimeout := 3;
...

// this piece of code I'm calling periodically in the only one existing thread
...
SQL_Query := 'EXEC my_procedure_which_returns_dataset'

with Query_Object do
  begin
    Close;    
    CommandType := cmdText;
    CommandText := SQL_Query;
    CommandTimeout := 5;             // doesn't affect the timeout
    CursorLocation := clUseServer;   // let the dataset retreives prepared data
    Open;
  end;

// and here I need to get faster than in the default 15 seconds to let the user
// know that the reading takes more than mentioned 5 seconds
...

非常感谢：）

推荐答案

CommandTimeout 在您长时间运行查询时正在踢入。 $ code> CommandTimeout 属性 TADOConnection 但这不起作用。您必须使用 TADODataSet 的 CommandTimeout 。

CommandTimeout is kicking in when you have long running queries. There is a CommandTimeout property of TADOConnection but that does not work. You have to use the CommandTimeout of the TADODataSet instead.

如果服务器不可用，您的问题说连接丢失，则需要指定 TADOConnection ConnectionTimeout >组件。默认值为15秒，然后控制权返回给您的应用程序。

If the server is unavailable, your question says "connection is lost", you need to specify ConnectionTimeout of the TADOConnection component. Default is 15 seconds before control is returned to your application.

编辑1 我认为我发现了CommandTimeout不起作用的情况。我已经测试了一个很大的桌子。返回所有行需要几分钟的时间。如果我的存储过程从BigTable 中选择*，则查询超时永远不会发生。至少我没有足够的耐心等待。但是如果查询看起来像这样从Col1 的BigTable顺序中选择*，而在 Col1 上没有索引，则CommandTimout按预期工作。

Edit 1 I think I have discovered a situation where CommandTimeout does not work. I have tested this against a really big table. It takes several minutes to return all rows. If my stored procedure does select * from BigTable the query timeout never happens. At least I was not patient enough to wait it out. But if the query looks like this select * from BigTable order by Col1 and there is no index on Col1, the CommandTimout works as expected.

在SSMS中运行这两个查询之间的区别很明显。第一个开始立即返回行，第二个需要在返回行之前思考。当SQL Server找到需要的行并开始返回它们时，CommandTimeout不起作用。

The difference between the two queries is obvious when running them in SSMS. The first starts to immediately return rows and the second needs to "think" about it before it returns rows. When SQL Server have found the rows it needs and start to return them, CommandTimeout does not work.

如果设置 CursorLocation 到 clUseServer CommandTimeout 将按照预期的方式对两个查询工作。

If you set CursorLocation to clUseServer the CommandTimeout will work as expected for both queries.

这篇关于ADO组件CommandTimeout的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！