Re: server side resultset search (for performance reasons) - Mailing list pgsql-jdbc

Why don't you count the records where the sort column is smaller/bigger
(depending on sort order) than the current record's sort column ?
Something like:
select count(*) from mytable where mycondition and sortcolumn < crtvalue
(or > in case of descending ordering)
This of course will still possibly leave you multiple records with the
same value of the sort column as the value of the current record, but in
this case you anyway should select all of those records I guess...

HTH,
Csaba.


On Fri, 2003-08-01 at 13:12, Jesus Sandoval wrote:
> 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 hope someone can help me further in this.
>
> Thanks in advance
>
> Jesus Sandoval
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>