pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedlength of recordset read through a cursor
5+ messages / 4 participants
[nested] [flat]
* length of recordset read through a cursor
@ 2003-07-29 14:10 Knut P. Lehre <k.p.lehre@tiscali.no>
0 siblings, 0 replies; 5+ messages in thread
From: Knut P. Lehre @ 2003-07-29 14:10 UTC (permalink / raw)
To: pgsql-sql
After declaring a cursor, one way of obtaining the length of the resultset
is to perform a "MOVE 0" and read the PQcmdStatus which returns a "MOVE nn"
where nn is the length of the resultset. (A negative MOVE can then be used
to allow starting to fetch records from the beginning of the resultset.)
Is there another, possibly faster way?
Thanks, K.P.Lehre
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: length of recordset read through a cursor
@ 2003-08-01 11:25 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 5+ messages in thread
From: Christoph Haller @ 2003-08-01 11:25 UTC (permalink / raw)
To: pgsql-sql; k.p.lehre@tiscali.no
>
> After declaring a cursor, one way of obtaining the length of the
resultset
> is to perform a "MOVE 0" and read the PQcmdStatus which returns a
"MOVE nn"
> where nn is the length of the resultset. (A negative MOVE can then be
used
> to allow starting to fetch records from the beginning of the
resultset.)
>
> Is there another, possibly faster way?
>
Looks like you're using libpq (because you mention PQcmdStatus),
then after declaring a cursor and FETCH ALL, try
1.3.4. Retrieving SELECT Result Information
PQntuples Returns the number of tuples (rows) in the query result.
int PQntuples(const PGresult *res);
I'm not exactly sure what you're trying to achieve or going to do,
so if I misunderstood you, ask again.
Regards, Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: length of recordset read through a cursor
@ 2003-08-05 11:13 Knut P. Lehre <k.p.d.lehre@basalmed.uio.no>
0 siblings, 1 reply; 5+ messages in thread
From: Knut P. Lehre @ 2003-08-05 11:13 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, Christoph Haller <ch@rodos.fzk.de>
>> After declaring a cursor, one way of obtaining the length of the
>resultset
>> is to perform a "MOVE 0" and read the PQcmdStatus which returns a
>"MOVE nn"
>> where nn is the length of the resultset. (A negative MOVE can then be
>used
>> to allow starting to fetch records from the beginning of the
>resultset.)
>>
>> Is there another, possibly faster way?
>>
>Looks like you're using libpq (because you mention PQcmdStatus),
>then after declaring a cursor and FETCH ALL, try
>
>1.3.4. Retrieving SELECT Result Information
>
> PQntuples Returns the number of tuples (rows) in the query result.
>
> int PQntuples(const PGresult *res);
>
>I'm not exactly sure what you're trying to achieve or going to do,
>so if I misunderstood you, ask again.
>
>Regards, Christoph
Thanks for your reply.
What I'm trying to do is the following: I want to browse through a view
containing more than 10000 records. To avoid slowing things down too much,
I would like my client program to receive (through the network) only the
records that are to be displayed on the screen. I believe I could do this
by declaring a cursor and then fetching the parts of the resultset I need.
It would be useful to know the size of the resultset immediately after the
cursor has been declared. How do I get this information? I could of course
fetch all of the resultset, but that is what I am trying to avoid.
Shouldn't it be quicker to perform a move through the set than fetching it?
I found that moving zero records results in a move to the end of the
resultset, with a command status returning the number of records moved.
Although I expected this method to take less time than a fetch (does it?),
I was wondering if there might be another way to get the size of the
resultset that can be fetched through the declared cursor.
KP
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: length of recordset read through a cursor
@ 2003-08-05 12:22 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 5+ messages in thread
From: Christoph Haller @ 2003-08-05 12:22 UTC (permalink / raw)
To: pgsql-sql; k.p.d.lehre@basalmed.uio.no
>
> >> After declaring a cursor, one way of obtaining the length of the
> >resultset
> >> is to perform a "MOVE 0" and read the PQcmdStatus which returns a
> >"MOVE nn"
> >> where nn is the length of the resultset. (A negative MOVE can then
be
> >used
> >> to allow starting to fetch records from the beginning of the
> >resultset.)
> >>
> >> Is there another, possibly faster way?
> >>
> >Looks like you're using libpq (because you mention PQcmdStatus),
> >then after declaring a cursor and FETCH ALL, try
> >
> >1.3.4. Retrieving SELECT Result Information
> >
> > PQntuples Returns the number of tuples (rows) in the query
result.
> >
> > int PQntuples(const PGresult *res);
> >
> >I'm not exactly sure what you're trying to achieve or going to do,
> >so if I misunderstood you, ask again.
> >
> >Regards, Christoph
>
> Thanks for your reply.
> What I'm trying to do is the following: I want to browse through a
view
> containing more than 10000 records. To avoid slowing things down too
much,
> I would like my client program to receive (through the network) only
the
> records that are to be displayed on the screen. I believe I could do
this
> by declaring a cursor and then fetching the parts of the resultset I
need.
> It would be useful to know the size of the resultset immediately after
the
> cursor has been declared. How do I get this information? I could of
course
> fetch all of the resultset, but that is what I am trying to avoid.
> Shouldn't it be quicker to perform a move through the set than
fetching it?
> I found that moving zero records results in a move to the end of the
> resultset, with a command status returning the number of records
moved.
> Although I expected this method to take less time than a fetch (does
it?),
> I was wondering if there might be another way to get the size of the
> resultset that can be fetched through the declared cursor.
>
I do not know about the internals of FETCH or MOVE.
To me your first approach looks reasonable. I doubt there is way to
learn
about the size of a resultset simply by declaring a cursor (because
there is
no query taking place yet).
You may like to send your request to the performance list.
Regards, Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: length of recordset read through a cursor
@ 2003-08-06 03:25 Rod Taylor <rbt@rbt.ca>
parent: Knut P. Lehre <k.p.d.lehre@basalmed.uio.no>
0 siblings, 0 replies; 5+ messages in thread
From: Rod Taylor @ 2003-08-06 03:25 UTC (permalink / raw)
To: Knut P. Lehre <k.p.d.lehre@basalmed.uio.no>; +Cc: pgsql-sql@postgresql.org, Christoph Haller <ch@rodos.fzk.de>
Yes, a move takes less time, but can still a significant amount of time.
Do you need to know exactly what to expect? Run ANALYZE recently?
A cheat I've used before is to parse the EXPLAIN (not EXPLAIN ANALYZE)
output for the expected number of records involved. If that number was
less than 2000, I MOVE through them for an exact count -- otherwise
display as approx <explain number>.
In most cases it's within 50% of actuality, sometimes better, but very
few people care. They just want to know whether the information from
their search is within the next screen or two.
On Tue, 2003-08-05 at 07:13, Knut P. Lehre wrote:
> >> After declaring a cursor, one way of obtaining the length of the
> >resultset
> >> is to perform a "MOVE 0" and read the PQcmdStatus which returns a
> >"MOVE nn"
> >> where nn is the length of the resultset. (A negative MOVE can then be
> >used
> >> to allow starting to fetch records from the beginning of the
> >resultset.)
> >>
> >> Is there another, possibly faster way?
> >>
> >Looks like you're using libpq (because you mention PQcmdStatus),
> >then after declaring a cursor and FETCH ALL, try
> >
> >1.3.4. Retrieving SELECT Result Information
> >
> > PQntuples Returns the number of tuples (rows) in the query result.
> >
> > int PQntuples(const PGresult *res);
> >
> >I'm not exactly sure what you're trying to achieve or going to do,
> >so if I misunderstood you, ask again.
> >
> >Regards, Christoph
>
> Thanks for your reply.
> What I'm trying to do is the following: I want to browse through a view
> containing more than 10000 records. To avoid slowing things down too much,
> I would like my client program to receive (through the network) only the
> records that are to be displayed on the screen. I believe I could do this
> by declaring a cursor and then fetching the parts of the resultset I need.
> It would be useful to know the size of the resultset immediately after the
> cursor has been declared. How do I get this information? I could of course
> fetch all of the resultset, but that is what I am trying to avoid.
> Shouldn't it be quicker to perform a move through the set than fetching it?
> I found that moving zero records results in a move to the end of the
> resultset, with a command status returning the number of records moved.
> Although I expected this method to take less time than a fetch (does it?),
> I was wondering if there might be another way to get the size of the
> resultset that can be fetched through the declared cursor.
>
> KP
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-08-06 03:25 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-29 14:10 length of recordset read through a cursor Knut P. Lehre <k.p.lehre@tiscali.no>
2003-08-01 11:25 Re: length of recordset read through a cursor Christoph Haller <ch@rodos.fzk.de>
2003-08-05 11:13 Re: length of recordset read through a cursor Knut P. Lehre <k.p.d.lehre@basalmed.uio.no>
2003-08-06 03:25 ` Rod Taylor <rbt@rbt.ca>
2003-08-05 12:22 Re: length of recordset read through a cursor Christoph Haller <ch@rodos.fzk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox