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

[reply]
      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())
    }

      [download]
      It would probably be possible to encode this in the driver so you could pass an attribute to prepare {pg_use_cursor =>1}.
[reply]
[d/l]
        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;

        [download]
[reply]
[d/l]
        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

[reply]
      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

[reply]

In Section Meditations