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

Jesus:

I had a thought about this...

Since you want to keep "pointing" to the current record, why not re-issue the query using the "order by" as I've
mentioned. But...why not place, as part of the where clause, do something regarding the column >= or <= the current
record'scolumn that is being sorted upon?   

Scot
-------Original Message-------
From: Jesus Sandoval <meli@mzt.megared.net.mx>
Sent: 08/02/03 12:56 PM
To:
Subject: Re: [JDBC] Server side resultset search (for performance reasons)

>
> 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 after the sort, even if the
relative position into the resultset is not the same, I'll explain this with a
little example of 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 previously saved 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, then re-query another
1000 (now with offset 2000), and so on.... until I finish the 300,000
records or found the record that matches the saved primary-key.

If I could search (by a server side function) in the resultset and know
about the relative position of the record containing the 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 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@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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match
>