Re: DBI fetchall_arrayref using $max_size doesn't dereference normally.

[gmax]

If you call $sth->fetchall_arrayref(undef,$max_rows) like you do, then your result will be an array of arrays, not an array of hashes, like you would like.

Moreover, your usage of $max_rows is not recommended. The docs state clearly that you should use a buffer to fetch records using that parameter.

# example from DBI docs
my $max_rows=10_000;
my $rows = []; # cache for batches of rows
while( my $row = ( shift(@$rows) ||
      shift(@{$rows=$sth->fetchall_arrayref({},$max_rows) || []}) )
 ) {
   ... # do something with $row (not $rows, which is only the cache)
 }

Last, but not least, be aware that you are using the same statement handler for a outer and an inner loop. That is a mistake! The second "prepare" will reset $sth, and it will not continue fetching records. You must use two of them, one for fetching records, and one for updating.

Please see:

• DBI recipes for several alternatives of fetching records.
• Speeding up the DBI for the usage of that specific idiom that you are trying.
• DBI/mysql gathering all rows on execute, about fetching rows all-at-once vs. one-by-one.

Update
Before trusting your code with 1 million records, try it with some smaller data set, to ensure that it's doing what you need.

[Tatnall]

gmax,

The code is currently at work, thank you. I'm sure there is an even better way than the way I modified the code per your suggestion since I don't understand all of what is happening or why.

If you or someone else could unpack the while statement it could be greatly appreciated.

[runrig]

The docs say that when there are no more rows, fetchall_arrayref returns a reference to an empty array. That would imply your while loop would never terminate (a reference is always true, even if it's a reference to an empty array). gmax's suggested code above fixes that.