radiantmatrix has asked for the wisdom of the Perl Monks concerning the following question:

I have a Tk application that is used to monitor various conditions in a third-party application. The operation is extremely simple: I query a SQL database using DBI (there are several different queries that check for different conditions), then populate a Tk::Columns widget with the results. Each Columns widget is on it’s own page of a Tk::Notebook widget.

This has been working wonderfully for several weeks, but a recent request to add a new monitor has highlighted an issue: when there is a query that takes a while, the whole application is unresponsive until the query completes. When we’re talking about a few seconds, it’s no big deal: set $mw->Busy until I finish, to let people know I’m working. When the query takes on the order of minutes, it’s a problem.

I’ve already put my SQL through the wringer, asking our DBA team to tweak it — it’s not going to get any more efficient. The tables are already indexed correctly (again, according to the DBA team). The query at hand just takes a while, because it needs to be somewhat complex and it’s looking through lots of data. The issue, as expressed in psuedo-ish code:

# statement is prepared in advance, and values are bound right after t
+hat

$mw->Busy;
$tk_col->delete(0, 'end');
$sth->execute(); # this blocks for several minutes;
$mw->Unbusy;
while (my $row = $sth->fetchrow_arrayref) {
    $tk_col->insert('end', $row);
}

[download]

Once I get past the $sth->execute(), the app responds acceptably: I can view other notebook tabs, etc. while the widget is populated.

I’ve poked about on Google and SuperSearch for a couple of days, and the only solution I’ve been able to come up with to keep the execute() from blocking is to spawn a thread that handles it, and not update the widget until the thread returns. This approach seems to require quite a bit of shared memory magic, since the thread will have to get me an AoA with the results (or be aware of the Columns widget in order to populate it, depending). Besides that, I understand that threads on Win32, which is about 80% of the deployment, are tricky at best and unsupported at worst.

I can count on Perl >=5.8.6 being available, and just about any CPAN module (they have to be availalbe from ASPN on the Win32 machines, so there are a few limitations. It’s policy, there’s nothing I can do, even though I know I could build just about anything that’s in CPAN but not ASPN.) The Win32 machines do use the ActiveState build(s) of Perl, and the other machines use the “normal” Perl distribution.

Anybody have ideas about how to approach solving this?

Update:

Thanks to a reminder from the AnonyMonk, I explored POE. The easy solution to this would be POE::Component::EasyDBI. Unforutnately, it's not in ASPN. Forutnately, making a problem-specific solution using the standard POE (which is available from ASPN) was not that hard. Basically: 
POE::Session->Create (
   inline_states => {
      _start => \&execute_query(); # calls $kernel->yield('result') wh
+en done
      result => \&populate_result(); # fills Tk Widget
      _stop  => sub { $MW->update() };
   },
   args => [ $sth ],
);
POE::Kernel->run();

[download]

Updates:

<-radiant.matrix->
A collection of thoughts and links from the minds of geeks
The Code that can be seen is not the true Code
"In any sufficiently large group of people, most are idiots" - Kaa's Law

Replies are listed 'Best First'.
Re: Long-running DB query makes Tk application unresponsive
by scmason (Monk) on Oct 13, 2005 at 19:24 UTC
    Hi,

    Two things:

    I have had reasonable success using the Perl's threading api on windows. In fact, using the new threads, everything work exactly as I expected.

    A second approach to threading would be spawning a new process to check. You can use the Tk::Send to communicate between the two. Just make you new process a Main Window that is not visible. It will then be able to use Send, and your Main interafce would use Recieve.

    Good luck. Post back what you decided to do and why!.

    "Never take yourself too seriously, because everyone knows that fat birds dont fly" -FLC
