Thread: length of recordset read through a cursor

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

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

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

>> After declaring a cursor, one way of obtaining the length of the>resultset>> is to perform a "MOVE 0" and read the
PQcmdStatuswhich returns a>"MOVE nn">> where nn is the length of the resultset. (A negative MOVE can then be>used>> to
allowstarting to fetch records from the beginning of the>resultset.)>>>> Is there another, possibly faster way?>>>Looks
likeyou're using libpq (because you mention PQcmdStatus),>then after declaring a cursor and FETCH ALL, try>>1.3.4.
RetrievingSELECT Result Information>>    PQntuples Returns the number of tuples (rows) in the query result.>>    int
PQntuples(constPGresult *res);>>I'm not exactly sure what you're trying to achieve or going to do,>so if I
misunderstoodyou, 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

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
>