Date: Sat, 16 Oct 1999 21:02:15 +0200
From: Ryszard Kurek <rychu@sky.pl>
To: pgsql-interfaces@postgresql.org
Subject: Table viewer for big sorted tables
Message-ID: <19991016210215.A4354@fnet.pl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0pre2i


Hi all!

I want to write a PostgreSQL based program in C++ on Linux system, 
but I have a problem:

For example, user must fill up the form. 
One of the fields is product_symbol that is a key in another table (indexed).

I want user to select the correct product_symbol from list, that 
show product_symbol and corresponding fields from products table
in sorted way. 
In other words, I want to write table viewer thet allow user  to walk on 
entire table using cursor keys, Pgup, PgDown and Home/End.

I first thought, that scrollable cursor would be good. I was wrong, 
because first fetch from cursor took long time if table 
had big number of rows. Cursors are also insensitive :(
and re-opening would take long time again...

I'm thinking about sequentially reads from table with 
select statement with where clause. 
BUT I don't know how to fast obtain the next (in order) value of product_symbol
(next to current value)

The slow version is:
SELECT min(product_symbol) FROM products WHERE product_name > 'current_product';

This take about 1 second in table with 100 000 records (2 columns only).
If I want to show 20 rows at the same time on screen - this take 20 seconds :(

Any suggestions ???

TIA

-- 
Ryszard Kurek
rychu@sky.pl