Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
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 scrutinizing the Monastery: (7)
As of 2014-12-27 17:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

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





    Results (177 votes), past polls