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