Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
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 scrutinizing the Monastery: (13)
As of 2014-09-18 13:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (115 votes), past polls