Обсуждение: 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 >