Re: DBI problems with Pg

yohan tordjman wrote:

> hi,
>
> I'm using postgresql v7.0.2 on Redhat Linux 6.2 with Perl's DBI
> and DBD::Pg ... I've got a table ('annonces'), with 30 columns
> and 100 000 rows .
>
> The following script will work fine when launched on the Pg box;
> but when launched from a remote computer, it will take hours to
> execute, and finally hang the script. Though, we have a 100mbit
> LAN. I've tried to put a limit in my select query, and then i get
> a result -- it still takes a long time, yet.
>
> Have anyone experimented the same behavior ?
> Am i missing some FAQ or something ?
>
> Thanks id advance.
> Yohan Tordjman
>
> My script:
> --------------------------------------cut
> use strict;
> use DBI;
>
> my $dbh = DBI->connect("dbi:Pg:dbname=xxxx;host=xxx", "user", "pass");
> my $query = "SELECT * FROM annonces";
>
> my $sth = $dbh->prepare($query);
> $sth->execute;
>
> while ( my @rows = $sth->fetchrow_array ) {
>
> # statements.....
>
> }
>
> $sth->finish;
> $dbh->disconnect;
> --------------------------------------------cut

Rather simple, you know : fetching your records over a network takes
much longer than processing them on the same computer where
your backend resides. We encountered the same with Oracle, so
I suspect there isn't much that you can do, apart from making sure that
your script ALWAYS executes on the same machine as the backend.

Jurgen Defurne
defurnj(at)glo(dot)be