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

In reply to [solved] Long-running DB query makes Tk application unresponsive by radiantmatrix

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.