DBI AoArefs dereference

jaiguevara has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks, Using DBI with MySQL, I want to load the results of SELECT via fetchrow_arrayref into an array of arrayrefs. I'm doing something wrong because I end up with a thousand copies of the last row retrieved instead of a thousand unique rows.

my @qrydata; # I want to load the query rows into this array.

my $xx=0; #row counter
while (my $row = $sth->fetchrow_arrayref) {
  # Does the arrayref have the expected data? Yes - unique rows
    for my $fld(@$row) { print "$fld|"; } print "\n";
  # so push the arrayref onto the Array
    push @qrydata, $row;
  # cut it short for test purposes - save 10 rows
    last if $xx++ >10;   
}
# this prints out 10 copies of the last row fetched!! :(
for my $row (@qrydata) {
    for my $fld(@$row) { print "$fld<"; } print "\n";
}
[download]

Any insights would be appreciated. Why am I doing this? I need the total row count before I proceed. I'm thinking this is faster than running SELECT COUNT first for the query, then iterating through the query again. At this point I'm rethinking this, but still want to know what's wrong with my array scheme.

Comment on DBI AoArefs dereference Download Code

Replies are listed 'Best First'.
Re: DBI AoArefs dereference by ikegami (Patriarch) on Jun 14, 2010 at 04:57 UTC
`fetchrow_arrayref` always returns a reference to the same array as an optimisation. This is documented. If you wish to save the data, you need to copy it. `push @qrydata, [ @$row ];` [download] By the way, `last if $xx++ >10;` [download] should be `if (@qrydata > 10) { $sth->finish(); last; }` [download] The counter variable is useless, and calling `finish` avoids a warning. Even better would be to use a `LIMIT` clause to not fetch the rows you don't want in the first place. Then you'd also be able to use `selectall_*`.	[reply] [d/l] [select]
Re: DBI AoArefs dereference by graff (Chancellor) on Jun 14, 2010 at 05:27 UTC
You said: I need the total row count before I proceed. I'm thinking this is faster than running SELECT COUNT first for the query, then iterating through the query again. It might be fun and instructive to benchmark the alternatives -- that would certainly be better than guessing. (Hint: "select count" might not cost much, but it depends on whether the query involves difficult joins and conditions, requiring full scans of large tables.) In any case, I don't see how the OP code helps to get a row count, since it seems to stop counting at 10. If the query result isn't outrageously large (i.e. fits in memory), you can just use DBI::fetchall_arrayref, get the count right away, and iterate through the rows too: `my $result_rows = $sth->fetchall_arrayref; if ( ref( $result_rows ne 'ARRAY' or @$result_rows == 0 ) { print "Oops! No data. Now what?\n"; } else { print "Yo! you got " . scalar( @$result_rows ) . " records:\n"; for my $row ( @$result_rows ) { print join( "\t", @$row ), "\n"; } }` [download]	[reply] [d/l]
Re: DBI AoArefs dereference by pajout (Curate) on Jun 14, 2010 at 07:46 UTC
If you can change sql query, add "LIMIT = 10" clause (it is, in my opinion, best for performance), otherwise you can use `my $AoA = $dbh->selectall_arrayref($sql, {MaxRows=>10}, @sqlparams);` [download]	[reply] [d/l]
Re: DBI AoArefs dereference by jaiguevara (Initiate) on Jun 15, 2010 at 05:22 UTC
thanks for the responses. Of course the adage "when in doubt read the manual" applies. My main problem was fetchrow_arrayref's use of the same reference for every row. Also the various ideas on dealing with the row count and optimizing the query are helpful.	[reply]