Re: Why is UseDeclareFetch so slow?

[Hiroshi Inoue <inoue(at)tpf(dot)co(dot)jp>]

Rainer Bauer wrote:
> Hello,
>
> based on the problem I had here
> <http://archives.postgresql.org/pgsql-performance/2007-06/msg00461.php>
> I thought it would be interesting to know why UseDeclareFetch is slowing my
> query down dramatically.
>
> I have a table containing 50 tuples and retrieve all 50 tuples with a bound
> column select statement:
> SELECT * FROM tblItem WHERE intItemIDCnt = ?
>
> This takes 8 seconds (over a slow DSL connection with 150ms ping). Tom Lane
> suggested enabling UseDeclareFetch and the performance drop was significant:
> Instead of 8 seconds, the query now takes 30-32 seconds. I also tested
> enabling UseServerSidePrepare (without UseDeclareFetch) and the query was
> executed in 7 seconds.
>
> After rewriting the query to use an IN clause it now works perfect:
> SELECT * FROM tblItem WHERE intItemIDCnt IN (...)
> The result is retrieved in 400ms (UseServerSidePrepare=950ms and
> UseDeclareFetch=1242ms).
>
> The only thread I found discussing the UseDeclareFetch/UseServerSidePrepare
> options was <http://archives.postgresql.org/pgsql-odbc/2006-10/msg00051.php>.
>
> So my questions are
> 1) Is there any chance the driver itself could be enhanced like Dimitri
> suggested here:
> <http://archives.postgresql.org/pgsql-performance/2007-06/msg00512.php>?

You can specify the fetch count in UseDeclareFetch mode using the
 Cache Size option or FETCH=N connection string.

> 2) When should UseServerSidePrepare be used?

Basically it should be used for 7.4 or later servers.

> 3) When should UseDeclareFetch be used?

The driver holds results of queries in memory.
So when you would retrieve large results, please use the UseDeclareFetch 
option so as to suppress the memory consumption.

regards,
Hiroshi Inoue