Date: Sat, 19 Oct 2002 13:01:36 +0200
From: "Jeroen T. Vermeulen" <jtv@xs4all.nl>
To: am@fx.ro
Cc: pgsql-interfaces@postgresql.org
Subject: Re: Handling data in libpq
Message-ID: <20021019110135.GX11228@xs4all.nl>
Mail-Followup-To: am@fx.ro, pgsql-interfaces@postgresql.org
References: <80bf0666.0210170015.59e12eed@posting.google.com>
	<20021019132352.A419@coto>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20021019132352.A419@coto>
User-Agent: Mutt/1.4i

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