I have a really simple script that queries a database then loops over each row like this: 

$sth = $dbh->prepare("select COLUMN_1 from table where COLUMN_2 < 2500
+");
$sth->execute();

while (my ($column_1_value) = $sth->fetchrow_array()) {

# do some processing on the column 1 value here

}

[download]

There is nothing fancy here except the query brings back 60 million rows! As a result, when I try to run the script it brings the database and machine running the database to its knees during the execution of the SQL statement.

Short of database level things or memory upgrades (that are largely outside my control) does anyone have any suggestions for handling these big queries in a more effective manner?

One thought I had was to break the query up into smaller chunks as such: 


$sth = $dbh->prepare("select COLUMN_1 from table where COLUMN_2 betwee
+n ? and ?");

for ($i = 0; $i < 2500; $i += 100) {

    $sth->execute($i, $i + 100);

    while (my ($column_1_value) = $sth->fetchrow_array()) {

        # do some processing on the column 1 value here

    }

}

[download]
but that seems ugly to me. Surely there must be a more elegant solution?

Thanks.

In reply to Big database queries by ezekiel

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.