[Dbix-class] paging question

James Marca jmarca at translab.its.uci.edu
Tue Feb 2 19:14:27 GMT 2010


Hi.  

First DBIx::Class is excellent and kudos all around.

I have recently needed to use the paging feature for the first time
and I'm not sure of the best way to do this.

First a simple question:  How should one terminate the paging?  Is
there some variable to check to find out how many pages of data are
expected from a result set?  I couldn't find anything in the docs,
but I probably missed it.

What I am doing instead is just paging until my result set comes back
empty.  But that is a bad solution in practice and gets to my second
issue with paging.

My query is rather large and expensive.  I'm using paging not to drive
a UI, but because when running the request without paging psql
complained that it ran out of disk space for the tmp file it needed
for the cursor (I forget the exact wording of the error).  My page
size is currently set at 4 million rows, which seems fine.

My guess is that under the hood paging is preparing the query, then
executing that prepared statement repeatedly with different offsets.
However, in practice it *appears* (I can't prove this yet so I could
be wrong) that the page(2), page(3), etc queries take just as long to
execute as the first page(1) query, as if they aren't running off of
the same prepared statement.

And because the queries take so long, I really don't want to run that
last page(n+1) query, wait an age, and then  get nothing.  

So, is paging using a prepared, cached statement, or should I jump
down to custom sql statements to optimize as much as I can in my
somewhat extreme situation?

Thanks,
James Marca

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.




More information about the DBIx-Class mailing list