byterock

In part 16 we has a quick look at the NAME and other attributes that can help you create portable code. Well this time lets break with good portable code like I did in part 15 and bend the spec a bit. We will have a look at Asynchronous Queries

Wrapped Around Your Fingers
So we are not talking about the Album but then again I think the album should of been called Asynchronicity, as it really was overplayed not very well executed and over-hyped, anyway lets get on track.

Asynchronous in DBI means we are sending a query off to the DB and instead of waiting for a response, we go off and do our own thing and DBI and hopefully later we can come back and ask if our results are ready.

So far the only one I have played with is DBD::PG and I used it to broadcast the same long running query across a large number of client DBs and then store up the returned results. So to start we have to import the async constants like this

use DBD::Pg ':async';

We then simply add in the appropriate value as an attribute during the prepare like this

my $sth = $dbh->prepare($sql, {pg_async => PG_ASYNC});

We can then check the statement handle with the 'pg_ready' method which returns 'true' if the query is finished, some thing like this

while (!$sth->pg_ready) {
...

There is also the very useful 'pg_cancel' which kills the query if it is really taking too long and the not so useful 'pg_result' which kinda turns your asycn query back into a sycn query.

On with my little example. Like I said before I had a single query that was to be run on large number of DBs, most of the queries took anywhere from 3 to 15 mins and few well over, The original script simply iterated over the list of DBs and ran the query, waited for the results and then processed the returned data. It took about 6 hours to run.

So what I did was iterate over the DBs, connected to each, prepared the statement with pg_async and pushed the resultant $sth into a hashref, with the key as the DB name.

Next I simply looped through the values in the hash executed them in turn and I did something like this

while (keys(%{$my_sths})){

foreach my $db (keys(%{$my_sths}){
my $sth = $my_sths->{$db}
if ($sth->pg_ready) {
do_process($sth);
delete($my_sths->{$db});
}
...

Once we got it all working It only took about 2 hours to run.

You will note that is is how DBD::PG does things as this is not part of the DBI spec this will not work on 99% of DBDs and each DBD does this in it own little way.