[Dbix-class] how do resultsets actually work under the hood?

Ben Tilly btilly at gmail.com
Tue May 28 23:38:17 GMT 2013


As others have said, the answer is, "it depends".

In some databases (eg MySQL) the database will stream the entire
resultset to the client, which then iterates over it.  In others (I'm
pretty sure Oracle behaved this way), you get back data in chunks and
the client has the logic to know when to fetch another chunk.  This
can even be configurable.  I've had the fun in raw DBI of convincing
PostgreSQL to use cursors and send me back data in manageable chunks
as it was produced on the server.

But if, as in Java, you explicitly populate a collection, then you
have no choice but to take all of the data back.

On Tue, May 28, 2013 at 2:37 PM, Iain C Docherty
<dbix-class at iain-docherty.com> wrote:
> I was in conversation with a Java programmer today about the design of a
> database system, in a nutshell he was saying that in Java he would create a
> 'collection' of all rows in a table and then iterate over the collection of
> objects. If it was too big he would 'split' the query by searching for all
> records starting with 'a', then 'b' etc.
>
> I was appalled that this would eat up memory, and starting going on about
> DBIC and how in Perl I would create a resultset then iterate over it using
> 'next'.
>
> I soon realized that I didn't have much idea how this 'just worked' under
> the surface (in DBI I presume?) without using large amounts of memory to
> hold every row from the table.
>
> I have done some digging since, I have looked at the DBI code, but frankly I
> got rather lost.
>
> So, can anyone give a concise description on how this works, in particular.
>
> Where (if anywhere) does it store a record of every row in a resultset so
> that I can just call 'next' and quickly get the next row.
>
> Or does it do this by making a request to the database for every row? Isn't
> this slow?
>
> Thank you.
> Iain.
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk



More information about the DBIx-Class mailing list