Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

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:


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?


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

Replies are listed 'Best First'.
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 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 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.


      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.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://638503]
Approved by Corion
Front-paged by Corion
[ambrus]: you already have blackboards and a canvas for projector or overhead transparencies (or positive film slide projector, not used for maths) canvas in the same lecture halls today, and switch in a few minutes between presentations,
[ambrus]: they're only difficult to use together.
[ambrus]: overhead transparencies are a nice convenience by the way that mix the two slides, because you can write them in advance and edit them during the presentation easily. but they're not very much in fasion these days.
[ambrus]: you can even print them.
[Discipulus]: ambrus i'm trying out MremoteNG which wrap putty and rdp and many other things..
[Corion]: ambrus: Yes, ideally you would have the ease of overhead projection transparencies and pens drawing on them, combined with the computer generated slide text...
[Corion]: Maybe the solution would be a tablet (with pens), like the Wacom tablets, but you still need good software and need to know how to operate it well in an interactive setting ;)

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (11)
As of 2017-09-26 10:30 GMT
Find Nodes?
    Voting Booth?
    During the recent solar eclipse, I:

    Results (293 votes). Check out past polls.