How to terminate a working thread
hannes.re...@gmx.at
maybe this has been already discussed, but I haven't found a solution
for my problem.
I'm using a thread for executing a SQL-Select to have the possibility
to break up the execution of the Select pushing a 'break'-Button.
This all works except that the thread is not being killed immediatly
calling TerminateVOThread or TerminateThread. It waits for the
SQLFetch-Function (which is the only thing done by the thread-
function) to be finished.
This would not be a problem if my App would continue working, but the
GUI freezes until the SQLFetch-function in the thread finishes. I also
tried without calling TerminateThread, leave allocated memory thats
used in the thread for the GC and just get on working but without
success.
Does anyone have an idea how to terminate a thread immediatly even if
the called in it is currently working?
TIA Hannes
Ginny Caughey
Is the SQLFetch function executing on the GUI thread? If not, I don't see
why your GUI is freezing. In general if a GUI thread is freezing, you need
something like ApplicationExec(EXECWHILEEVE#NTS) or whatever it is for the
VO GUI so Windows messages can be processed while the GUI thread is busy
looping doing something that takes a while.
The main problem is that the SQLFetch is taking too long. It's not usually a
great idea to kill a thread but instead let the thread know when it should
commit suicide. You need to break up whatever is getting fetched in SQLFetch
into smaller units if possible so the worker thread can frequently check if
it should commit suicide (return).
As a side note, make sure you've investigated every aspect of why the select
statement is taking so long in the first place. For example, would
additional indexes on the tables help? Are they getting rebuilt on a
schedule such as every night? I've found this can make an very big
difference for predicate expressions - the ones in the WHERE clauses.
--
Ginny Caughey
www.wasteworks.com
<hannes.re...@gmx.at> wrote in message
news:34ca30a9-dfd9-4f49...@35g2000pry.googlegroups.com...
hans083
<ginny.caughey.onl...@wasteworks.com> wrote:
> Hannes,
>
> Is the SQLFetch function executing on the GUI thread? If not, I don't see
> why your GUI is freezing. In general if a GUI thread is freezing, you need
> something like ApplicationExec(EXECWHILEEVE#NTS) or whatever it is for the
> VO GUI so Windows messages can be processed while the GUI thread is busy
> looping doing something that takes a while.
The SQLFetch-call is one of three lines in the function, which
represents my thread. The other things are setting a flag for the main
thread to know that the Select has finished and the last one is
ExitVOThread. I already tried ApplicationExec with both possible
parameters, but without success.
However, it is strange.
my thread func:
STATIC FUNCTION oos_odbcselect_threadFunc(pTData AS
oos_odbcselect_threadData)
pTData.siRetCode := SQLFetch(pTData.hSelect)
pTData.lFinish := TRUE
ExitVOThread(0)
RETURN 0
>
> The main problem is that the SQLFetch is taking too long. It's not usually a
> great idea to kill a thread but instead let the thread know when it should
> commit suicide. You need to break up whatever is getting fetched in SQLFetch
> into smaller units if possible so the worker thread can frequently check if
> it should commit suicide (return).
I wouldn't have a problem to leave the SQLFetch running in a thread,
my only problem is that it freezes my GUI.
>
> As a side note, make sure you've investigated every aspect of why the select
> statement is taking so long in the first place. For example, would
> additional indexes on the tables help? Are they getting rebuilt on a
> schedule such as every night? I've found this can make an very big
> difference for predicate expressions - the ones in the WHERE clauses.
Unfortunately it's not that easy. I've created a reporting tool where
my users can dynamically generate reports over their data. Most of
them don't even know how to spell SQL. If they generate a report and
use a lot of tables (what I can't avoid) the select (which I generate
in the back) will not only take seconds, it will takes minutes. In
order to have the possibility to cancel a running select I tried to
use threads.
Ginny Caughey
that somebody pressed Cancel in the GUI thread - I don't see any way the
fetch thread could respond to a Cancel request even if it did know about it
from your code since it is blocking on the SQLFetch function.
Is the GUI thread sitting in a loop like WHILE ! pTData.lFinish after you
start up the worker thread? If so that loop is where you put the
ApplicationExec call. In any case, ExitVOThread is unnecessary, since the
RETURN should end the thread for you.
I think in this scenario where you want to allow the users to execute ad hoc
queries but have no control over what the users are likely to ask SQL to do,
it might make more sense to give them a user friendly front end and just let
it hang. We solve this problem with our customers in two ways - we recommend
either Crystal Reports or SQL Server Reporting Services for ad hoc reports,
and we will also write reports using these tools for them if they wish. That
way our customers who do know SQL have full power, and those who don't still
get well performing reports. The other advantage of using a 3rd party
reporting tool like this is we don't get blamed for our users' bad queries.
;-)
--
Ginny Caughey
www.wasteworks.com
"hans083" <hannes.re...@gmx.at> wrote in message
news:2e60b234-2ee5-4121...@o4g2000pra.googlegroups.com...
hans083
<ginny.caughey.onl...@wasteworks.com> wrote:
> How are you calling your thread function? And how does the fetch thread know
> that somebody pressed Cancel in the GUI thread - I don't see any way the
> fetch thread could respond to a Cancel request even if it did know about it
> from your code since it is blocking on the SQLFetch function.
@oos_odbcselect_threadFunc(), pTData, 0, @nID)
I tried every idea that that came up to me. First I showed a window
with a break-Button called by the main thread. Then I tried to show
the break-Window out of another thread, that's where I am now. The
window shows up, I can fully control it while the other thread is
fetching data. Pressing break freezes the GUI until the Select
finishes.
>
> Is the GUI thread sitting in a loop like WHILE ! pTData.lFinish after you
> start up the worker thread? If so that loop is where you put the
> ApplicationExec call. In any case, ExitVOThread is unnecessary, since the
> RETURN should end the thread for you.
Yes, that's it exactly. Although, ApplicationExec doesn't what it's
supposed to.
>
> I think in this scenario where you want to allow the users to execute ad hoc
> queries but have no control over what the users are likely to ask SQL to do,
> it might make more sense to give them a user friendly front end and just let
> it hang. We solve this problem with our customers in two ways - we recommend
> either Crystal Reports or SQL Server Reporting Services for ad hoc reports,
> and we will also write reports using these tools for them if they wish. That
> way our customers who do know SQL have full power, and those who don't still
> get well performing reports. The other advantage of using a 3rd party
> reporting tool like this is we don't get blamed for our users' bad queries.
> ;-)
Unfortunately I have no other choice than keep providing this
generation tool. Their recommendation for self-configured reports goes
very deep. Crystal or SQL Server Reporting Services would be too much
complicated for them. As everybody who is working with computers but
has no experience in programming or even Database querys, they want to
do everything and that easily and it must be fast. The one who is
first blamed for their bad queries is me, until I describe them THEIR
errors. Sure, I trie to avoid every potential error in my generator,
but this is impossible as you can easily imagine.
Ginny, thanks for your help. If I can find a satisfying solution
somewhen somehow I'll post it here.
>
> --
>
> Ginny Caugheywww.wasteworks.com
>
Ginny Caughey
thread is a bad idea - I'd just get rid of that one. It would make more
sense to just start up a separate app instead - have you considered that
approach? Then your main app won't hang.
Anyway, your users may not know SQL, but what they're doing is such a bad,
bad idea. Unless they are actually running reports off a copy database
instead of the production one, their bad queries could bring performance
down for the whole company! Companies that are large enough to have DBAs in
charge of the SQL databases typically won't even allow users to pary on
production databases at all for this reason.
--
Ginny Caughey
www.wasteworks.com
"hans083" <hannes.re...@gmx.at> wrote in message
news:a66106e6-3039-4ac1...@a29g2000pra.googlegroups.com...
Malcolm
> I wouldn't have a problem to leave the SQLFetch running in a thread,
> my only problem is that it freezes my GUI.
How are you using the thread (I would not expect it to freeze the GUI
unless you chose to freeze the GUI by what you do with the GUI thread)
hans083
Malcolm,
that's how I'm working:
pThread := CreateVOThread(NULL_PTR, 0, @oos_odbcselect_threadFunc(),
pTData, 0, @nID)
STATIC FUNCTION oos_odbcselect_threadFunc(pTData AS
oos_odbcselect_threadData)
pTData.siRetCode := SQLFetch(pTData.hSelect)
pTData.lFinish := TRUE
//ExitVOThread(0)
RETURN 0
I also never expected it to freeze my GUI, but it does and I have no
idea why, it's not logical.
Geoff Schaller
You are going about this the wrong way.
Firstly, you won't be able to do what you want to do because the
priority in the other threads is such that you don't get processing time
to contact the lax thread to cancel it. Secondly, any strategy along
these lines is hopeless without at least a genuine dual core CPU and
probably unlikely unless you have about 4 cores. Do your customers have
such CPUs?
The answer is a lot simpler: use asynchronous selects.
Not only can you then have processing time in other threads, you can
cancel the select or offer a progress bar. Robert has a sample for this
in his VO2Ado install.
Geoff
"hannes.re...@gmx.at" <hannes.re...@gmx.at> wrote in
message
news:34ca30a9-dfd9-4f49...@35g2000pry.googlegroups.com:
__________ Information from ESET NOD32 Antivirus, version of virus
signature database 3739 (20090105) __________
The message was checked by ESET NOD32 Antivirus.
hans083
wrote:
> Hannes,
>
> You are going about this the wrong way.
>
> Firstly, you won't be able to do what you want to do because the
> priority in the other threads is such that you don't get processing time
> to contact the lax thread to cancel it. Secondly, any strategy along
> these lines is hopeless without at least a genuine dual core CPU and
> probably unlikely unless you have about 4 cores. Do your customers have
> such CPUs?
>
> The answer is a lot simpler: use asynchronous selects.
>
> Not only can you then have processing time in other threads, you can
> cancel the select or offer a progress bar. Robert has a sample for this
> in his VO2Ado install.
>
> Geoff
>
> messagenews:34ca30a9-dfd9-4f49...@35g2000pry.googlegroups.com:
>
>
>
> > Hi all,
>
> > maybe this has been already discussed, but I haven't found a solution
> > for my problem.
> > I'm using a thread for executing a SQL-Select to have the possibility
> > to break up the execution of the Select pushing a 'break'-Button.
> > This all works except that the thread is not being killed immediatly
> > calling TerminateVOThread or TerminateThread. It waits for the
> > SQLFetch-Function (which is the only thing done by the thread-
> > function) to be finished.
> > This would not be a problem if my App would continue working, but the
> > GUI freezes until the SQLFetch-function in the thread finishes. I also
> > tried without calling TerminateThread, leave allocated memory thats
> > used in the thread for the GC and just get on working but without
> > success.
>
> > Does anyone have an idea how to terminate a thread immediatly even if
> > the called in it is currently working?
>
> > TIA Hannes
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 3739 (20090105) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
Geoff,
I don't have the time to try that immediatly, but I'll let you know if
it works. Thanks!