MSSQL and Perl - heavy load

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

I am using the following function to retrieve the last date from an MSSQL table:

sub getlast{
        my $panel = shift;
        my @lastsale;
        my $dbh;
        my $sth;

        $dbh = DBI->connect( "dbi:Sybase:server=$ini{SQL01DB}{server};
                database=$ini{SQL01DB}{database}", $ini{SQL01DB}{usern
+ame},
                $ini{SQL01DB}{password} );
        $sth = $dbh->prepare( "select convert(varchar,max(dtmTimeEnter
+),120),
                convert(varchar,max(dtmDateTime),120) from $table
                where intSiteID=$site and intPanelID=$panel
                and intAdditiveCode>0" );
        $sth->execute();

        @lastsale = $sth->fetchrow;

        $sth->finish;
        $dbh->disconnect;

        if ( $lastsale[0] || $lastsale[1] ){
                return ( @lastsale );
                }
        else{
                return;
                }
        }
[download]

This code generally works perfectly for my purposes. But, when the MSSQL server is under heavy load it seems to be incorrectly returning nothing for both elements in the lastsale array in some random instances. At first, I thought perhaps the server was refusing the connection (due to too many), but I don't believe this to be the case. If the connection is refused, then the prepare should cause an error that exits the script (right?). There are some instances that there will legitimately be nothing returned, and I need these. If anyone has a similar experience, or any ideas to help me troubleshoot this I would greatly appreciate it!

Comment on MSSQL and Perl - heavy load Download Code

Replies are listed 'Best First'.
Re: MSSQL and Perl - heavy load by bv (Friar) on Jan 11, 2010 at 17:37 UTC
If the connection is refused, then the prepare should cause an error that exits the script (right?). Only if you told it to! There are 2.5 ways of doing this. First way (least pretty): `$sth = $dbh->prepare( "select blah from table" ) or die "Prepare failed: ", $dbh->errstr;` [download] Second way: `$dbh->{RaiseError} = 1; $sth = $dbh->prepare( "select blah from table" )` [download] Both previous ways work, but kill your script and don't offer a clean way to close the transaction (if you are using transactions). Try this, way 2.5: `$dbh->{RaiseError} = 1; eval { my $sth = $dbh->prepare("select blah from table"); $sth->execute(); }; if ($@) { warn "Transaction aborted: $@"; eval { $dbh->rollback }; die "Unable to rollback: $@" if $@; }` [download] `print map{substr'hark, suPerJacent other l',$_,1}(11,7,6,16,5,1,15,18..23,8..10,24,17,0,12,13,3,14,2,4);`	[reply] [d/l] [select]
Re: MSSQL and Perl - heavy load by almut (Canon) on Jan 11, 2010 at 17:46 UTC
In addition to what bv explained, the `$sth->fetchrow` may also fail. From the docs (emphasis added): If there are no more rows or if an error occurs, then fetchrow_array returns an empty list. You should check $sth->err afterwards (or use the RaiseError attribute) to discover if the empty list returned was due to an error.	[reply] [d/l]
Re^2: MSSQL and Perl - heavy load by taomanjay (Novice) on Jan 11, 2010 at 18:46 UTC
This sounds like something that might work for me. Does that return an int? So, could I do this: `@lastsale = $sth->fetchrow; if ( $sth->err ){ exit 1; }` [download] Not that I would do it exactly like that, but should that work?	[reply] [d/l]
Re^3: MSSQL and Perl - heavy load by almut (Canon) on Jan 11, 2010 at 21:47 UTC
Yes, as I read the docs, that should work fine (though it might be more informative to also print the `$sth->errstr` instead of simply doing `exit 1`). As you're simply testing for truth, it does not matter whether `->err` returns an integer or not (which doesn't seem to be guaranteed). In general, when unconditionally reading/testing error variables or methods, it's good to check the docs, because in some cases, their value is invalid unless an error did in fact occur — think of `$!`. In this particular case though, this is not a problem, as the docs explicitly state "the DBI resets $h->err to undef before almost all DBI method calls" (the "almost all" refers to methods irrelevant here).	[reply] [d/l] [select]
Re^4: MSSQL and Perl - heavy load by taomanjay (Novice) on Jan 11, 2010 at 22:52 UTC
Re: MSSQL and Perl - heavy load by NiJo (Friar) on Jan 11, 2010 at 17:34 UTC
I assume that the connect and disconnect to/from the DB have been copied in only for clarity. If you do this in every function, your heavy DB load is expected.	[reply]
Re^2: MSSQL and Perl - heavy load by taomanjay (Novice) on Jan 11, 2010 at 18:02 UTC
Oh, that is exactly how the function works. I know it is not ideal, but it is also not the cause of the heavy load. This function basically gets called a total of 60 times. This is a temporary script and the next iteration of the script will be nicer with the DB connections. I know why we have the load, it's due to other things which I am aware of and have no control over.	[reply]