Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

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

by Tux (Monsignor)
on May 02, 2014 at 14:57 UTC ( #1084797=note: print w/ replies, xml ) Need Help??


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

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


Comment on Re^4: perl mysql - INSERT INTO, 157 columns
Re^5: perl mysql - INSERT INTO, 157 columns
by erix (Vicar) on May 02, 2014 at 19:25 UTC
    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://1084797]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (12)
As of 2014-10-24 10:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (131 votes), past polls