Out of memory error

[Atul Mishra]

Hi all,

My environment is Eclipse+tomcat 5.0[Tomcat is the local tomcat within eclipse]
I have a report query which can produce anywhere from 10000-70000 records.
I dont have any problem displaying data in jsp when the records are 10,000.

I have a record count of 64000 now and while displaying,running th query, the program exists with Out of memory error saying heap size.

java.lang.OutOfMemoryError: Java heap space

Corerct exception looks like this: Why ? is it because of 65000 records ?
How can I increase my heapsize ?
In my program, I have a Statement object and a Resultset rs
then I iterate thru while(rs.next()) -

HTTP Status 500 -

--------------------------------------------------------------------------------

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

javax.servlet.ServletException: Servlet execution threw an exception


root cause

java.lang.OutOfMemoryError: Java heap space


note The full stack trace of the root cause is available in the Apache Tomcat logs.


Thanks,

[Ben Souther]

First, I'm not sure why you would ever want a query to return that many records. Wouldn't you be better off paging at the database level so you only return a useful number of records?

That being said/asked...
Altering the heap size in Tomcat depends on how Tomcat is started and what OS you're using. In your case, you're using an embedded version of Tomcat so this question is really one of Eclipse configuration.

I'm going to move it to our IDE's forum where Eclipse questions go.

-Ben

[Atul Mishra]

ben,

Thank you for your answer.
Do you think paging at database level and thereby returning xxx[say 10,000] records at a time can solve the issue ?

If yes, I can propose that to the clients..
Right now they were talking about al data display in the results page..

Thanks

[Ben Souther]

If the data is just being displayed in the browser 10,000 seems very excessive. What kind of data are you displaying?

Also, is your production app running in an embedded version of Tomcat (in Eclipse)?
[ June 19, 2007: Message edited by: Ben Souther ]

[Atul Mishra]

For your questions:

If the data is just being displayed in the browser 10,000 seems very excessive. What kind of data are you displaying?

Data is production data: This report will be run may be once/twice a day

Also, is your production app running in an embedded version of Tomcat (in Eclipse)?

Ok, regarding production app- No: but this report is a new development and trying in Eclipse+tomcat now. Dont know when we get certified to use servers etc..

Thanks

[Ben Souther]

Originally posted by Atul Mishra:
Data is production data: This report will be run may be once/twice a day

My question had less to do with number of hits or the load on the server as it did the usefulness of any page with so many rows of data.
I can't imagine ever needing to look at 10 thousand rows of anything in one page.

[Atul Mishra]

Yes, but thats what clients request!

Thanks

[Jeanne Boyarsky]

Originally posted by Atul Mishra:
Yes, but thats what clients request!

Unless the client is requesting a download (Excel, tab delimited, etc), the client may not realize how unusable such a large web page is.

You may want to create a mockup of dummy data using that many rows to show an example. Most people want paging in such a scenario.

[Atul Mishra]

Yes, first they want a JSP display and then an Excel download...

Thanks.

[Ben Souther]

Then I would suggest following Jeanne's approach for the JSP end.
I would almost be willing to bet that the customer will prefer paging with a small number of rows in each screen.

For the delimited file, you might not want to use JSP.
If, by using a database cursor, you can stream the delimited text down to the user's machine, you should be able to prevent having to store the entire query result in the application server's memory.

This could be done by either writing the results to a file on the server and then providing a link to it or, (more efficient if the report is only to be run once) stream the contents straight from the cursor to the servlet's output stream.

[Atul Mishra]

Ok, Yesterday I convinced the customers for displaying it as batches[In Jeaane's terms]
But they prefer batches of 5000[for big records.. hah, they say batches of 100 will be tooo many pages for 70000 buly records]

How to query Oracle by batches I am still looking, havent done it yet..

Saying so, I ave a question about this:

For the delimited file, you might not want to use JSP.
If, by using a database cursor, you can stream the delimited text down to the user's machine, you should be able to prevent having to store the entire query result in the application server's memory.

This could be done by either writing the results to a file on the server and then providing a link to it or, (more efficient if the report is only to be run once) stream the contents straight from the cursor to the servlet's output stream.

I have a DAO which runs the query and gets the resultset. Are you saying to write the results of the query[right now its just a SELECT statement] to a file in the server ?

And

Stream the contents straight from cursor to servlets's output stream - Any links for doing this ?

Thanks Ben, appreciate your valuable suggestions

[Ben Souther]

We have a FAQ entry in the JSP FAQ on this.
http://faq.javaranch.com/search?PaginationOrPaging

For Oracle you would use the ROWNUM keyword.
If you need more help with that, you might want to start a new thread the JDBC forum.

[Ben Souther]

Stream the contents straight from cursor to servlets's output stream

I probably should have worded that better.
I didn't mean straight from the cursor to the output stream.

I meant process each row from the cursor (add commas etc...) and then write the results of each to the servlet outputstream.
Doing this should eliminate the need to hold the entire result in memory at once.

I have never done this with Oracle but a Google search seems to turn up a lot:
http://www.google.com/search?hl=en&q=Oracle+cursor+jdbc&btnG=Google+Search

Again, for better help with this, you might want to start a new thread in the JDBC forum.


Also, I just went back and re-read your first post which says 'query'.
I'm not sure why but I had it in my head that you were using stored procedures.

You should be able to do the same thing with a ResultSet.
[ June 21, 2007: Message edited by: Ben Souther ]

[Atul Mishra]

Thanks Ben for the pointers.

I got two articles from Devx which talks about this. Trying to understand it and implement it for our JSP.

Thanks again, I am not using Stored procedures. I am using simgle select statement and a Statement object[not a Prepared stataement object too..]