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
}
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
}
}
but that seems ugly to me. Surely there must be a more elegant solution?
Thanks.
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.
|