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

Jesus,

Using cursors and fetches should work, and you shouldn't be getting out
of memory errors.  Can you submit a test case that reproduces the out of
memory problem so that we can look into this bug?

thanks,
--Barry

PS. I am assuming you have tried the latest version from
jdbc.postgresql.org to be sure that this isn't already fixed in the
latest production driver from 7.3 (which will run fine against a 7.2
database).

Jesus Sandoval wrote:
> "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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

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

I already did research and the problem about memory and found out that the problem was in my program I was creating too much event objects without any purpose, about 16 event objects (my fault) in the program while doing the search for each row (300,000).

I already solve it and everything is working fine now.

Anyway I think that the server side (a function) will have better performance, I think driver 7.3 is better suited for this job, because the functions now can receive cursors as parameters, do you know where can I find any help in order to do this function????

There have to be a cursor accesible many times in the server (somehow) and the function has to receive this cursor along with another variable with the contents of one field (or maximum 3 fields, in my design I doubt that I have tables with more than 3 fields making the primary key), the function returns a bigint with the row value of the record that matches.

Thanks for your help.

Jesus Sandoval