----- Original Message -----
From: "Alex Pilosov" <[EMAIL PROTECTED]>
To: "Michael Wray" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, November 14, 2001 10:51 AM
Subject: Re: Perl Pg Vs. DBD::Pg
> Using DBI, you must be careful how you fetch the data.
>
> There are methods for fetching entire result set as an array of arrays or
> array of hashes, but doing row-by-row fetching as a reference of an array
> is fastest. That was most likely the reason for disparity.
And more....
If you use Apache, mod_perl and Apache::DBI make
your DBI script faster.
#because of less connections
If you use almost same statements except for values,
you may use "place holder" like below:
[Sample]
use strict;
use Benchmark;
use Pg;
use DBI;
my $iLoop = 5000;
my $sDt = '2001-12-18';
my $sSel = 'SELECT TITLE, CREDT FROM ALBUM ORDER BY TITLE';
my @aPg;
{
$aPg[0] = new Benchmark;
my $oConn = Pg::connectdb('host=lins dbname=test user=scott
password=tiger');
die $oConn->errorMessage if($oConn->status != PGRES_CONNECTION_OK);
$oConn->exec('begin');
$oConn->exec('DELETE FROM ALBUM');
$oConn->exec('end');
$aPg[1] = new Benchmark;
$oConn->exec('begin');
for(my $i=0;$i<$iLoop;$i++) {
$oConn->exec("INSERT INTO
ALBUM(TITLE,CREDT)VALUES('TTL-$i','$sDt')");
}
$oConn->exec('end');
$aPg[2] = new Benchmark;
my $result = $oConn->exec($sSel);
my $sLine;
while (my @row = $result->fetchrow) { $sLine = join(':', @row); }
$aPg[3] = new Benchmark;
}
my @aDb;
{
$aDb[0] = new Benchmark;
my $hDb = DBI->connect('dbi:Pg:host=lins;dbname=test',
'scott', 'tiger', {RaiseError => 1, AutoCommit=>0});
$hDb->commit;
$hDb->do('DELETE FROM ALBUM');
$aDb[1] = new Benchmark;
for(my $i=0;$i<$iLoop;$i++) {
$hDb->do("INSERT INTO ALBUM(TITLE,CREDT)VALUES('TTL-$i','$sDt')");
}
$hDb->commit;
$aDb[2] = new Benchmark;
my $hSt = $hDb->prepare( $sSel );
$hSt->execute();
my $sLine;
while(my $raData=$hSt->fetchrow_arrayref()){ $sLine = join(':',
@$raData);}
$hSt->finish;
$hDb->disconnect;
$aDb[3] = new Benchmark;
}
my @aDbP;
$aDbP[0] = new Benchmark;
{
my $hDb = DBI->connect('dbi:Pg:host=lins;dbname=test',
'scott', 'tiger', {RaiseError => 1, AutoCommit=>0});
$hDb->commit;
$hDb->do('DELETE FROM ALBUM');
$aDbP[1] = new Benchmark;
my $hStI = $hDb->prepare(q/INSERT INTO ALBUM(TITLE,CREDT)VALUES(?, ?)/);
for(my $i=0;$i<$iLoop;$i++) {$hStI->execute("TTL-$i", $sDt); }
$hDb->commit;
$aDbP[2] = new Benchmark;
my $hSt = $hDb->prepare($sSel);
$hSt->execute();
my $sLine;
while(my $raData=$hSt->fetchrow_arrayref()){$sLine = join(':',
@$raData); }
$hSt->finish;
$hDb->disconnect;
$aDbP[3] = new Benchmark;
}
print "Pg\n",
"1:",timestr(timediff($aPg[1], $aPg[0])),"\n",
"2:",timestr(timediff($aPg[2], $aPg[0])),"\n",
"3:",timestr(timediff($aPg[3], $aPg[0])),"\n",
"DBI-1\n",
"1:",timestr(timediff($aDb[1], $aDb[0])),"\n",
"2:",timestr(timediff($aDb[2], $aDb[0])),"\n",
"3:",timestr(timediff($aDb[3], $aDb[0])),"\n",
"DBI-2\n",
"1:",timestr(timediff($aDbP[1], $aDbP[0])),"\n",
"2:",timestr(timediff($aDbP[2], $aDbP[0])),"\n",
"3:",timestr(timediff($aDbP[3], $aDbP[0])),"\n";
[Result]
Pg
1: 0 wallclock secs ( 0.00 usr + 0.01 sys = 0.01 CPU)
2: 8 wallclock secs ( 1.60 usr + 0.79 sys = 2.39 CPU)
3: 9 wallclock secs ( 1.69 usr + 0.81 sys = 2.50 CPU)
DBI-1
1: 0 wallclock secs ( 0.07 usr + 0.01 sys = 0.08 CPU)
2:14 wallclock secs ( 4.93 usr + 0.97 sys = 5.90 CPU)
3:14 wallclock secs ( 5.02 usr + 1.00 sys = 6.02 CPU)
DBI-2
1: 0 wallclock secs ( 0.00 usr + 0.00 sys = 0.00 CPU)
2:10 wallclock secs ( 1.83 usr + 0.82 sys = 2.65 CPU)
3:10 wallclock secs ( 1.93 usr + 0.82 sys = 2.75 CPU)
==============================================
Kawai, Takanori(Hippo2000)
Mail: [EMAIL PROTECTED] [EMAIL PROTECTED]
http://member.nifty.ne.jp/hippo2000
==============================================
