[Dbix-class] paging question

[James Marca]

On Tue, Feb 02, 2010 at 11:14:27AM -0800, James Marca wrote:
> Hi.  
> 
> First DBIx::Class is excellent and kudos all around.
> 
...

> 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.  

More details.  I stuck in a line to print the timestamp at the
beginning and end of the query.  The queries for page 1 and page 2
both took around 4 hours to complete.  While it could be that I'm just
out of luck here, I'd appreciate some advice from anybody who knows
the code well as to whether or not manually setting up a prepared
statement is worth it.

>From what I read in the code and from reading the docs, I see that
DBIx::Class caches statements, but I'm not sure whether or not that is
the same thing as reusing a prepared statement with different
parameters.

Regards,
James

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