Re: Cancelling an ADO Command takes too long


first question, are you sure Oracle accepts such a command? Just
asking, it's been years since I last worked with that db... :)

I'm not sure of anything -- that's part of my problem ;-)

I would expect that Oracle, given that it is a major player, would support a
cancel command but I wouldn't be surprised if it doesn't. I know Oracle is
capable of cancelling active queries via administrative commands since
that's how most of our clients cancel runaway queries now. However, I would
like to offer a solution that doesn't require the use of a DBA. DBA's tend
to like our software more when they don't have to get involved in the
day-to-day operation.

Said this, I'm not sure if regarding updates and depending on if you do
batch updates or not, it would work as you want. Maybe CancelBatch
would be better for that. See also CancelUpdate.

The queries in question are mostly SELECT statements. When the query takes
too long (or appears to lock) it is usually because the person writing the
query didn't join two tables correctly. The server is busy spending its
time joining millions of records together for the correct result set when
the author merely wanted a few dozen. They will notice their mistake almost
immediately but by then it's too late.

Check these URLs. You will find some examples

Thanks for the reply and the links. I will see if anything there can help
me.

Your original question references AdoCommand which does not return a recordset
Here you say most of your queries are selects.
If you are using TadoDataset or TadoQuery to return the select then you would
use Adodataset1.recordset.cancel to cancel Asyncronous execution.

and you would do that out of the AdoDataset OnFetchProgress event

procedure TForm1.ADODataSet1FetchProgress(DataSet: TCustomADODataSet;
Progress, MaxProgress: Integer; var EventStatus: TEventStatus);
begin
if CancelQuery then DataSet.Recordset.Cancel;
{CancelQuery is a global boolean variable set to True by a Cancel button}
{Note that DataSet.Cancel doesn't seem to work, hence
DataSet.Recordset.Cancel}
Application.ProcessMessages;
end;

I have no experience with canceling queries that are not Selects
--
Brian Bushay (TeamB)
Bbushay@xxxxxxxxx
.

Quantcast