Skip site navigation (1) Skip section navigation (2)

Re: Why is UseDeclareFetch so slow?

From: Hiroshi Inoue <inoue(at)tpf(dot)co(dot)jp>
To: Rainer Bauer <usenet(at)munnin(dot)com>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: Why is UseDeclareFetch so slow?
Date: 2007-06-27 01:12:19
Message-ID: 4681B973.6080101@tpf.co.jp (view raw or flat)
Thread:
Lists: pgsql-odbc
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


In response to

Responses

pgsql-odbc by date

Next:From: Dave PageDate: 2007-06-27 07:46:26
Subject: Re: Proposal for new pgsqlODBC feature - hiding tables inaccessible to the current user
Previous:From: Hiroshi InoueDate: 2007-06-27 00:01:16
Subject: Re: Proposal for new pgsqlODBC feature - hiding tables inaccessible to the current user

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group