"David C. Hartwig Jr" wrote:
> Mark Alliban wrote:
>
> > Hi,
> >
> > I have an application which dials a modem, and connects to Postgres using
> > ODBC. When I call the SQLExecDirect function to get the (quite large: 10000
> > rows) result set, it takes a long time to execute. The query itself is
> > indexed and runs very quickly from psql. I presume the delay is because the
> > ODBC is downloading all the rows. Is there any way to either:
> >
> > 1) Use compression on the recordset to make the call faster?
> > 2) Download each row one at a time, when SQLFetch is called?
> >
> > Also I don't seem to be able to find an ODBC function to return the number
> > of records in a results set. Is there one?
> >
> > TIA,
> > Mark.
>
> Its been a while since I have used the ODBC driver, but I believe if you set
> the Declare/Fetch driver option you will get a better response time. This
> option uses a cursor to bring in the rows in set. I believe the default is
> to cache 100 rows at a time - on demand. Kind of a lazy retrieval thing.
> The side effect of using this option is that as long as that record set is in
> use, you must open another separate connection to perform other database
> operations. Hope this helps.
Correction. You won't need to open another connection unless you plan to do
separate transactions with in the scope of an open query statement.
PostgreSQL requires cursors to be executed within a truncation block. As such
the ODBC driver opens a truncation for all queries when using Declare/Fetch.
And since PostgreSQL does not support nested transactions, additional
transactions may not be invoked within the scope of an open query statement.
