Hello Rainer,

initially I was surprised you did not match non-CURSOR time with FETCH
100, but then thinking little bit the explanation is very simple -
let's analyze what's going in both cases:

Without CURSOR:
1.) app calls PQexec() with "Query" and waiting for the result
2.) PG sends the result to app, data arriving grouped into max
possible big packets, network latency is hidden by huge amount per
single send

With CURSOR and FETCH 100:
1.) app calls PQexec() with "BEGIN" and waiting
2.) PG sends ok
3.) app calls PQexec() with "DECLARE cursor for Query" and waiting
4.) PG sends ok
5.) app calls PQexec() with "FETCH 100" and waiting
6.) PG sends the result of 100 rows to app, data arriving grouped
into max possible big packets, network latency is hidden by huge data
amount per single send
7.) no more data (as you have only 50 rows in output) and app calls
PQexec() with "CLOSE cursor" and waiting
8.) PG sends ok
9.) app calls PQexec() with "COMMIT" and waiting
10.) PG sends ok

as you see the difference is huge, and each step add your network
latency delay. So, with "FETCH 100" we save only cost of steps 5 and 6
(default "FETCH 1" will loop here for all 50 rows adding 50x times
latency delay again). But we cannot solve cost of other steps as they
need to be executed one by one to keep execution logic and clean error
handling...

Hope it's more clear now and at least there is a choice )
As well, if your query result will be 500 (for ex.) I think the
difference will be less important between non-CURSOR and "FETCH 500"
execution...

Rgds,
-Dimitri


On 6/22/07, Rainer Bauer <usenet@munnin.com> wrote:
> Hello Dimitri,
>
> >Rainer, but did you try initial query with FETCH_COUNT equal to 100?...
>
> Yes I tried it with different values and it's like you suspected:
>
> FETCH_COUNT 1 Time: 8642,000 ms
> FETCH_COUNT 5 Time: 2360,000 ms
> FETCH_COUNT 10 Time: 1563,000 ms
> FETCH_COUNT 25 Time: 1329,000 ms
> FETCH_COUNT 50 Time: 1140,000 ms
> FETCH_COUNT 100 Time: 969,000 ms
>
> \unset FETCH_COUNT Time: 390,000 ms
>
> Rainer
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

PFC,

> Correct. I use a pyro-remote server. On request this remote server copies
> > the relevant rows into a temporary table, uses a copy_to Call to push
> > them
> > into a StringIO-Objekt (that's Pythons version of "In Memory File"),
> > serializes that StringIO-Objekt, does a bz2-compression and transfers
> the
> > whole block via VPN.
>

> What about a SSH tunnel using data compression ?
Setup on multiple Windows Workstations in multiple Installations is not
possible.

> If you fetch all rows from a query in one go, would it be fast ?
I tried the same copy_to via VPN. It took 10-50x the time it took locally.

>Also, PG can now COPY from a query, so you don't really need the temp
table...
I know, but was stuck to 8.1 on some servers.

Best wishes,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2007 will take place in Vilnius, Lithuania from Monday 9th July
to Wednesday 11th July. See you there!

Hello Dimitri,

>Hope it's more clear now and at least there is a choice )
>As well, if your query result will be 500 (for ex.) I think the
>difference will be less important between non-CURSOR and "FETCH 500"
>execution...

The problem is that I am using ODBC and not libpq directly.

I will have to rewrite most of the queries and use temporary tables in some
places, but at least I know now what the problem was.

Thanks for your help.

Rainer

Rainer Bauer wrote:
> Hello Dimitri,
>
>> Hope it's more clear now and at least there is a choice )
>> As well, if your query result will be 500 (for ex.) I think the
>> difference will be less important between non-CURSOR and "FETCH 500"
>> execution...
>
> The problem is that I am using ODBC and not libpq directly.

That opens up some questions. What ODBC driver are you using (with exact
version please).

Joshua D. Drake

>
> I will have to rewrite most of the queries and use temporary tables in some
> places, but at least I know now what the problem was.
>
> Thanks for your help.
>
> Rainer
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Hello Joshua,

>That opens up some questions. What ODBC driver are you using (with exact
>version please).

psqlODBC 8.2.4.2 (build locally).

I have restored the 8.2.4.0 from the official msi installer, but the results
are the same.

Rainer

Rainer, seeking psqlODBC code source it seems to work in similar way
and have an option "SQL_ROWSET_SIZE" to execute FETCH query in the
same way as "FETCH_COUNT" in psql. Try to set it to 100 and let's see
if it'll be better...

Rgds,
-Dimitri

On 6/22/07, Rainer Bauer <usenet@munnin.com> wrote:
> Hello Joshua,
>
> >That opens up some questions. What ODBC driver are you using (with exact
> >version please).
>
> psqlODBC 8.2.4.2 (build locally).
>
> I have restored the 8.2.4.0 from the official msi installer, but the results
> are the same.
>
> Rainer
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Hello Dimitri,

>Rainer, seeking psqlODBC code source it seems to work in similar way
>and have an option "SQL_ROWSET_SIZE" to execute FETCH query in the
>same way as "FETCH_COUNT" in psql. Try to set it to 100 and let's see
>if it'll be better...

But that is only for bulk fetching with SQLExtendedFetch() and does not work
for my case with a single bound column where each tuple is retrived
individually by calling SQLFetch().
See <http://msdn2.microsoft.com/en-us/library/ms713591.aspx>

Rainer