At 08:52 AM 23/03/2006, Richard Wesley wrote:

>On Mar 22, 2006, at 13:13, Alan McDonald wrote:
>
> >> If I am executing a select query via isc_dsql_execute in a background
> >> thread under Windows, what is the best way to abort the execution if
> >> the user cancels the operation in a foreground thread? Can I issue a
> >> rollback on the transaction? Do I just orphan the execution thread
> >> until it completes? Or is there some other mechanism?
>
> > how are you fetching the rows? you can fetch in a loop and
> > interrupt the
> > loop, then rollback the transaction.
>
>I guess the question is "Where is all the time spent executing a slow
>query"? I had assumed that all Fetch did was read a data stream that
>had already been created. Is that not the case?

No, it's not the case. Fetch gets one row per call. Your program
has to provide a loop mechanism to fetch multiple rows. If you want
a user to be able to stop the loop then you will need to build in
some kind of callback mechanism between the user interface and your
fetch loop to stop the fetching and roll back the transaction.

You are getting confused between "executing" and "preparing". The
first time a SELECT query is run, you have to prepare it. How long
the prepare takes depends on various factors, like the complexity of
the query and the complexity of the metadata.

Then, once the query is run, the time it takes to send the first
packet of output depends on factors like the efficiency of the
statement, the state of the metadata and whether optimal indexes are used.

So prepare + an inefficient query could take a lot of time to deliver
the first row to your FETCH loop. Then it is a question of managing
your buffers so that you have somewhere to put the output and manage it.

Subsequent "runs" of the same query will be faster, as long as you
keep the query prepared.

When a query that ought to be fast seems slow, you should suspect
your indexes. You can run your query in isql with SET PLAN to get the
query plan back and see what indexes are being used. You can use
gstat to look at the stats for those indexes.

You can't "abort" something in one thread from another thread. To be
thread-safe, each thread must have its own distinct "session" with
the database, viz. connection, transactions and queries. You can't
"abort" a transaction at all: you can start it, and you can end it
by committing it or rolling it back. And you can neither commit nor
rollback the transaction while fetching is still going on. Period.

Once you start fetching from a statement, if you want a means to
abort the fetching (and thereby make it possible to rollback the
transaction) then you have to write that code.

You mentioned somewhere that you were "using embedded". You'll need
to be more explicit about what you mean. "Embedded" in Linux and
Windows are two entirely different animals.

./heLen