in reply to Re^4: perl mysql - INSERT INTO, 157 columns
in thread perl mysql - INSERT INTO, 157 columns

Usually not

Well, maybe so but you really should limit in the case of postgres.

This is basically your example code running against 9.4devel, with and without a limiting where-clause: 

$ perl ./tux.pl #  
PostgreSQL 9.4devel_HEAD_20140502_2044_0717748 on x86_64-unknown-linux
+-gnu, compiled by gcc (GCC) 4.9.0, 64-bit

              prepare  execute  finish
 no where     0.00007  5.52991  0.02707
 no where     0.00005  5.42172  0.03283
 no where     0.00005  5.42320  0.03247

where 0=1     0.00005  0.00049  0.00000
where 0=1     0.00002  0.00013  0.00000
where 0=1     0.00002  0.00012  0.00000

[download]

(foo has 10M 1-column rows; just a create table foo as select n from generate_series(1, 10000000) as f(n); )

(What the hell -- let me just dump the test here too, even if it's a bit clunky (disks are cheap and patient): 

use strict;
use warnings;
use DBI;
use Time::HiRes qw/gettimeofday tv_interval/;
my $dbh = DBI->connect or die "oops - $!\n";
print $dbh->selectrow_arrayref('select version()')->[0], "\n\n";
my $sql1 = "select * from foo";
my $sql2 = "select * from foo where 0 = 1";
print "              prepare  execute  finish\n";
time_this( $dbh, $sql1, ' no where' );
time_this( $dbh, $sql1, ' no where' );
time_this( $dbh, $sql1, ' no where' );
print "\n";
time_this( $dbh, $sql2, 'where 0=1');
time_this( $dbh, $sql2, 'where 0=1' );
time_this( $dbh, $sql2, 'where 0=1' );

sub time_this {
    my ($dbh, $sql, $how) = @_;
    my $t0;
    $t0 = [gettimeofday];
    my $sth = $dbh->prepare( $sql );
    print $how, "   ";
    printf("  %7.5f", 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", tv_interval($t0 , [gettimeofday]));
    print "\n";
}

[download]
)

Replies are listed 'Best First'.
Re^6: perl mysql - INSERT INTO, 157 columns
by Tux (Canon) on May 03, 2014 at 12:52 UTC

    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

    [download]
    Read more... (3 kB)

    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom