Thread: 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
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 >
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
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
>
>> 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).
I'm surprised no one has yet suggested using a cursor.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > >> 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). > > I'm surprised no one has yet suggested using a cursor. Which the JDBC driver in CVS now supports. -- Nic Ferrier http://www.tapsellferrier.co.uk
Talking about cursors, please, someone can explain me why I can't use setFetchSize() using the CVS version of the
JDBCdriver? I get the following error. There are no problem in the SQL query I'm sending to the backend, if I don't use
setFecthSize()everything runs fine.
java.sql.SQLException: ERROR: parser: parse error at or near "null" at character 23
at org.postgresql.core.QueryExecutor.executeV2(QueryExecutor.java:286)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:104)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:66)
at org.postgresql.jdbc1.AbstractJdbc1ResultSet.next(AbstractJdbc1ResultSet.java:135)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:135)
On Mon, 04 Aug 2003 10:05:40 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> 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).
>
> I'm surprised no one has yet suggested using a cursor.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
/~\ The ASCII Felipe Schnack (felipes@ritterdosreis.br)
\ / Ribbon Campaign Analista de Sistemas
X Against HTML Cel.: 51-91287530
/ \ Email! Linux Counter #281893
Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter@ritterdosreis.br
Fone: 51-32303341
> 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... David
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.
"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
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) >
Barry Lind escribió:
Jesus,Barry: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,
--BarryPS. 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