Thread: Why is UseDeclareFetch so slow?

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>?
2) When should UseServerSidePrepare be used?
3) When should UseDeclareFetch be used?

Rainer

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

Hello Hiroshi,

>> 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.

Yes, but the real question is why is the query 4 times slower when
UseDeclareFetch is enabled (FETCH was set to 100)?

I would have expected that the ODBC driver is fetching 100 rows in advance and
thus the query should execute in less than a second instead of 30 seconds.

Rainer

Rainer Bauer wrote:
> Hello Hiroshi,
>
>>> 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.
>
> Yes, but the real question is why is the query 4 times slower when
> UseDeclareFetch is enabled (FETCH was set to 100)?
>
> I would have expected that the ODBC driver is fetching 100 rows in advance and
> thus the query should execute in less than a second instead of 30 seconds.

Could you send me directly the Mylog output ?

regards,
HIroshi Inoue

It has been my experience, which is largely with Access as a client,
already attempts to perform similar optimizations to my queries.
Often Access will take a query like:

SELECT * FROM PersonTbl;

and turn it into:

SELECT * FROM PersonTbl WHERE "PersonID" = 10566 OR "PersonID" = 10568
OR "PersonID" = 10365 OR "PersonID" = 10705 OR "PersonID" = 10390 OR
"PersonID" = 10391 OR "PersonID" = 10392 OR "PersonID" = 10447 OR
"PersonID" = 10403 OR "PersonID" = 10414;

> Yes, but the real question is why is the query 4 times slower when
> UseDeclareFetch is enabled (FETCH was set to 100)?
>
> I would have expected that the ODBC driver is fetching 100 rows in advance and
> thus the query should execute in less than a second instead of 30 seconds.
>
> Rainer
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>