Hi,
I'm using PostgreSQL with Apache and mod_perl to keep
persistent connections. Thus far I have been doing
standard selects using DBD::Pg on tables sometimes
returning 1000s of records. This becomes a problem with
the larger queries, as the memory needed by Perl to
receive the result set bloats the Web server processes
horrendously (exceeding 20+ MB per process).
To address this memory issue I've started using cursors to
select the 10 or 20 records that the user will see
at one time. This alleviates the memory issue to some
extent, however this approach creates a new problem: I
can't seem to (easily) determine the total results that
a select statement will yield when using a cursor. The
only way I've managed to get hold of the total results
is by doing the following, which seems absolutely
ludicrous (!):
$dbh = DBI->connect( ...)
$dbh->{AutoCommit} = 0;
$dbh->do("declare c cursor for select * from reports order by id");
$dbh->do("move 10 in c");
--> $dbh->do("select * into table temp from reports where id > 0");
--> my $num_rows = $dbh->do("delete from temp where id > 0");
--> $dbh->do("drop table temp");
my $sth = $dbh->prepare("fetch 20 in c");
$sth->execute();
while ( my $r = $sth->fetchrow_hashref ) { .. }
$dbh->commit();
$sth->finish();
$dbh->disconnect();
Is there any other less expensive way to find out what
$num_rows is going to be for a select statement? Am I
missing something obvious?
I'd be grateful for any help / thoughts.
Cheers.
Patrick
--
#===============================#
\ KAN Design & Publishing Ltd /
/ T: +44 (0)1223 511134 \
\ F: +44 (0)1223 571968 /
/ E: mailto:patrick@kan.co.uk \
\ W: http://www.kan.co.uk /
#===============================#