in reply to It's bad manners to slurp

I am aware of a similar issue with DBI code. In particular, the use of selectall or fetchall, compared with fetchrow. I have seen instances.

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.

If you're serious about avoiding this problem in DBI then you need to use LIMIT and OFFSET (or their cursorly equivalent) to step through your result set.

-sam

Replies are listed 'Best First'.
Re: Re: It's bad manners to slurp
by mpeppler (Vicar) on Apr 29, 2004 at 15:26 UTC
    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

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