Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re^3: perl mysql - INSERT INTO, 157 columns

by erix (Vicar)
on May 02, 2014 at 12:53 UTC ( #1084783=note: print w/ replies, xml ) Need Help??


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

my $sth = $dbh->prepare ("select * from foo");

Surely that should be

my $sth = $dbh->prepare ("select * from foo where 1=0 ");

Or something like that (limit, top, or whatever to not have the db think deeper than necessary...).


Comment on Re^3: perl mysql - INSERT INTO, 157 columns
Download Code
Re^4: perl mysql - INSERT INTO, 157 columns
by Tux (Monsignor) on May 02, 2014 at 14:57 UTC

    Usually not: the fetch starts only at the first fetch, so no table content is actually fetched at all.

    Some DBD's do not even need the execute. They have the table/field info readily available after the prepare.


    Enjoy, Have FUN! H.Merijn
      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

      (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"; }
      )

        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

        Enjoy, Have FUN! H.Merijn

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1084783]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (15)
As of 2014-08-28 16:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (264 votes), past polls