ADO components CommandTimeout

Question

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 ?

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.

The last, what I've tried is this

// 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
...

Thanks a lot :)

Randall

Answer 1

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.

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.

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.

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.

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

Kevon

You're right, it's 15 seconds, but I have set the ConnectionTimeout of the assigned TADOConnection to 3 seconds and still I get the control back after 15 seconds. – Randall

@daemon_x: Updated answer with some more findings. I can recreate what you are experiencing. – Kevon

It works fine, I've made a mistake in something else (not ADO relevant) and after correction I really got the timeout after TADODataSet.CommandTimeout + TADODataSet.Connection.ConnectionTimeout. Accepted as a solution also because of CursorLocation remark :) Thanks for your time – Randall

Answer 2

Following is what we use to set the timeout to 300 for our long running reports.

  //***** Fix setting CommandTimeOut. 
  //      CommandTimeOut "should" get the timeout value from its connection. 
  //      This is not supported in ADODB (using Delphi5)
  TADODataSet(qryReport).CommandTimeout := ADOConnection.CommandTimeout;

Edit

Executing following piece of code on my Development Machine times out after 1 second.

• The query has a connectionstring to our SQLServer production database.
• The script (tries to) runs for 10 seconds
• After one second, I get a TimeOut exception

Test

procedure TForm1.btn1Click(Sender: TObject);
const
  SSQL: string =
    'DECLARE    @intLoop int '#13#10
    + 'SET @intLoop = 10 '#13#10
    + 'WHILE @intLoop > 1 '#13#10
    + 'BEGIN '#13#10
    + ' SELECT  @intLoop, GetDate() '#13#10
    + ' WAITFOR DELAY ''00:00:01'' '#13#10
    + ' SELECT  @intLoop = @intLoop -1 '#13#10
    + 'END ';
begin
  qry1.SQL.Text := SSQL;
  TADODataSet(qry1).CommandTimeout := 1;
  qry1.ExecSQL;
end;

Culberson

+1 thanks for the tip, but it doesn't work. Anyway, I need the reverse situation - get the response as fast as possible, but even if I set the CommandTimeout to 5, according to your suggestion, I'm still waiting 30 seconds. – Randall

@daemon_x, it works for us setting it to 300 but we haven't tried it setting it < 30. If I find some time, I'll check it and report my findings. – Culberson

@daemon_x: I have updated the answer with a script that works for me. If this does not work for you, there's something else wrong. If it does, you have a starting point to find the differences with your actual code. – Culberson

@Lieven - yes, this maybe works for ExecSQL, but I'm trying to open a dataset using Open method. Namely I want to execute stored procedure, which returns a resultset. Currently I came to the TADODataSet object (see my updated question). – Randall

This is a mere example. For our reporting we use Open io ExecSQL and have the command timeouts honored. Does this example give you a time out? Dit you trie it? When you alter it to use Open io ExecSQL (and change the sql statement into something that takes time), does it work? – Culberson

Yes, I've tried it and - yes. Also the example what I've mentioned in the question works, I've made a mistake in something else (not in ADO stuff). When I corrected my code, I really got the timeout after TADODataSet.CommandTimeout + TADODataSet.Connection.ConnectionTimeout. Sorry for my mistake and wasting your time :) – Randall

Answer 3

I've always used the following code to set the CommandTimeout value on a TADOQuery. If you adjust the class name it should also work with the others as well.

type 
TADOQueryHack = class(TADOQuery);

...

TADOQueryHack(Qry).CommandTimeout := COMM_TIMEOUT;

Cryosurgery