CODEWITHSUNDEEP
mysqlperldbi
py3r3str
py3r3str

Reputation: 1879

How to get data from big table row by row

I need to get all data from mysql table. What I've try so far is:

my $query = $connection->prepare("select * from table");
$query->execute();
while (my @row=$query->fetchrow_array)
{
    print format_row(@row);
}

but there is always a but...

Table has about 600M rows and apparently all results from query is store in memory after execute() command. There is not enough memory for this:(

My question is:

Is there a way to use perl DBI to get data from table row by row?Something like this:

my $query = $connection->prepare("select * from table");
while (my @row=$query->fetchrow_array)
{
    #....do stuff
}

btw, pagination is to slow:/

Upvotes: 0

Views: 658

Answers (3)

Bodo Hugo Barwich
Bodo Hugo Barwich

Reputation: 241

When working with Huge Tables I build Data Packages with dynamically built SQL Statements like

$sql = "SELECT * FROM table WHERE id>" . $lastid . " ORDER BY id LIMIT " . $packagesize

The Application will dynamically fill in $lastid according to each Package it processes.

If table has an ID Field id it has also an Index built on that Field so that the Performance is quite well.
It also limits Database Load by little rests between each Query.

Upvotes: 0

Tim Bunce
Tim Bunce

Reputation: 1110

apparently all results from query is store in memory after execute() command

That is the default behaviour of the mysql client library. You can disable it by using the mysql_use_result attribute on the database or statement handle.

Note that the read lock you'll have on the table will be held much longer while all the rows are being streamed to the client code. If that might be a concern you may want to use SQL_BUFFER_RESULT.

Upvotes: 2

Borodin
Borodin

Reputation: 126722

The fetchall_arrayref method takes two parameters, the second of which allows you to limit the number of rows fetched from the table at once

The following code reads 1,000 lines from the table at a time and processes each one

my $sth = $dbh->prepare("SELECT * FROM table");
$sth->execute;

while ( my $chunk = $sth->fetchall_arrayref( undef, 1000 ) ) {

    last unless @$chunk;    # Empty array returned at end of table

    for my $row ( @$chunk ) {

        print format_row(@$row);
    }
}

Upvotes: 2

Related Questions