fetchrow_array memory usage

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

I am working with a script that executes a variety of sql commands fed to it and prints the output to a flat file. Currently I am using the following method to print the records

while ((@cols)=$sth->fetchrow_array){
 print OFILE join "$delimiter" => (@cols),"\n";

    } #end while
[download]

My question is the following - does this execute the query and store everything in the array prior to printing or does it grab and print one row and at a time - which is what I am thinking it does? If all of the records are stored in the array prior to printing, then isn't there a chance I am going to run into some memory issues? We generally are dealing with 2-6 millions records with varying "widths".

Also, I had originally written it with error trapping like this

while ((@cols)=$sth->fetchrow_array||die("unable to print row $DBI::er
+rstr\n")){
 print OFILE join "$delimiter" => (@cols),"\n";

    } #end while
[download]

but this wouldn't compile and run. Is there a more efficient way in which to trap this error? Sometimes we get divide by zero errors that should have been captured by this. Do I need to do something like

if ($DBI::errstr) {die("error $DBI::errstr\n");}
[download]

Regards, Tony

Comment on fetchrow_array memory usage Select or Download Code

Replies are listed 'Best First'.
Re: fetchrow_array memory usage by Zaxo (Archbishop) on Jun 22, 2005 at 01:26 UTC
Whether `fetchrow_array` gets all the selections at once or not depends on the DBD module. IIRC, mysql grabs them all, but an rdbms with cursors could deliver one at a time. That comes at a cost to speed, but so does running out of memory. Set RaiseError => 1 in the db handle to get handier error handling. Update: If yours is MySQL, you can `SELECT foo, bar, baz INTO OUTFILE '/path/to/flatfile.txt' . . .` , provided you have FILE privilege. After Compline, Zaxo	[reply]
Re: fetchrow_array memory usage by davidrw (Prior) on Jun 22, 2005 at 01:32 UTC
My question is the following - does this execute the query and store everything in the array prior to printing or does it grab and print one row and at a time - which is what I am thinking it does? Yup -- it executes the query in the database (Update just read Zaxo's reply--y, i guess i'm thinking of RDBMS that support the row-at-a-time fetching) and the "fetch" calls just bring back one row at a time, and you're also right that this (all the fetch* methods) prevents your code from loading the entire result in memory at once. Of course, there are times where loading all-at-once is just fine and that's that the select* (selectrow_, selectall_, selectcol_*) DBI functions are for.	[reply]
Re^2: fetchrow_array memory usage by ctaustin (Sexton) on Jun 22, 2005 at 05:08 UTC
My RDBMS is Oracle, so it is using cursors - so it looks like I am getting one row at a time. Thanks for all of the feedback. I'll be setting the RaiseError now. Regards, Tony	[reply]