title: What's the most efficient way to work through a large result in Perl DBI?
body: <p>I have a table with a few million rows.  Currently, I'm working my way through them 10,000 at a time by doing this:</p>
  
  <pre><code>for (my $ival = 0; $ival &lt; $c_count; $ival += 10000)
  {
      my %record;
  
      my $qry = $dbh-&gt;prepare
              ( "select * from big_table where address not like '%-XX%' limit $ival, 10000");
      $qry-&gt;execute();
  
      $qry-&gt;bind_columns( \(@record{ @{$qry-&gt;{NAME_lc} } } ) );
  
      while (my $record = $qry-&gt;fetch){
          this_is_where_the_magic_happens($record)
      }
  }
  </code></pre>
  
  <p>I did some benchmarking and I found that the prepare/execute part, while initially fast, slows down considerably after multiple 10,000 row batch.  Is this a boneheaded way to write this?  I just know if I try to select everything in one go, this query takes forever.</p>
  
  <p>Here's some snippets from the log:</p>
  
  <pre><code>(Thu Aug 21 12:51:59 2014) Processing records 0 to 10000
  SQL Select =&gt;  1 wallclock secs ( 0.01 usr +  0.00 sys =  0.01 CPU)
  (Thu Aug 21 12:52:13 2014) Processing records 10000 to 20000
  SQL Select =&gt;  1 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)
  (Thu Aug 21 12:52:25 2014) Processing records 20000 to 30000
  SQL Select =&gt;  2 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)
  (Thu Aug 21 12:52:40 2014) Processing records 30000 to 40000
  SQL Select =&gt;  5 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)
  (Thu Aug 21 12:52:57 2014) Processing records 40000 to 50000
  SQL Select =&gt; 13 wallclock secs ( 0.01 usr +  0.00 sys =  0.01 CPU)
  ...
  (Thu Aug 21 14:33:19 2014) Processing records 650000 to 660000
  SQL Select =&gt; 134 wallclock secs ( 0.01 usr +  0.00 sys =  0.01 CPU)
  (Thu Aug 21 14:35:50 2014) Processing records 660000 to 670000
  SQL Select =&gt; 138 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)
  (Thu Aug 21 14:38:27 2014) Processing records 670000 to 680000
  SQL Select =&gt; 137 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)
  (Thu Aug 21 14:41:00 2014) Processing records 680000 to 690000
  SQL Select =&gt; 134 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)
  </code></pre>
  
  <p>Would it be faster to do some other way?  Should I remove the 'where' clause and just throw out results I don't want in the loop?</p>
  
  <p>Thanks for the help.</p>