Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Re: Re: It's bad manners to slurp

by mpeppler (Vicar)
on Apr 29, 2004 at 15:26 UTC ( [id://349158]=note: print w/replies, xml ) Need Help??


in reply to Re: It's bad manners to slurp
in thread It's bad manners to slurp

Depending on your DBD, calling fetchrow() may also slurp the entire result into memory. It just won't give it to you all at once, which might reduce memory usage to some degree in exchange for the added overhead in repeated fetchrow() calls.
I would contend that this is a bug, and a pretty serious one at that. While pre-fetching data is (often) a good idea, it should never be done blindly without checking the available space (and pre-fetching X million rows doesn't make sense, anyway!)

Michael

Replies are listed 'Best First'.
Re: Re: Re: It's bad manners to slurp
by samtregar (Abbot) on Apr 29, 2004 at 15:31 UTC
    Contend all you want, as far as I know it's the truth for at least DBD::mysql and DBD::Pg. I imagine it would be fairly easy to test this theory with a properly giant DB and GTop.

    -sam

      It is possible to change this behaviour with both DBD::mysql and DBD::pg For DBD::mysql you can give prepare {mysql_use_result => 1}. But be aware that by doing this, you change the behaviour of the rows() method. And for DBD::pg you can use cursors:
      $db->do("declare imprecator CURSOR FOR select * from test"); $c = $db->prepare("FETCH 100 FROM imprecator") for (1..10) { # I have a more robust example somewhere $c->execute(); print Data::Dumper::Dumper($c->fetchall_arrayref()) }
      It would probably be possible to encode this in the driver so you could pass an attribute to prepare {pg_use_cursor =>1}.
        Another way for simple queries with MySQL 4.x is to use the HANDLER statement.
        HANDLER table OPEN; HANDLER table READ FIRST; HANDLER table READ NEXT;
        Interesting. However, according the DBD::mysql docs mysql_use_result "tends to block other processes." I'm not sure what that means exactly but it doesn't sound good! I think I'll stick with LIMIT and OFFSET...

        -sam

      You're right - if you're stuck with a driver that behaves this way you need to be careful. IIRC it is configurable in DBD::mysql, don't know about DBD::Pg.

      Still - it's something that shows a fundamental lack of understanding of large-scale database management issues that this behavior is the default for these two drivers (and no, I don't know if it's the driver's fault or the underlying API)

      Michael

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://349158]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others perusing the Monastery: (7)
As of 2024-03-28 11:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found