Re: Critical performance problems on large databases

At 11:09 AM 4/11/02 -0500, Gunther Schadow wrote:
>There was one remark about Perl or PHP always loading the complete
>result set before returning. Bad for them. I don't use either and
>I think it's just bad design to do that on the client but I don't
>care about bad clients. I care about a good server.

>The constructive responses suggested that I use LIMIT/OFFSET and
>CURSORs. I can see how that could be a workaround the problem, but
>I still believe that something is wrong with the PostgreSQL query
>executer. Loading the entire result set into a buffer without
>need just makes no sense. Good data base engines try to provide

AFAIK you can turn off buffering the whole result set with perl DBI/DBD.

Buffering allows programmers to easily use each row of a query to make
other queries, even if the DB doesn't support cursors.

e.g.
select ID from table1;
for each row returned {
         select ID2 from table2 where x=ID;
                 for each row returned:
                         ....
}

Without buffering or cursors you will have to open another connection or
store all rows somewhere then do the subsequent queries using the stored rows.

Cheerio,
Link.