[reply]

      Hm, I researched using Tk::Send, but I can't find it as any component in the Tk heirarchy, or indeed anywhere on CPAN or in my local installation. Maybe I'm looking in the wrong place? If I can get it from the ASPN repository, then I can use it. Unfortunately, not unless.

      As to threads, the idea of sharing that much data (and that complex a structure) gives me shudders, because I don't know what I'm doing shared-memory wise. From my research, I've developed an instinctive "share as little data as you can" response. If a few monks here tell me that isn't a valid response, and explain a little bit why, I'll probably go ahead and work on threads.

      I still wonder, though, if there's something I can do to tell DBI or the DB or something to return immediately and notify me when it's finished (or something).

      <-radiant.matrix->
      A collection of thoughts and links from the minds of geeks
      The Code that can be seen is not the true Code
      "In any sufficiently large group of people, most are idiots" - Kaa's Law
[reply]
        Update: I was too fast with the response, and missed that nearly everythig I was suggesting you were already doing - sorry, nothing to see

        ...reality must take precedence over public relations, for nature cannot be fooled. - R P Feynmann

[reply]
Re: Long-running DB query makes Tk application unresponsive
by renodino (Curate) on Oct 13, 2005 at 22:28 UTC
    Which DBI driver are you using ? (e.g., DBD::ODBC, DBD::mysql, DBD::Pg, etc.)

    Which version of Perl/TK are you using ?

    Alas, most DBD's have no async i/f, so such things are difficult to handle, esp. given both DBI's and Perl/Tk's less than robust threading implementations.

    (Hopefully I'll one day get Thread::Apartment and Perl/TK running together - once I figure out how to handle closures - , and many of these issues will hopefully go away, esp. in combo w/ DBIx::Threaded. The latter may help provide a solution, esp. the async methods (see $sth->start_execute()), tho I haven't yet tried to get pTk and DBIx::Threaded to play nice together - which I'll be needing to do soon as well).

    In the near term, you might try running Perl/Tk in its own thread, open a Thread::Queue, and use Storable to transer result data (not the handle, the actual data) to pTk. Then set an after()/repeat() in pTk to poll the queue.

[reply]

      I'm not bound to one DBD. The app currently talks to one of two databases, depending on user preference. One is SyBase, and I use DBD::ODBC for that connection. The other is Oracle and I use DBD::Oracle for that.

      In the near future, Oracle will be the only choice, so I'd have no issue planning around DBD::Oracle.

      DBIx::Threaded is a wonderful module! I got it working on my *NIX machines, and I was thrilled -- until I realized that ASPN hasn't managed to compile it into a ppd, yet. Unfortunately, unless the module is available on ASPN, the stupid policy prevents me from using it on Win32.

      I'm figuring out how to implement a Thread::Queue solution as you suggested. I'm somewhat new to threads, so I'm taking my time on this one. Thanks for the suggestions! I'll update this if I end up succeeding on this approach. Any sample code you can provide that might help me get up and running more rapidly would be appreciated, as well.

      <-radiant.matrix->
      A collection of thoughts and links from the minds of geeks
      The Code that can be seen is not the true Code
      "In any sufficiently large group of people, most are idiots" - Kaa's Law
[reply]
Re: Long-running DB query makes Tk application unresponsive
by badaiaqrandista (Pilgrim) on Oct 13, 2005 at 23:59 UTC

    Can you change the query? I had performance problem in my web app recently and to solve it I changed the algorithm.

    For example, you can use LIMIT in your SQL, and you don't want to show 1000 records in one screen anyway. So you only query for the first 50 results and when the user scroll down to the 51st result, you query for the next 50 results.

    Or you probably want to process some of the data offline, so that your query can run much faster.

    cheers,
    badaiaqrandista
[reply]
Re: Long-running DB query makes Tk application unresponsive
by zentara (Cardinal) on Oct 14, 2005 at 10:47 UTC
    keep the execute() from blocking is to spawn a thread that handles it, and not update the widget until the thread returns. This approach seems to require quite a bit of shared memory magic, since the thread will have to get me an AoA with the results (or be aware of the Columns widget in order to populate it, depending)

    Just brainstorming..... but you should be able to work out a way of getting the data back from the thread, without it totally finishing. Maybe the way the SQL works, it has to be that way, but the thread could set a shared flag when it knows one portion of the data is retreived, and you could setup a timer in the main Tk thread to repeatedly check the flag, and do what is neccesary to read the completed section. Maybe if you thought about it, you could figure out a method for the thread to detect when a column is ready to be populated, then have it set the shared flag, and put the column number( or whatever) in another shared variable. Then as the Tk loop goes through it's wait-timer, it will process that column as it detects it is ready. That way, you will see the columns fill as they are individually finished. Just setup a shared array @columns_finished. Have the thread push finished columns into @columns_finished, and have a Tk timer, watch for and shift off of @columns_finished and send it to the Columns widget.

    P.S. If this script is more than a "1-shot deal", you should make your thread reusable, instead of launching multiple async threads.

    I'm not really a human, but I play one on earth. flash japh
[reply]

      Hm, my communication skills are letting me down again. The retrieval of the rows is not slow. The end result set is only about 10-15 rows on average, but the gymnastics to get them in SQL is terribly complex. I had our DBA guys optimize the SQL, but even so it takes several minutes just to *start* returning rows, even in SQL*PLUS, for example.

      As to making a reusable thread, I've lately been toying with the idea of having a long-running process act as some kind of IPC server, and having the GUI just be a client of that. The client might look something like: 

      $res = $srvr->ask($SQL,@params);
if ($res == SRVR_ACK && SRVR_WAIT) {
   # set up something to check back for results later
}
else {
   # deal with the results
}

      [download]

      I think that's kinda-sorta what you're talking about, but I just can't seem to wrap my head around this, so I might be wrong. Thoughts?

      <-radiant.matrix->
      A collection of thoughts and links from the minds of geeks
      The Code that can be seen is not the true Code
      "In any sufficiently large group of people, most are idiots" - Kaa's Law
[reply]
[d/l]
        As far as making a reusable thread, see ztk-BBC-World-News-Rss-TickerTape and look at the sub work. It is a sleeping thread, which gets turned on by the shared variable $shash{'go'} and is killed by $shash{'die'}. So you can create the thread, (or a couple of them), wake them with a go=1, and let them do whatever you want. When done, set go=0. The sleep 1 gives a very efficient sleep, but you can remove the 1 second lagtime, by using a smaller sleep time with something like "select,undef,undef,undef,100" for a 100 microsecond delay.
        I'm not really a human, but I play one on earth. flash japh
[reply]
Re: Long-running DB query makes Tk application unresponsive
by Anonymous Monk on Oct 14, 2005 at 03:27 UTC

    Maybe you should look at POE for this.

[reply]

      Hm, POE does look like an excellent solution. I'm definately putting that on the shelf for the next iteration of this app. Unfortunately, it looks like the amount of work to learn POE and re-think my design in a POE-ish way is a little steep for a short-term solution.

      Nifty framework, though! I've heard mention of it before and never gave it more than a cursory glance, so I'm glad you reminded me of it.

      <-radiant.matrix->
      A collection of thoughts and links from the minds of geeks
      The Code that can be seen is not the true Code
      "In any sufficiently large group of people, most are idiots" - Kaa's Law
[reply]

        The problem is how to break up your long running query into small enough chunks that your gui remains responsive. Once you've done that, you could just use a Tk-repeat().

        Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
        Lingua non convalesco, consenesco et abolesco. -- Rule 1 has a caveat! -- Who broke the cabal?
        "Science is about questioning the status quo. Questioning authority".
        The "good enough" maybe good enough for the now, and perfection maybe unobtainable, but that should not preclude us from striving for perfection, when time, circumstance or desire allow.
[reply]
[d/l]

Back to Seekers of Perl Wisdom