Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

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

by Tux (Monsignor)
on May 03, 2014 at 12:52 UTC ( #1084887=note: print w/ replies, xml ) Need Help??


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

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


Comment on Re^6: perl mysql - INSERT INTO, 157 columns
Select or Download Code

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (4)
As of 2015-07-30 04:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (270 votes), past polls