Re: Server side resultset search (for performance reasons)

Jesus:

One thing you might try (may not be the most optimal solution) is that when the column is selected in JTable for re-sorting, re-issue your query but "order by" that column. You certainly can still use your mechanism by which you limit total records selected.

I would think Postgres would have cached the results from the initial query thus making the re-query fairly quick.

Scot

-------Original Message-------
From: Jesus Sandoval <meli(at)mzt(dot)megared(dot)net(dot)mx>
Sent: 08/02/03 07:23 AM
To: pgsql-jdbc(at)postgresql(dot)org
Subject: [JDBC] Server side resultset search (for performance reasons)

>
> I have a requirement that don't know how to accomplish:

The application is returning 300,000 records in some SELECT clause (a
ResultSet) this finish the client's JVM memory, so my solution to this
was to use the SELECT's LIMIT clause returning 600 records and making
the internal logic that when the user gets past the 600th record the
application automatically issues another query with LIMIT and OFFSET
clauses causing a little delay but this is better than finishing the
memory in the client's machine (if you know a better solution or have
some advices, please let me know).

My request here is, that if the user makes the record 250,000 the
current record, because in the grphical interface (a JTable by the way),

and selects to ORDER BY a different column, the record number 250,000
will be completely a different record, and the application have to point

to the same record before the order by.
Because the JTable is always showing a postgres table, and my postgres
tables always have a primary key, I can identify (by means of the
primary key) one record, I can get the Primary Key of the actual record,

and after the ORDER BY, I can search (secuentially in the resultset) the

record pointer (this is actually the row index in the rresultset) until
the PK matches.

Before I change to the LIMIT version of my SELECTs, this was no problem,

the search was in memory within the entire ResultSet, but this is
prohibitive in very large resultsets, because of the memory constraints.

I have looked that in the server side the query is made very
efficiently, because the response time for the first 600 records is very

fast, ( the last 600 records are no as fasts as the firsts).

So I was trying to find some way to make the search of the record index
in the server side if possible, because actually, I make the search on
the client side, but it is very slow, the client gets 600 records, make
the search and if the record is not found gets another 600 records
(other SELECT clause) and searches again, and if not found, gets another

600 records...... and so on, making this process very slow.

Some ideas that came to me reading the docs, are a Stored Procedure that

can reach the resultset produced in the server side, that receives the
target record primary key, and makes the search returning -1 or the
record index.

Other idea is send with the SELECT a function like
"SELECT *, getIndex(currRecordPK) FROM mytable WHERE mycondition ORDER
BY micolumn"

in this case getIndex is a function that compares the Primary Key of the

current record with the one in the parameter (I have never made a
Postgresql function so maybe the way I say this works is wrong), and put

the index of the record that matches (if in the resultset is the record)

in somewhere that I can retrieve to make the current Record of the new
ResultSet.

I also saw in the mailing list something like a patch from Barry Lind
and Nic Ferrier titled: "Getting a ResultSet for a refcursor element"
that sounds near, but I checked the docs for the JDBC driver and nothing
is said about this.

I hope someone can help me further in this.

Thanks in advance

Jesus Sandoval

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
>