I have an application that does a few queries and one insert (via a stored
proc) within a loop that right now should run almost 400 times. When run
against the development server, it runs fine. When run against the
production mirror, it hangs.
I have 3 prepared selects:
$sth1 = $dbh1->prepare( <<EOF
select column1, column2
from dbo.table1
where x = ?
EOF);
$sth2 = $dbh2->prepare( <<EOF
select columnA, columnB
from dbo.table2
where y = ?
and z = ?
EOF);
$sth3 = $dbh1->prepare( <<EOF
declare \@date datetime
select \@date = max(date)
from dbo.table3
where a = ?
and b = ?
EOF);
These 3 queries are used to map an input record to a database table's
columns. So, a file is opened, a while loop used to read each line, the
line is parsed, and the data mapped to the db columns. When the mapping
is complete, I have a stored proc that is prepared each time through the
loop (had to do that since DBD::Sybase won't allow placeholders here).
while (<IN>)
{
.....
$sth1->execute(x) or die(...);
$href = $sth1->fetchrow_hashref();
... do something with data in $href
$sth1->finish();
$sth2->execute(y, z) or die(...);
$href = $sth2->fetchrow_hashref();
... do something with data in $href
$sth2->finish();
$sth3->execute(a, b) or die(...);
$href = $sth3->fetchrow_hashref();
... do something with data in $href
$sth3->finish();
.....
my $sql = sprintf("execute sp_insert %s%s%s....", @outarr);
my $sth4 = $dbh3->prepare("$sql");
if ($DBI::err)
{
... error handling
$sth4->finish();
}
$sth4->finish();
$counter++;
if ($counter == 100)
{
$dbh3->commit();
$counter = 0;
}
}
$dbh3->commit(); ###Commit leftovers
The first 2 db handles point to different databases on the same server
without AutoCommit on (doesn't really matter for selects). The 3rd db
handle has AutoCommit off (so I can buffer transactions). It had to be
done this way because (DBD::)Sybase complained about multiple statement
handles on a single db handle with AutoCommit off.
I'm printing out $sql to a log with $| = 1; Looking at the way the log
file is filled, it looks like the first transaction is written about 30s
after program execution. Then I'll get 60-80 written to the log every so
often... nothing steady. Exactly 363 transactions should be
committed. However, only 329 transactions are written to the log and 300
are commited.
The problem appears to be with statement handle $sth3. Originally, I had
it so it looked like
select max(date) from dbo.table3
where a = ?
and b = ?
I have no idea why this is less efficient than doing declare select
select... I've never seen anything like that when working with
Informix. Anyways, if I remove this query, the program will run against
the production mirror quickly and it won't hang.
Statement handles $sth1-$sth3 only return one row... if more were returned
for some inexplicable reason, I would ignore them anyway. That's why I
finish after each fetch (otherwise (DBD::)Sybase complains and dies). The
sp executed with $sth4 does a lone insert... there's nothing to fetch.
I tried committing everytime, to see if one transaction in particular
caused an error. It committed one transaction and then:
DBD::Sybase::db commit failed: OpenClient message: LAYER = (1) ORIGIN =
(1) SEVERITY = (1) NUMBER = (49)
Message String: ct_send(): user api layer: external error: This routine
cannot be called because another command structure has results pending.
I don't understand this. Every statement handle is finished before
continuing to the next. All return 0 or 1 row.
The only difference between development and production mirror dbs is the
amount of data in the tables. For example, the table queried in $sth3 has
about 43000 rows in development while production has 4x10^6 rows. I can
understand the query taking longer, but not freezing altogether. I've run
this against Informix using Perl 5.6 on AIX 4.3.3 (substituting a
regular prepare with placeholders for the stored proc) and it runs fine
even when the tables have massive amounts of data.
My environment is:
OS: SunOS 5.6 (Solaris 2.5.1)
Perl 5.00404
DBI 1.13
DBD::Sybase 0.21
Sybase 11.9.2
Thanks
--Curt
