DBIx::Class::ResultSet iterate over items new query every time

Question

I am using DBIx::Class::ResultSet to query for items and paginate my data. This is the query I use:

my $clients = $c->model('DB::User')->search(
    { active_yn => 'y', client_yn => 'y' },
    {   
        columns => [qw/id first_name last_name username email/],
        order_by => ['last_name'],
        page     => ($c->req->param('page') || 1), 
        rows     => 20,
    }   
);

Once, I get the resultset, I loop through the results in Template::Toolkit like so:

[% WHILE (client = clients.next) %]
    <tr>
        <td>[% client.first_name %] [% client.last_name %]</td>
    </tr>
[% END %]

My question is, does each time I call next issue a new query? It is unclear to me because the all method says it returns all elements in the set, so does the first query not do that? I'd like to be as efficient as possible. Thanks!

set DBIC_TRACE=1 in your environment, then run the query. You'll see the SQL that's being generated, and with a bit of debug loging, you'll be able to correlate the database access with the model, view, or controller actions. — Len Jaffe, Jun 06 '13 at 17:56
That's a good suggestion. Do you know where I can see the output from DBIC_TRACE? I'm not running the test server, so it doesn't go to STDOUT. I'm using nginx and fastcgi and I can't find it. — srchulo, Jun 06 '13 at 18:22
But the output typically goes to STDOUT, which is typically sent to the error log on a web server. — Len Jaffe, Jun 06 '13 at 19:02
Yeah, that's typically how it is with Apache. But with nginx I check /var/log/nginx/error.log and it doesn't display any queries. — srchulo, Jun 06 '13 at 23:51
Run the app using bin/_server.pl and take the whole nginx/fastcgi error redirection out of the picture. — RET, Jun 07 '13 at 00:47

score 2 · Accepted Answer · answered Jun 07 '13 at 06:18

2

The answer is no: it will not execute a new query each time you call next, as long as you are working with the same resultset instance. It is intended to be a fast way of iterating the records.

answered Jun 07 '13 at 06:18

stevenl

6,736
26
33

great! That's the answer I was hoping for :) Thanks! – srchulo Jun 07 '13 at 20:37

score 0 · Answer 2 · answered Jun 07 '13 at 17:18

Search returns a ResultSet object in scalar and a list of Result objects in list context. You can force returning a ResultSet by using search_rs.

Next will use cursors if your database supports them but only fetches one row per call and constructs the Result object from it.

If the number of rows is low, doing a single SQL query and constructing all Result objects at once is faster. That's usually the case when you paginate.

Next makes sense when the amount of memory to construct all Results objects would be too large, for example when exporting millions of rows.

DBIx::Class::ResultSet iterate over items new query every time

2 Answers2