Perl DBI fetch portion of database?

Question

I'm working on a database, where the number of rows is somewhere above 1,000,000. I have my select statement, but if I fetchall to begin with, I run out of memory quickly. Here are my 2 questions:

1. Since I dont know the exact size of the database to start, is there any way to find out the size of the database without doing a fetchall? The computer literally cannot support it.

2. Is there any way to fetch say a certain chunk of the database, maybe like 5,000 at a time to process, instead of doing an individual fetchrow for each and every line? I just finished running a test, and to do it row by row, its looking to be almost 4 minutes per 1000 rows worked on, and the boss isnt looking favorably on a program that is going to take almost 3 days to complete.

This is my code:

while ($i < $rows)
{
    if ($i + $chunkRows < $rows)
    {
        for ($j = 0; $j < $chunkRows; $j++)
        {
            @array = $sth->fetchrow();
            ($nameOne, $numberOne, $numberTwo) = someFunction($lineCount,@array,$nameOne,$numberOne, $numberTwo);
        }
    }
    else #run for loop for j < rows % chunkrows
    $i = $i + $j
}

Answer 1

To get the number of rows in a table, you can use

Select count(*) from Table

To limit the number of rows returned, this may be specific to your database. MySQL, for example, has a Limit keyword which will let you pull back only a certain number of rows.

That being said, if you are pulling back all rows, you may want to add some other questions here describing specifically what you are doing, because thats not a common thing in most applications.

If you dont have a limit available in your database, you can do things like flag a column with a boolean to indicate that a row was processed, and then re-run your query for a limited number of rows, skipping those that have been completed. Or record the last row id processed, and then limit your next query to rows with a greater id. There's a lot of ways around that.

Answer 2

Show your fetchrow looping code; there may be ways to improve it, depending on how you are calling it and just what you are doing with the data.

I believe the database drivers for most databases will fetch multiple rows at once from the server; you are going to have to say what underlying type of database you are using to get good advice there. If indeed it is communicating with the server for each row, you are going to have to modify the SQL to get sets of rows at a time, but how to do that varies depending on what database you are using.

Ah, DB2. I'm not sure, but I think you have to do something like this:

SELECT *
FROM (SELECT col1, col2, col3, ROW_NUMBER() OVER () AS RN FROM table) AS cols
WHERE RN BETWEEN 1 AND 10000;

and adjust the numbers for each query until you get an empty result. Obviously this is more work on the database side to have it repeat the query multiple times; I don't know if there are DB2 ways to optimize this (i.e. temporary tables).