Memory issues with large query storage

[Andrew Latham <al...@kanbay.com>]

I hope someone can assist here.

Basically I have been writing a 3 Tier Web app using Tomcat and trying
to adhere to the Struts framework. The application connects to an
existing MSSQL database and creates graphs of the data. The way I've
built it is I define an entity bean to store a row of returned data from
the select query. Each entity bean is appended to a Collection and then
made available to the JSP for me to access using <logic:iterate>. This
all works fine, until the Collection.size() reaches numbers like 600,000
and then I hit memory issues. I know there is not much I can do but
increase -Xmx and page it or buy more physical memory.

The question I have relates to displaying the data. Some of the output I
want in a table. Showing all results returned is not good if its more
than 100, so I created a "google" type <<previous 10, next 10>> to cycle
through them, so for this to happen I need the Collection of what can be
100,000 beans to sit in memory as having to go back and get the next 10
from the SQL database is about 2 minutes each time. When the Collection
is returned it has a scope of "Request". To make this available to
another JSP page (for the next 10) I rescoped the Collection to
"session". This introduces a memory problem where it sits around until
the session ends and the GC picks it up. Is there a better way to pass
the Collection from one JSP to another without re-scoping it or what I
can see happen is that if a user runs many queries they all sit around
until the session ends utilising memory.

I've thought I've setting the Collection to NULL when a new query is
run. Is this an acceptable method?

Appreciate any advice

Thanks

Andrew Latham



*----------
This message and any attachment(s) is intended only for the use of the addressee(s) and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended addressee(s), you are hereby notified that any use, distribution, disclosure or copying of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachment(s) and notify the sender or Kanbay postmaster immediately.

Any views expressed in this message are those of the individual sender and not of Kanbay.

Although we have taken steps to ensure that this e-mail and any attachment(s) are free from any virus, we advise that in keeping with good computing practice the recipient should ensure they are actually virus free.

Re: Memory issues with large query storage

["Lloyd A. Duke" <li...@lloyd-duke.net>]

Saving 600K rows of data in memory for a user is quite extreme.
There are several alternatives here. Why save the entire row?
Typically I see (and do) the saving of id's instead of the entire row. 
Then subsequent calls use the id for the lookup.  Caching can help the 
performance of the trip to the database... although thats kinda what 
your problem is here (not enough memory)....

Paging (your google previous, next) is typically done using only the 
records you display and perhaps the *next* and *previous* records and 
the number span you are viewing. a good example of paging can be found 
in suns petstore sample application (http://java.sun.com/blueprints/code/)

A good look over that should save you lots of pain.

regards,
Lloyd

Andrew Latham wrote:

>I hope someone can assist here.
>
>Basically I have been writing a 3 Tier Web app using Tomcat and trying
>to adhere to the Struts framework. The application connects to an
>existing MSSQL database and creates graphs of the data. The way I've
>built it is I define an entity bean to store a row of returned data from
>the select query. Each entity bean is appended to a Collection and then
>made available to the JSP for me to access using <logic:iterate>. This
>all works fine, until the Collection.size() reaches numbers like 600,000
>and then I hit memory issues. I know there is not much I can do but
>increase -Xmx and page it or buy more physical memory.
>
>The question I have relates to displaying the data. Some of the output I
>want in a table. Showing all results returned is not good if its more
>than 100, so I created a "google" type <<previous 10, next 10>> to cycle
>through them, so for this to happen I need the Collection of what can be
>100,000 beans to sit in memory as having to go back and get the next 10
>from the SQL database is about 2 minutes each time. When the Collection
>is returned it has a scope of "Request". To make this available to
>another JSP page (for the next 10) I rescoped the Collection to
>"session". This introduces a memory problem where it sits around until
>the session ends and the GC picks it up. Is there a better way to pass
>the Collection from one JSP to another without re-scoping it or what I
>can see happen is that if a user runs many queries they all sit around
>until the session ends utilising memory.
>
>I've thought I've setting the Collection to NULL when a new query is
>run. Is this an acceptable method?
>
>Appreciate any advice
>
>Thanks
>
>Andrew Latham
>
>
>
>*----------
>This message and any attachment(s) is intended only for the use of the addressee(s) and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended addressee(s), you are hereby notified that any use, distribution, disclosure or copying of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachment(s) and notify the sender or Kanbay postmaster immediately.
>
>Any views expressed in this message are those of the individual sender and not of Kanbay.
>
>Although we have taken steps to ensure that this e-mail and any attachment(s) are free from any virus, we advise that in keeping with good computing practice the recipient should ensure they are actually virus free.
>
>
>  
>




---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org