Content-Type: text/plain;
  charset="iso-8859-1"
From: "John L. Turner" <jlt@wvinter.net>
Reply-To: jlt@wvinter.net
Organization: JCI Inc.
To: am@fx.ro, pgsql-interfaces@postgresql.org
Subject: Re: Handling data in libpq
Date: Sat, 19 Oct 2002 10:48:04 -0400
User-Agent: KMail/1.4.2
References: <80bf0666.0210170015.59e12eed@posting.google.com>
	<20021019110135.GX11228@xs4all.nl> <20021019184725.A313@coto>
In-Reply-To: <20021019184725.A313@coto>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200210191046.51753.jlt@wvinter.net>
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 wi=
th
> > 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 discussio=
n.
>
> > 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 th=
e=20
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 ]=20

Then when the users Clicks (Selects) the Character, only those Last Names W=
ith=20
the Selected Character are fetched, and added to Treeview display

Ditto for Products, only they are Grouped by a Category, then any number of=
=20=20
Sub Categories, until the small group of products are shown

After the TreeView item is selected, the focus is changed to the second pag=
e=20
of a Tab Form to allow each field to be edited
--=20
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}