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

Thanks for the reply.

Yes, I re-issue my query with re-sorting (this work is better done by the DBMS, than a Java client side implemented
sort,I think). 
But the problem is that the application saves a pointer to the current record, and needs to point to the same record
afterthe sort, even if the relative position into the resultset is not the same, I'll explain this with a little
exampleof 6 records, position 
is the relative record position into the actual resultset:
position    primary key    field
                   content            content
    1                a                        a6                <--- Current record
    2                b                        a5
    3                c                        a7
    4                d                        a8
    5                e                        a3
    6                f                        a9

If the user makes an order by field content the resulting resultset is:
position    primary key    field
                   content            content
    1                e                        a3
    2                b                        a5
    3                a                        a6        <-- new current record
    4                c                        a7
    5                d                        a8
    6                f                        a9

My application is able to do the reposition of the current record when the ResultSets are small,  because
I save the primary-key content, before the re-query, and when I obtain the new query, I do a secuential scan of the
previouslysaved primary key content. 

The problem is with large resultsets (say 300,000 records for example) because I have to requery with limit (say 1000
records)and do a secuential scan there, if not found, re-query another 1000 records (with offset 1000) and scan there,
thenre-query another 
1000 (now with offset 2000), and so on.... until I finish the 300,000 records or found the record that matches the
savedprimary-key. 

If I could search (by a server side function) in the resultset and know about the relative position of the record
containingthe key, I only have to do one query with the calculated offset depending of this information. 

Any ideas?????


"Scot P. Floess" escribió:

> 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
limittotal 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@mzt.megared.net.mx>
> Sent: 08/02/03 07:23 AM
> To: pgsql-jdbc@postgresql.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
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html