Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Speeding up the DBI

by gmax (Abbot)
on Jul 14, 2003 at 12:02 UTC ( #273952=perlmeditation: print w/ replies, xml ) Need Help??

Help for this page

Select Code to Download


  1. or download this
    for (@values) {
       my $affected =
          $dbh->do(qq{INSERT INTO $table (col1, col2) VALUES (?, ?)},
            undef, @$_);
    }
    
  2. or download this
    my $sth = $dbh->prepare(qq{INSERT INTO $table (col1, col2) VALUES (?, 
    +?)});
    for (@values) {
       # do something with the values
       my $affected = $sth->execute (@$_);
    }
    
  3. or download this
    my $query = qq{INSERT INTO $table (column1, column2) VALUES };
    my @values = (['a','b','c'],
    ...
           .')';
    }
    my $affected = $dbh->do($query);
    
  4. or download this
    $sth->execute; # dataset has 1,000,000 records
    #
    ...
        # do something with $aref
        # $aref now contains (at most) 5,000 rows
    };
    
  5. or download this
    my $rowcache;
    while (my $aref = shift(@$rowcache)
    ...
        # do something with $aref
        # $aref now contains only one row
    };
    
  6. or download this
    #!/usr/bin/perl -w
    # create a MySQL test database
    ...
    $inserted += $dbh->do($query) if $count;
    print "inserted $inserted records\n";
    $dbh->disconnect;
    
  7. or download this
    #!/usr/bin/perl -w
    # create a SQLite test database
    ...
    print "inserted $inserted records\n";
    $dbh->disconnect;
    
  8. or download this
    #!/usr/bin/perl -w
    # test_profile.pl
    ...
    $sth->execute();
    print "@$_" while  $_ = $sth->fetchrow_arrayref();
    $dbh->disconnect();
    
  9. or download this
    c:\path> set DBI_PROFILE=2
    c:\path> perl test_profile.pl
    
  10. or download this
    $ DBI_PROFILE=2 perl test_profile.pl
    
  11. or download this
    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, ma
    +x 0.001857s)
    'select count(*) from testdbi' =>
        0.000697s / 6 = 0.000116s avg (first 0.000119s, min 0.000010s, max
    + 0.000511s)
    
  12. or download this
    DBI::Profile: 0.002569 seconds 0.46% (16 method calls) test_profile.pl
    'DESTROY' =>
    ...
        0.000041s / 2 = 0.000021s avg (first 0.000028s, min 0.000013s, max
    + 0.000028s)
    'prepare' =>
        0.000121s
    
  13. or download this
    $ DBI_PROFILE=4/DBI::ProfileDumper perl test_profile.pl
    $ dbiprof -match key1=fetchrow_arrayref
    ...
      Shortest Time : 0.000013 seconds
      Average Time  : 0.000021 seconds
      Key 1         : fetchrow_arrayref
    
  14. or download this
    $ perldoc DBI::ProfileDumper
    $ perldoc dbiprof
    
  15. or download this
    $dbh->{Profile} = 4;
    $sth->{Profile} = 6;
    
  16. or download this
    DBI::Profile: 0.000444 seconds (3 method calls) test_profile.pl
    'DESTROY' =>
    ...
        0.000384s
    'DBD::mysql::st::fetchrow_arrayref' =>
        0.000041s / 2 = 0.000021s avg (first 0.000027s, min 0.000014s, max
    + 0.000027s)
    
  17. or download this
    $dbh->{Profile} = 0;
    $sth->{Profile} = 0;
    
  18. or download this
        # create a DBI::Profile object. Notice that the object
        # is an attribute of $sth, not a standalone entity.
    ...
        # at DESTROY time
        $sth->{Profile} =0;
    }
    
  19. or download this
    perldoc DBI::Profile
    
  20. or download this
    #!/usr/bin/perl -w
    use strict;
    ...
    profile_calls($sth20, 20, $max_rows);
    
    $dbh->disconnect;
    
  21. or download this
    #
    # NB. Output edited and trimmed to fit the screen
    ...
    'fetchrow_array'    => 0.758613s / 100001 = 0.000008s avg
    'fetchrow_arrayref' => 0.690056s / 100001 = 0.000007s avg
    'fetchrow_hashref'  => 4.451645s / 100001 = 0.000045s avg
    

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://273952]
Approved by Corion
Front-paged by broquaint
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (6)
As of 2014-12-25 15:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (160 votes), past polls