[Maypole] no use of LIMIT in sql queries

[Kevin]

My apologies for the fact that this isn't a specifically maypole problem,  
however, I don't know where else to go.

When viewing the demos, I saw the retrieve_all() call for the list and  
said in horror "of course, I can configure that to use a limit 10,20 to  
the query and paging so that it doesn't destroy the computer when a table  
is viewed with 100,000+ rows.

But it doesn't appear to be possible.

Class::DBI lets you write your own sql functions, so it might be possible  
somewhat by hacking through that, but it's not supported by  
Class::DBI::Pager.  Nor is it supported by DBIx::Abstract.  Or  
SQL::Abstract (what Class::DBI::Abstract uses) or DBIx::Recordset!

For example, in Class::DBI::Query:
sub where {
	my ($self, $type, @cols) = @_;
	my @where = $self->where_clause;
	my $last = pop @where || "";
	$last .= join " AND ", $self->restrictions;
	$last .= " ORDER BY " . $self->order_by if $self->order_by;
	push @where, $last;
	return @where;
}


So now I find myself wondering: am I the only person using limits?  Do we  
really not need to use limits, and rather just fetch the rows used from  
the queries?

But some simple benchmarks tell me that's not at all the case.  (Mysql is  
the only server I'm testing against though.  Perhaps others work better).

use Benchmark;
use DBI;
my $dbh = DBI->connect(...);
timethese(150, { 'iterate'    => \&iterate, 'all' => \&all});

sub iterate {
	my $sth = $dbh->prepare("Select * from ims where mid = 1");
	$sth->execute || die "Couldn't execute sth in basic";
	
	my $r = $sth->fetchrow_hashref();
	my $r = $sth->fetchrow_hashref();
	my $r = $sth->fetchrow_hashref();
}

sub all {
	my $sth = $dbh->prepare("Select * from ims where mid = 1 limit 100");
	$sth->execute || die "Couldn't execute sth in basic";
	
	my $res = $sth->fetchall_arrayref({});
}
all:  5 wallclock secs ( 0.33 usr +  0.02 sys =  0.35 CPU) @ 428.57/s  
(n=150)
             (warning: too few iterations for a reliable count)
iterate: 34 wallclock secs ( 0.33 usr +  0.47 sys =  0.80 CPU) @ 187.50/s  
(n=150)

Any idea what I'm missing here?  Why do no perl database modules support  
the use of limits in select queries?

Over the last few years I've optimized a heck of a lot of web-based code  
to the T, and much of that has involved proper use of LIMIT.  Now I'm  
boggled wondering what everyone else does.

-- 
Ciao,
Kevin
Allpoetry.com Community Manager
"Say the word before the rain comes"