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

"scott.marlowe" escribió:

> On Mon, 4 Aug 2003, David Wall wrote:
>
> > > I'm surprised no one has yet suggested using a cursor.
> >
> > Some people don't use cursors because creating and using them requires
> > db-specific code inside their otherwise vanilla JDBC.  Of course, using
> > LIMIT/OFFSET is also db-specific...
>
> Actually, cursors are sql spec, i.e. any SQL92 database that supports
> transactions likely supports cursors, at least static ones.
>
> limit/offset, like you mention, are not spec in sql 92.  They looked like
> they might have been considered, as they are in the older versions listed
> as reserved keywords though.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

Well, I'm here again.

After reading the docs, and the jdbc mail-list I found somebody with kind of
same needs, but with a fatal fate as you can read at
http://forum.java.sun.com/thread.jsp?thread=342693&forum=48&message=1416972
Maybe he has to change Database, he mentions SapDB. But I don't want to
change DBMS, I saw the cursors before trying LIMIT and OFFSET, but didn't
understood exactly its use and how to use them.

But in the book Practical PostgreSQL points that Cursors are better than
LIMIT and OFFSET because I don't have to re-execute the query.

So I changed my logic in order to work with cursors, this seems to solve the
problems, but.... I got an OutOfMemoryError.

I have a secuential search, in the client (as I explain before the purpose
is to find the record position of the
record with the primary key value equal to what I saved before).
In order to do this search, I declare a second cursor with SELECT primarykey
FROM table WHERE condition. (the data select is SELECT * FROM table WHERE
condition).

When I go to the procedure (method in Java) where I search for the
primary-key value, I make first
myConn.executeUpdate("MOVE -" + getTotalRecords()+1 +" IN cursorkeys");
in order to go to the beginning of the resultset.

after that, I do a for like this:

int myActualOffset = 0;
int myWindowSize = 1000;
int i;
String value;
boolean found = false;
for (i=0; i<getTotalRecords(); i++)
{
    if ( i >= myActualOffset + myWindowSize)        // If the record pointer
is past the actual resultset fetched
    {
        rs.close();        // I put this after noted that memory was being
ated by the application, but is useless
        rs = myStatement.executeQuery("FECTH " + myWindowSize + "
FROM cursorkeys");
    }
    rs.absolute(i-myActualOffset+1);
    value = rs.getString(1);
    if (value.equals(keySaved))
    {
        found = true;
        break;
    }
}
if (found)
    return i;
else
    return -1

It is, take the cursor pointer to the beginning and making a search until
I reach the end of the fetched rows
then fetch more rows and make the search with them, and so on... until found
or the end of the entire cursor.

But the memory is being exhausted by this search, I think that each fetch,
doesn't release the memory before fetch the new rows, the memory use keeps
growing until a OutOfMemoryError occurs in Java.

Can you point me what is wrong with this, please......

I am working with Linux RedHat 7.3 (I'm looking at the memory use with the
"top" command and "M" (sort by memory usage, option), The POstgresql release
is 7.2.3 and the jdbc driver is pgjdbc2.jar.

Thanks in advance...

Jesus Sandoval