Thread: Table viewer for big sorted tables

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

Ryszard Kurek <rychu@sky.pl> writes:
> The slow version is:
> SELECT min(product_symbol) FROM products WHERE product_name > 'current_product';

Try

SELECT ... FROM products WHERE product_name > 'current_product' ORDER BY product_name LIMIT 1;

You will need to have an index on product_name to make this fast ---
without the index there will be a sort step.

The ideal solution to your problem is probably

SELECT ... FROM products ORDER BY product_name LIMIT 1 OFFSET n;

which avoids issues like what happens when there is more than one row
with the same product_name.  However, 6.5.* is not bright enough to
use an index for this kind of query (it only considers an index if
there is a matching WHERE clause).  7.0 will be smarter.
        regards, tom lane

On Sat, Oct 16, 1999 at 04:20:30PM -0400, Tom Lane wrote:
> Ryszard Kurek <rychu@sky.pl> writes:
> > The slow version is:
> > SELECT min(product_symbol) FROM products WHERE product_name > 'current_product';
> 
> Try
> 
> SELECT ... FROM products WHERE product_name > 'current_product' ORDER BY product_name LIMIT 1;

I've tried several combination but not this particual one...

IT WORKing now !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1

thank you very much, you saved my life :)

-- 
pozdrowienia, Ryszard Kurek                                   UIN: 1741033
mailto:rychu@sky.pl  * sms: 501128171@sms.centertel.pl * www.fnet.pl/rychu