for (@values) { my $affected = $dbh->do(qq{INSERT INTO $table (col1, col2) VALUES (?, ?)}, undef, @$_); } #### my $sth = $dbh->prepare(qq{INSERT INTO $table (col1, col2) VALUES (?, ?)}); for (@values) { # do something with the values my $affected = $sth->execute (@$_); } #### my $query = qq{INSERT INTO $table (column1, column2) VALUES }; my @values = (['a','b','c'], ['d','e','f'], ['g','h','i'] ); my $start=0; for (@values) { $query .= ',' if $start++; $query .= '(' . (join (",", map { $dbh->quote($_)} @$_)) .')'; } my $affected = $dbh->do($query); #### $sth->execute; # dataset has 1,000,000 records # # DON'T DO THIS # my $aref = $sth->fetchall_arrayref; # # It would create a 1,000,000 rows array my $max_rows = 5_000; while (my $aref = $sth->fetchall_arrayref(undef, $max_rows)) { # do something with $aref # $aref now contains (at most) 5,000 rows }; #### my $rowcache; while (my $aref = shift(@$rowcache) || shift ( @{$rowcache= $sth->fetchall_arrayref(undef, $max_rows)} ) ) { # do something with $aref # $aref now contains only one row }; #### #!/usr/bin/perl -w # create a MySQL test database use strict; use DBI; my $db = shift || 'test'; my $dbh = DBI->connect("dbi:mysql:$db" . ";mysql_read_default_file=$ENV{HOME}/.my.cnf", undef, undef, # replace with the following line if you don't have a config file # , "username", "password", {RaiseError => 1}) or die "can't connect\n"; my $max_fields = 20; my $field_size = 9; my $max_records = 100_000; my $max_rows_per_query = 10000; # adjust this value to max_allowed_packet my $count = 0; my $text = 'abcdefghi'; my $inserted = 0; $dbh->do(qq{drop table if exists testdbi}); $dbh->do(qq{create table testdbi ( } . (join ",", map {"id$_ char($field_size) not null"} (1..$max_fields)) .qq{ , primary key (id1) )}); my $base_query = qq{INSERT INTO testdbi VALUES}; my $query = $base_query; # create a multiple insertion statement # INSERT ... VALUES (...), (...), (...) ... ; # for (1..$max_records) { $query .= ',' if $count++; $query .= '(' . join( ",", map( {"'". ($text++)."'"}(1 .. $max_fields))) .')'; if ($count >= $max_rows_per_query) { $inserted += $dbh->do($query); print "$inserted\n"; $query = $base_query; $count = 0; } } $inserted += $dbh->do($query) if $count; print "inserted $inserted records\n"; $dbh->disconnect; #### #!/usr/bin/perl -w # create a SQLite test database use strict; use DBI; my $db = shift || 'test'; my $dbh = DBI->connect("dbi:SQLite:$db", "","", {RaiseError => 1, PrintError=>0}) or die "can't connect\n"; my $max_fields = 20; my $field_size = 9; my $max_records = 100_000; my $max_commit = 5_000; my $text = 'abcdefghi'; my $inserted = 0; my $create_query = "create table testdbi ( " . (join ",", map {"id$_ char($field_size) not null"} (1..$max_fields)) . ", primary key (id1))"; # Drops existing table, ignoring errors. # Sort of "DROP TABLE IF EXISTS" in MySQL eval { $dbh->do(qq{drop table testdbi}) }; $dbh->do($create_query); $dbh->do('begin'); my $sth = $dbh->prepare(qq{INSERT INTO testdbi VALUES (} . (join ",", map {'?'} (1..$max_fields)) .")" ); for (1..$max_records) { $inserted += $sth->execute( map {$text++} (1..$max_fields) ); if (($inserted % $max_commit) == 0) { $dbh->do('commit'); print "$inserted\n"; $dbh->do('begin'); } } $dbh->do('commit'); print "inserted $inserted records\n"; $dbh->disconnect; #### #!/usr/bin/perl -w # test_profile.pl use DBI; use strict; my $dbh = DBI->connect("DBI:mysql:test;host=localhost", "user", "password", {RaiseError => 1}) or die "can't connect\n"; my $query = qq{select count(*) from testdbi}; my $sth = $dbh->prepare($query); $sth->execute(); print "@$_" while $_ = $sth->fetchrow_arrayref(); $dbh->disconnect(); #### c:\path> set DBI_PROFILE=2 c:\path> perl test_profile.pl #### $ DBI_PROFILE=2 perl test_profile.pl #### DBI::Profile: 0.002769 seconds 1.09% (16 method calls) test_profile.pl '' => 0.002072s / 10 = 0.000207s avg (first 0.000007s, min 0.000003s, max 0.001857s) 'select count(*) from testdbi' => 0.000697s / 6 = 0.000116s avg (first 0.000119s, min 0.000010s, max 0.000511s) #### DBI::Profile: 0.002569 seconds 0.46% (16 method calls) test_profile.pl 'DESTROY' => 0.000021s / 2 = 0.000010s avg (first 0.000011s, min 0.000010s, max 0.000011s) 'FETCH' => 0.000007s 'STORE' => 0.000052s / 5 = 0.000010s avg (first 0.000026s, min 0.000003s, max 0.000026s) 'connect' => 0.001862s 'default_user' => 0.000031s 'disconnect' => 0.000106s 'disconnect_all' => 0.000014s 'execute' => 0.000314s 'fetchrow_arrayref' => 0.000041s / 2 = 0.000021s avg (first 0.000028s, min 0.000013s, max 0.000028s) 'prepare' => 0.000121s #### $ DBI_PROFILE=4/DBI::ProfileDumper perl test_profile.pl $ dbiprof -match key1=fetchrow_arrayref DBI Profile Data (DBI::ProfileDumper 1.0) Program : test_profile.pl Path : [ DBIprofile_MethodName ] Total Records : 1 (showing 1, sorted by total) Total Count : 2 Total Runtime : 0.000041 seconds #####[ 1 ]########################################################### Count : 2 Total Time : 0.000041 seconds Longest Time : 0.000028 seconds Shortest Time : 0.000013 seconds Average Time : 0.000021 seconds Key 1 : fetchrow_arrayref #### $ perldoc DBI::ProfileDumper $ perldoc dbiprof #### $dbh->{Profile} = 4; $sth->{Profile} = 6; #### DBI::Profile: 0.000444 seconds (3 method calls) test_profile.pl 'DESTROY' => 0.000012s 'disconnect' => 0.000311s 'prepare' => 0.000121s DBI::Profile: 0.000586 seconds (5 method calls) test_profile.pl 'DBD::mysql::st::DESTROY' => 0.000015s 'DBD::mysql::st::STORE' => 0.000146s 'DBD::mysql::st::execute' => 0.000384s 'DBD::mysql::st::fetchrow_arrayref' => 0.000041s / 2 = 0.000021s avg (first 0.000027s, min 0.000014s, max 0.000027s) #### $dbh->{Profile} = 0; $sth->{Profile} = 0; #### # create a DBI::Profile object. Notice that the object # is an attribute of $sth, not a standalone entity. # no "my" is required $sth->{Profile} = DBI::Profile->new; # Set the profile level $sth->{Profile} = 4; # do something with $sth # .... # # print the output using a built-in method print $sth->{Profile}->format; # reset the internal data, so new profiling info # can be stored $sth->{Profile}->{Data} = undef; # do something else with $sth # .... # # print the profile results again print $sth->{Profile}->format; # finally, disable the profile status, so it does nothing # at DESTROY time $sth->{Profile} =0; } #### perldoc DBI::Profile #### #!/usr/bin/perl -w use strict; use DBI; use DBI::Profile; use Benchmark qw(cmpthese); my $dbh; my $db = 'test'; my $driver = shift || 'mysql'; if ($driver eq 'mysql') { $dbh = DBI->connect("dbi:mysql:$db", "user", "password", {RaiseError => 1}) or die "can't connect\n"; } elsif ($driver eq 'SQLite') { $dbh = DBI->connect("dbi:SQLite:$db", "","", {RaiseError => 1}) or die "can't connect\n"; } else { # insert your favorite driver connection code here die "Driver $driver not supported\n" } # # testdbi is a table with 100,000 records # with 20 columns 9 chars each # my $query1 = qq{SELECT id1 from testdbi }; # 1 column my $query10 = qq{ SELECT id1,id2,id3,id4,id5,id6,id7,id8,id9,id10 FROM testdbi}; # 10 columns my $query20 = qq{ SELECT id1,id2,id3,id4,id5,id6,id7,id8,id9,id10, id11,id12,id13,id14,id15,id16,id17,id18,id19,id20 FROM testdbi }; # 20 columns sub profile_calls { my ($sth, $columns, $max_rows) = @_; my (@array, $rowcache, $aref, $href); printf "PROFILE - FETCHING %d COLUMN%s\n", $columns, $columns > 1 ? "s" :""; $sth->{Profile} = DBI::Profile->new; $sth->{Profile} = 4; $sth->execute; while (@array = $sth->fetchrow_array()) {}; $sth->execute; while ($aref = $sth->fetchrow_arrayref()) {}; $sth->execute; while ($href = $sth->fetchrow_hashref()) {}; $sth->execute; while ($aref = shift(@$rowcache) || shift (@{$rowcache= $sth->fetchall_arrayref(undef, $max_rows)})) {}; print $sth->{Profile}->format; $sth->{Profile} =0; } sub compare_calls { my ($sth, $columns, $max_rows) = @_; my (@array, $rowcache, $aref, $href); printf "COMPARE - FETCHING %d COLUMN%s\n", $columns, $columns > 1 ? "s" :""; cmpthese (5, { '---fetchrow_array' => sub {$sth->execute; while (@array = $sth->fetchrow_array()) {} }, 'fetchrow_arrayref' => sub {$sth->execute; while ($aref = $sth->fetchrow_arrayref()) {} }, '-fetchrow_hashref' => sub {$sth->execute; while ($href = $sth->fetchrow_hashref()) {} }, 'fetchall_arrayref' => sub {$sth->execute; while ($aref = $sth->fetchall_arrayref(undef, $max_rows)) {} }, 'fetchall_arrayref(buffered)' => sub {$sth->execute; while ($aref = shift(@$rowcache) || shift (@{$rowcache = $sth->fetchall_arrayref(undef, $max_rows)})) {} }, } ); } my $max_rows = 1000; my $sth1 = $dbh->prepare($query1); my $sth10 = $dbh->prepare($query10); my $sth20 = $dbh->prepare($query20); compare_calls($sth1, 1, 10_000); compare_calls($sth10, 10, $max_rows); compare_calls($sth20, 20, $max_rows); profile_calls($sth1, 1, $max_rows); profile_calls($sth10, 10, $max_rows); profile_calls($sth20, 20, $max_rows); $dbh->disconnect; #### # # NB. Output edited and trimmed to fit the screen # COMPARE - FETCHING 1 COLUMN Benchmark: timing 5 iterations of fetchrow_array, fetchrow_hashref, fetchall_arrayref, fetchall_arrayref(buffered), fetchrow_arrayref... fetchrow_array: 5 secs ( 2.28 usr + 0.73 sys = 3.01 CPU) fetchrow_hashref: 11 secs ( 8.81 usr + 0.76 sys = 9.57 CPU) fetchall_arrayref: 5 secs ( 2.24 usr + 0.77 sys = 3.01 CPU) fetchall_arrayref(buf): 5 secs ( 2.57 usr + 0.74 sys = 3.31 CPU) fetchrow_arrayref: 4 secs ( 2.00 usr + 0.71 sys = 2.71 CPU) Rate fetchrow_hashref fetchall_arrayref(buffered) fetchall_arrayref fetchrow_array fetchrow_arrayref 1 2 3 4 5 1 fetchrow_hashref 0.522/s -- -65% -69% -69% -72% 2 fetchall_arrayref(buf) 1.51/s 189% -- -9% -9% -18% 3 fetchall_arrayref 1.66/s 218% 10% -- 0% -10% 4 fetchrow_array 1.66/s 218% 10% 0% -- -10% 5 fetchrow_arrayref 1.85/s 253% 22% 11% 11% -- COMPARE - FETCHING 10 COLUMNs Benchmark: timing 5 iterations of fetchrow_array, fetchrow_hashref, fetchall_arrayref, fetchall_arrayref(buffered), fetchrow_arrayref... fetchrow_array: 13 secs ( 9.92 usr + 1.03 sys = 10.95 CPU) fetchrow_hashref: 20 secs (16.97 usr + 1.07 sys = 18.04 CPU) fetchall_arrayref: 11 secs ( 6.64 usr + 1.09 sys = 7.73 CPU) fetchall_arrayref(buf): 11 secs ( 7.40 usr + 1.12 sys = 8.52 CPU) fetchrow_arrayref: 7 secs ( 3.81 usr + 0.99 sys = 4.80 CPU) s/iter -fetchrow_hashref ---fetchrow_array fetchall_arrayref(buffered) fetchall_arrayref fetchrow_arrayref 1 2 3 4 5 1 fetchrow_hashref 3.61 -- -39% -53% -57% -73% 2 fetchrow_array 2.19 65% -- -22% -29% -56% 3 fetchall_arrayref(buf) 1.70 112% 29% -- -9% -44% 4 fetchall_arrayref 1.55 133% 42% 10% -- -38% 5 fetchrow_arrayref 0.960 276% 128% 77% 61% -- COMPARE - FETCHING 20 COLUMNs Benchmark: timing 5 iterations of fetchrow_array, fetchrow_hashref, fetchall_arrayref, fetchall_arrayref(buffered), fetchrow_arrayref... fetchrow_array: 22 secs (16.91 usr + 1.24 sys = 18.15 CPU) fetchrow_hashref: 30 secs (24.53 usr + 1.14 sys = 25.67 CPU) fetchall_arrayref: 18 secs (11.99 usr + 1.10 sys = 13.09 CPU) fetchall_arrayref(buf): 19 secs (12.22 usr + 1.12 sys = 13.34 CPU) fetchrow_arrayref: 10 secs ( 5.17 usr + 1.26 sys = 6.43 CPU) s/iter fetchrow_hashref fetchrow_array fetchall_arrayref(buffered) fetchall_arrayref fetchrow_arrayref 1 2 3 4 5 1 fetchrow_hashref 5.13 -- -29% -48% -49% -75% 2 fetchrow_array 3.63 41% -- -27% -28% -65% 3 fetchall_arrayref(buf) 2.67 92% 36% -- -2% -52% 4 fetchall_arrayref 2.62 96% 39% 2% -- -51% 5 fetchrow_arrayref 1.29 299% 182% 107% 104% -- PROFILE - FETCHING 1 COLUMN DBI::Profile: 5.487208 seconds (300110 method calls) test_dbi_calls.pl 'FETCH' => 0.000013s 'STORE' => 0.000245s 'execute' => 2.471070s / 4 = 0.617767s avg 'fetchall_arrayref' => 0.236404s / 101 = 0.002341s avg 'fetchrow_array' => 0.255235s / 100001 = 0.000003s avg 'fetchrow_arrayref' => 0.270196s / 100001 = 0.000003s avg 'fetchrow_hashref' => 2.254045s / 100001 = 0.000023s avg PROFILE - FETCHING 10 COLUMNs DBI::Profile: 8.832431 seconds (300110 method calls) test_dbi_calls.pl 'FETCH' => 0.000004s 'STORE' => 0.000219s 'execute' => 3.649873s / 4 = 0.912468s avg 'fetchall_arrayref' => 0.930104s / 101 = 0.009209s avg 'fetchrow_array' => 0.536815s / 100001 = 0.000005s avg 'fetchrow_arrayref' => 0.498186s / 100001 = 0.000005s avg 'fetchrow_hashref' => 3.217229s / 100001 = 0.000032s avg PROFILE - FETCHING 20 COLUMNs DBI::Profile: 12.905533 seconds (300110 method calls) test_dbi_calls.pl 'FETCH' => 0.000006s 'STORE' => 0.000219s 'execute' => 5.516249s / 4 = 1.379062s avg 'fetchall_arrayref' => 1.488744s / 101 = 0.014740s avg 'fetchrow_array' => 0.758613s / 100001 = 0.000008s avg 'fetchrow_arrayref' => 0.690056s / 100001 = 0.000007s avg 'fetchrow_hashref' => 4.451645s / 100001 = 0.000045s avg