Re: Server side resultset search (for performance reasons)

Barry Lind escribió:

> 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(at)postgresql(dot)org)
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

Barry:

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