Abort SQL Query with process time limit

Question

Can I write SQL queries using DELPHI, dbgo DB components and a SQL Server database server which are limited in respect to the process time ?

Like

select * from table where ......  

and process_time_limit = 5 sec ?

Better you give me 10% of the rows within a time limit instead of waiting hours for the complete query dataset

Answer 1

ADO components:

I would give a try to the asynchronous data fetch. When you'd execute the query, you'll remember when you've started and every time the OnFetchProgress event fires, you'll check if the EventStatus is still in esOK state and check the time elapsed from the query execution. If it elapsed, you might cancel data fetch by using the Cancel method on your dataset.

I meant to use something like the following (untested) pseudocode:

var
  FQueryStart: DWORD;

procedure TForm1.FormCreate(Sender: TObject);
begin
  // configure the asynchronous data fetch for dataset
  ADOQuery1.ExecuteOptions := [eoAsyncExecute, eoAsyncFetchNonBlocking];
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  // store the query execution starting time and execute a query
  FQueryStart := GetTickCount;
  ADOQuery1.SQL.Text := 'SELECT * FROM Table';
  ADOQuery1.Open;
end;

procedure TForm1.ADOQuery1FetchProgress(DataSet: TCustomADODataSet; Progress,
  MaxProgress: Integer; var EventStatus: TEventStatus);
begin
  // if the fetch progress is in esOK (adStatusOK) status and the time since
  // the query has been executed (5000 ms) elapsed, cancel the query
  if (EventStatus = esOK) and (GetTickCount - FQueryStart >= 5000) then
    DataSet.Cancel;
end;