You convinced me, but I was not completely wrong. Unify for example is doing what I said. That is most likely also why my mind thought it was for all databases. Also note that accessing a remote database (see Oracle) diminishes the difference:
All perl-5.18.2 (i686-linux-64int-ld)
psql (PostgreSQL) 9.3.4
DBI-1.631, DBD::Pg-3.1.1
prepare execute finish
------------- ------- ------- -------
no where 0.00011 0.02483 0.00009
where 0 = 1 0.00005 0.00058 0.00001
no where 0.00004 0.01491 0.00005
where 0 = 1 0.00005 0.00028 0.00000
no where 0.00004 0.01494 0.00005
where 0 = 1 0.00005 0.00030 0.00001
mysql Ver 15.1 Distrib 5.5.33-MariaDB
DBI-1.631, DBD::mysql-4.027
prepare execute finish
------------- ------- ------- -------
no where 0.00006 0.01328 0.00005
where 0 = 1 0.00005 0.00021 0.00000
no where 0.00003 0.01166 0.00004
where 0 = 1 0.00003 0.00014 0.00000
no where 0.00002 0.01050 0.00006
where 0 = 1 0.00004 0.00017 0.00001
Text::CSV_XS-1.07 (i586)
DBI-1.631, DBD::CSV-0.41
prepare execute finish
------------- ------- ------- -------
no where 0.00026 0.11570 0.00125
where 0 = 1 0.00067 0.08302 0.00000
no where 0.00037 0.12692 0.00126
where 0 = 1 0.00055 0.07317 0.00000
no where 0.00028 0.12742 0.00196
where 0 = 1 0.00080 0.08038 0.00000
Text::CSV_XS-1.07 (ia64)
DBI-1.631, DBD::CSV-0.41
prepare execute finish
------------- ------- ------- -------
no where 0.00111 1.01796 0.01366
where 0 = 1 0.00268 0.37951 0.00002
no where 0.00093 0.54718 0.01354
where 0 = 1 0.00231 0.77326 0.00003
no where 0.00105 0.64844 0.01313
where 0 = 1 0.00226 0.62751 0.00004
Unify DataServer 8.3
DBI-1.631, DBD::Unify-0.85
prepare execute finish
------------- ------- ------- -------
no where 0.00090 0.00041 0.00014
where 0 = 1 0.00156 0.03468 0.00047
no where 0.00069 0.00012 0.00002
where 0 = 1 0.00078 0.00695 0.00020
no where 0.00033 0.00006 0.00002
where 0 = 1 0.00066 0.00828 0.00019
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Prod
+uction (remote server)
DBI-1.631, DBD::Oracle-1.74
prepare execute finish
------------- ------- ------- -------
no where 0.02017 0.03333 0.00002
where 0 = 1 0.01903 0.01596 0.00001
no where 0.01679 0.02241 0.00001
where 0 = 1 0.01580 0.01449 0.00001
no where 0.01456 0.02465 0.00001
where 0 = 1 0.01581 0.01406 0.00001
firebird-2.5.2.26539
DBI-1.631, DBD::Firebird-1.17
prepare execute finish
------------- ------- ------- -------
no where 0.00384 0.00001 0.00442
where 0 = 1 0.00375 0.00001 0.00449
no where 0.00377 0.00001 0.00452
where 0 = 1 0.00382 0.00001 0.00436
no where 0.00378 0.00001 0.00448
where 0 = 1 0.00377 0.00001 0.00449
use 5.16.2;
use warnings;
use DBI;
use Data::Peek;
use Time::HiRes qw( gettimeofday tv_interval );
sub time_this
{
my ($dbh, $sql, $how) = @_;
my $t0 = [ gettimeofday ];
my $sth = $dbh->prepare ($sql);
printf "%s %7.5f", $how, tv_interval ($t0, [ gettimeofday ]);
$t0 = [ gettimeofday ];
$sth->execute;
printf " %7.5f", tv_interval ($t0, [ gettimeofday ]);
my @fld = @{$sth->{NAME}};
$t0 = [ gettimeofday ];
$sth->finish;
printf " %7.5f\n", tv_interval ($t0, [ gettimeofday ]);
} # time_this
for ( [ Pg => ""
+ ],
[ mysql => "database=merijn"
+ ],
[ CSV => "f_ext=.csv/r"
+ ],
[ Unify => ""
+ ],
[ Oracle => "", split m{/} => $ENV{ORACLE_USERID}
+ ],
[ Firebird => "db=$ENV{ISC_USER}", $ENV{ISC_USER}, $ENV{I
+SC_PASSWORD} ],
) {
my ($dbd, $dsn, $user, $pass) = @$_;
$dsn = join ":" => "dbi", $dbd, $dsn;
my $dbh = DBI->connect ($dsn, $user, $pass, {
RaiseError => 0,
PrintError => 1,
AutoCommit => 1,
ChopBlanks => 1,
ShowErrorStatement => 1,
FetchHashKeyName => "NAME_lc",
}) or do { warn DBI->errstr; next; };
say "DBI-", DBI->VERSION, ", DBD::$dbd-", "DBD::$dbd"->VERSION;
my $tbl = "test_$$";
my $sth = $dbh->do ("create table $tbl (k integer, v varchar (20))
+") or
do { warn $dbh->errstr; next; };
my $sti = $dbh->prepare ("insert into $tbl values (?, ?)");
$sti->execute ($_, "value$_") for 1 .. 10000;
my $sql1 = "select * from $tbl";
my $sql2 = "select * from $tbl where 0 = 1";
say " prepare execute finish";
say "------------- ------- ------- -------";
for (1 .. 3) {
time_this ($dbh, $sql1, " no where");
time_this ($dbh, $sql2, "where 0 = 1");
}
say "";
$dbh->do ("drop table $tbl");
}
Enjoy, Have FUN! H.Merijn