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@xxxxxxxxxx> 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
