Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Retrieving a MySQL query in chunks

by dannoura (Pilgrim)
on Sep 12, 2007 at 07:39 UTC ( #638503=perlquestion: print w/ replies, xml ) Need Help??
dannoura has asked for the wisdom of the Perl Monks concerning the following question:

hi,

Is there any way to retrieve a large (humongous even) reply from a MySQL query in chunks, so it doesn't freeze up the system?

thanks,
dan.

p.s. I mean besides storing it in a temporary table with unique ids from each record.

Comment on Retrieving a MySQL query in chunks
Re: Retrieving a MySQL query in chunks
by johnlawrence (Monk) on Sep 12, 2007 at 08:34 UTC
    I'm not sure if it's exactly what you want, but using LIMIT should do this. MySQL SELECT Syntax
Re: Retrieving a MySQL query in chunks
by stark (Pilgrim) on Sep 12, 2007 at 08:42 UTC

    Maybe you are looking for the "mysql_use_result" attribute of DBD::mysql.

    Set it like this:
    my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1});

    Otherwise the whole result is stored in memory (default "mysql_store_result").

    Hope this helps

Re: Retrieving a MySQL query in chunks
by eriam (Beadle) on Sep 12, 2007 at 11:02 UTC
    What you probably want is fetching the data with asynchrounous calls.

    You should have a look a POE::Component::SimpleDBI on cpan.

    Thanks

    Eriam
      That doesn't really solve the problem. The forked process still ends up loading the entire result set into memory at once. The answer for large queries is to set mysql_use_result.
        Sure it will load the stuff in memory (so yes he may need a lot of it), but the application won't freeze which is also what he was trying to achieve.

        Eriam

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://638503]
Approved by Corion
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (10)
As of 2014-09-18 08:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (109 votes), past polls