pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedHandling data in libpq
5+ messages / 3 participants
[nested] [flat]
* Handling data in libpq
@ 2002-10-19 10:23 am@fx.ro
2002-10-19 11:01 ` Re: Handling data in libpq Jeroen T. Vermeulen <jtv@xs4all.nl>
0 siblings, 1 reply; 5+ messages in thread
From: am@fx.ro @ 2002-10-19 10:23 UTC (permalink / raw)
To: pgsql-interfaces
Hello everyone!
I have written a C program that:
- displays in a tabular form the data in a table
- allows inserts,deletes,updates, and user-defined filters('enter a query')
- uses libpq and ncurses.
The program is rather inefficient right now :
- In order to fetch the data, i call:
RecordSet = PQexec(conn,"select FIELD1,...,FIELDn,OID from TBL");
nRecords = PQntuples(RecordSet);
- when i need a specific tuple, i call :
PQgetvalue(RecordSet,crtRecord,field); for each field
I'm looking for a new approach regarding fetching the data.
Usage of cursors is probably the path to follow. I would be deeply
grateful to anyone who would share a bit of his/her experience:
- i assume the table has 50000 tuples
- what is better: to fetch one tuple at one time, or to fetch
a certain number of tuples (let's say 100).
- if i fetch 100 tuples, PQntuples returns 100? how do i know how
many rows has the table?
- i have the impression that keeping track of the tuple's index i
nside the RecordSet and the index inside the whole table is quite
tricky, but manageable. How do you guys handle this kind of situations?
- are there more examples that use cursors, except the ones in
docs ( in the libpq chapter )?
Thanks,
Adrian Maier
(am@fx.ro)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Handling data in libpq
2002-10-19 10:23 Handling data in libpq am@fx.ro
@ 2002-10-19 11:01 ` Jeroen T. Vermeulen <jtv@xs4all.nl>
2002-10-19 15:47 ` Re: Handling data in libpq am@fx.ro
0 siblings, 1 reply; 5+ messages in thread
From: Jeroen T. Vermeulen @ 2002-10-19 11:01 UTC (permalink / raw)
To: am@fx.ro; +Cc: pgsql-interfaces
On Sat, Oct 19, 2002 at 01:23:52PM +0300, am@fx.ro wrote:
> I'm looking for a new approach regarding fetching the data.
> Usage of cursors is probably the path to follow. I would be deeply
> grateful to anyone who would share a bit of his/her experience:
>
> - i assume the table has 50000 tuples
> - what is better: to fetch one tuple at one time, or to fetch
> a certain number of tuples (let's say 100).
Obviously there is some query and communications overhead associated with
fetching anything, regardless of size, and the odds are it's going to be
larger than the cost of sending a tuple of actual data across. So if
you're going to pay that overhead anyway, you'll want to get some decent
amount of data for it.
However, that also implies that fetching 500 blocks of 100 tuples each is
always going to be slower than getting 50000 tuples in one go. The two
advantages of fetching smaller blocks are: (1) you can start processing
some data right away, which might be useful for e.g. user interaction
(or you can use asynchronous communication so you can process your last
block of data while fetching the next); and (2) you may be running out of
memory when fetching and processing 50000 tuples at once. Swapping can
be a real drag on performance.
> - if i fetch 100 tuples, PQntuples returns 100? how do i know how
> many rows has the table?
Well, if you assume your table has 50000 rows, why do you need to ask?
Seriously though, you don't get this information. But if an estimate
is good enough, you can "select count(*) from table" before you start
fetching data.
> - i have the impression that keeping track of the tuple's index i
> nside the RecordSet and the index inside the whole table is quite
> tricky, but manageable. How do you guys handle this kind of situations?
It's not so hard. Just an extra nested loop.
Jeroen
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Handling data in libpq
2002-10-19 10:23 Handling data in libpq am@fx.ro
2002-10-19 11:01 ` Re: Handling data in libpq Jeroen T. Vermeulen <jtv@xs4all.nl>
@ 2002-10-19 15:47 ` am@fx.ro
2002-10-19 14:48 ` Re: Handling data in libpq John L. Turner <jlt@wvinter.net>
0 siblings, 1 reply; 5+ messages in thread
From: am@fx.ro @ 2002-10-19 15:47 UTC (permalink / raw)
To: pgsql-interfaces
First of all, thanks for your answer.
On Sat, Oct 19, 2002 at 01:01:36PM +0200, Jeroen T. Vermeulen wrote:
> On Sat, Oct 19, 2002 at 01:23:52PM +0300, am@fx.ro wrote:
>
> > I'm looking for a new approach regarding fetching the data.
> > Usage of cursors is probably the path to follow. I would be deeply
> > grateful to anyone who would share a bit of his/her experience:
> >
> > - i assume the table has 50000 tuples
> > - what is better: to fetch one tuple at one time, or to fetch
> > a certain number of tuples (let's say 100).
>
> Obviously there is some query and communications overhead associated with
> fetching anything, regardless of size, and the odds are it's going to be
> larger than the cost of sending a tuple of actual data across. So if
> you're going to pay that overhead anyway, you'll want to get some decent
> amount of data for it.
Hmm.. Good point.
So, you're saying that fetching one tuple at one time is out of discussion.
> However, that also implies that fetching 500 blocks of 100 tuples each is
> always going to be slower than getting 50000 tuples in one go. The two
> advantages of fetching smaller blocks are: (1) you can start processing
> some data right away, which might be useful for e.g. user interaction
> (or you can use asynchronous communication so you can process your last
> block of data while fetching the next);
Well, the program doesn't do automatically data processing:
it only allows the user do see and modify the data.
>and (2) you may be running out of
> memory when fetching and processing 50000 tuples at once. Swapping can
> be a real drag on performance.
There is also another aspect: usually the user will not
browse all of those tuples ( the program allows the user to specify
custom WHERE and ORDER BY clauses, so that he/she can select only
the interesting rows ; and only sometimes will scroll all tuples ).
The conclusion is that the number of rows fetched at one time
should be chosen carefully.
> > - if i fetch 100 tuples, PQntuples returns 100? how do i know how
> > many rows has the table?
>
> Well, if you assume your table has 50000 rows, why do you need to ask?
This assumption is only for that table. I want that my program
works well even for larger tables ( most of the program's classes
and functions are independent from the actual table, and will be
moved to a library for later use).
> Seriously though, you don't get this information. But if an estimate
> is good enough, you can "select count(*) from table" before you start
> fetching data.
I see: the returned number is exact as long as another user doesn't
add/delete tuples after the 'select count(*)' and before the fetch.
I thought there was a way to find out how many tuples would return
a FETCH ALL, without executing that command.
> > - i have the impression that keeping track of the tuple's index i
> > nside the RecordSet and the index inside the whole table is quite
> > tricky, but manageable. How do you guys handle this kind of situations?
>
> It's not so hard. Just an extra nested loop.
Adrian Maier
(am@fx.ro)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Handling data in libpq
2002-10-19 10:23 Handling data in libpq am@fx.ro
2002-10-19 11:01 ` Re: Handling data in libpq Jeroen T. Vermeulen <jtv@xs4all.nl>
2002-10-19 15:47 ` Re: Handling data in libpq am@fx.ro
@ 2002-10-19 14:48 ` John L. Turner <jlt@wvinter.net>
2002-10-19 20:12 ` Re: Handling data in libpq am@fx.ro
0 siblings, 1 reply; 5+ messages in thread
From: John L. Turner @ 2002-10-19 14:48 UTC (permalink / raw)
To: am@fx.ro; pgsql-interfaces; +Cc: Jeroen T. Vermeulen <jtv@xs4all.nl>
On Saturday 19 October 2002 11:47, am@fx.ro wrote:
> First of all, thanks for your answer.
>
> On Sat, Oct 19, 2002 at 01:01:36PM +0200, Jeroen T. Vermeulen wrote:
> > On Sat, Oct 19, 2002 at 01:23:52PM +0300, am@fx.ro wrote:
> > > I'm looking for a new approach regarding fetching the data.
> > > Usage of cursors is probably the path to follow. I would be deeply
> > > grateful to anyone who would share a bit of his/her experience:
> > >
> > > - i assume the table has 50000 tuples
> > > - what is better: to fetch one tuple at one time, or to fetch
> > > a certain number of tuples (let's say 100).
> >
> > Obviously there is some query and communications overhead associated with
> > fetching anything, regardless of size, and the odds are it's going to be
> > larger than the cost of sending a tuple of actual data across. So if
> > you're going to pay that overhead anyway, you'll want to get some decent
> > amount of data for it.
>
> Hmm.. Good point.
> So, you're saying that fetching one tuple at one time is out of discussion.
>
> > However, that also implies that fetching 500 blocks of 100 tuples each is
> > always going to be slower than getting 50000 tuples in one go. The two
> > advantages of fetching smaller blocks are: (1) you can start processing
> > some data right away, which might be useful for e.g. user interaction
> > (or you can use asynchronous communication so you can process your last
> > block of data while fetching the next);
>
> Well, the program doesn't do automatically data processing:
> it only allows the user do see and modify the data.
>
> >and (2) you may be running out of
> > memory when fetching and processing 50000 tuples at once. Swapping can
> > be a real drag on performance.
>
> There is also another aspect: usually the user will not
> browse all of those tuples ( the program allows the user to specify
> custom WHERE and ORDER BY clauses, so that he/she can select only
> the interesting rows ; and only sometimes will scroll all tuples ).
>
> The conclusion is that the number of rows fetched at one time
> should be chosen carefully.
>
> > > - if i fetch 100 tuples, PQntuples returns 100? how do i know how
> > > many rows has the table?
> >
> > Well, if you assume your table has 50000 rows, why do you need to ask?
>
> This assumption is only for that table. I want that my program
> works well even for larger tables ( most of the program's classes
> and functions are independent from the actual table, and will be
> moved to a library for later use).
>
> > Seriously though, you don't get this information. But if an estimate
> > is good enough, you can "select count(*) from table" before you start
> > fetching data.
>
> I see: the returned number is exact as long as another user doesn't
> add/delete tuples after the 'select count(*)' and before the fetch.
>
> I thought there was a way to find out how many tuples would return
> a FETCH ALL, without executing that command.
>
> > > - i have the impression that keeping track of the tuple's index i
> > > nside the RecordSet and the index inside the whole table is quite
> > > tricky, but manageable. How do you guys handle this kind of
> > > situations?
> >
> > It's not so hard. Just an extra nested loop.
>
> Adrian Maier
Another way to look at it, is to create a TreeView, showing only part of the
data
We do that for both our Customer's and Products Tables
First, select all the First Letters in a Customers Table's Last Name Field
Show the User That list of Characters [ A ... Z ]
Then when the users Clicks (Selects) the Character, only those Last Names With
the Selected Character are fetched, and added to Treeview display
Ditto for Products, only they are Grouped by a Category, then any number of
Sub Categories, until the small group of products are shown
After the TreeView item is selected, the focus is changed to the second page
of a Tab Form to allow each field to be edited
--
John Turner
JCI Inc.
http://home.ntelos.net/~JLT
"Just because you do not know the answer
does not mean that someone else does"
Stephen J. Gould, {rip}
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Handling data in libpq
2002-10-19 10:23 Handling data in libpq am@fx.ro
2002-10-19 11:01 ` Re: Handling data in libpq Jeroen T. Vermeulen <jtv@xs4all.nl>
2002-10-19 15:47 ` Re: Handling data in libpq am@fx.ro
2002-10-19 14:48 ` Re: Handling data in libpq John L. Turner <jlt@wvinter.net>
@ 2002-10-19 20:12 ` am@fx.ro
0 siblings, 0 replies; 5+ messages in thread
From: am@fx.ro @ 2002-10-19 20:12 UTC (permalink / raw)
To: pgsql-interfaces
On Sat, Oct 19, 2002 at 10:48:04AM -0400, John L. Turner wrote:
> Another way to look at it, is to create a TreeView, showing only part of the
> data
>
> We do that for both our Customer's and Products Tables
> First, select all the First Letters in a Customers Table's Last Name Field
> Show the User That list of Characters [ A ... Z ]
>
>Then when the users Clicks (Selects) the Character, only those Last Names With
> the Selected Character are fetched, and added to Treeview display
> Ditto for Products, only they are Grouped by a Category, then any number of
> Sub Categories, until the small group of products are shown
>
> After the TreeView item is selected, the focus is changed to the second page
> of a Tab Form to allow each field to be edited
This seems to be a useful strategy. But my program is ncurses-based ,
so i have no TreeViews. I hope i'll find a way to adapt somehow
your idea of selecting first a group of tuples based on some criteria
like the first letter.
Thanks,
Adrian Maier
(am@fx.ro)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-10-19 20:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-19 10:23 Handling data in libpq am@fx.ro
2002-10-19 11:01 ` Jeroen T. Vermeulen <jtv@xs4all.nl>
2002-10-19 15:47 ` am@fx.ro
2002-10-19 14:48 ` John L. Turner <jlt@wvinter.net>
2002-10-19 20:12 ` am@fx.ro
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