The beta of CachedRowSet from JavaSoft provides a mechanism that may be of
value. You can disconnect the DB connection, serialize, and passivate this
object so it is useful for EJB and large result sets.
Joe
-----Original Message-----
From: Randy Stafford [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 18, 2000 4:33 PM
To: [EMAIL PROTECTED]
Subject: Re: Paging large database result sets
Jon-
Yes, I think this is a common (but tough) problem. Your choices seem to be:
1. Hold the ResultSet, Statement, etc. in stateless session bean state so
you can get the next batch of results. Undesirable because it holds onto
RDBMS resources. Plus extra work is required to navigate backwards (you'd
have to cache in stateless session bean state the results you'd already
navigated past).
2. Keep in conversational state the ids of the first and last rows the user
is currently looking at, and re-issue the query every time he navigates.
Undesirable because of extra computation, plus gets complex if there is
sorting & grouping going on, or the table changes out from under the user.
3. Read the whole result set and pre-create the corresponding HTML pages, as
one respondant suggested. Desirablility a function of practicality in your
app.
4. Read the whole result set and cache it somehow. Downside is memory
consumption.
5. Make the user refine his query to yield a smaller result set.
etc., etc. Includes elements of the old space-time tradeoff. :-)
In the FoodSmart example application on the Developer's Guide CD
(http://www.gemstone.com/javasuccess), the way we handle this is to read the
whole result set and cache it in GemStone's PCA. Then we give a
SearchResult entity bean back to the client, which allows random navigation
through the result set. This is an example of the search result as entity
bean pattern - see http://www.c2.com/cgi/wiki?SearchResultAsEntityBean.
Best Regards,
Randy Stafford
Senior Architect
GemStone Professional Services
> -----Original Message-----
> From: Jon Lee [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, April 18, 2000 9:53 AM
> To: [EMAIL PROTECTED]
> Subject: Paging large database result sets
>
> Currently looking at the best way to deal with http requests for result
> sets obtained from an SQL (DB2 AS400) database.
> I am using a stateful session bean to access this database.
> I am sure this must be a common issue.
> For example, a request effectively asks for a 500 row table to be
> returned.
> I want to return 50 of these rows in a response to the user, and then give
> them the option of returning the next 50 or previous 50, etc.
>
> a) I can limit the number of rows being returned using setMaxSize() on my
> prepared statement, but I am then unsure of how I can obtain the next set
> of records, when requested, as JDBC does not seem to allow me to do this
> (anyone know anything about this?)
>
> b) I can bring back the whole 500 row result set, store it on the
> StatefulSB as a cachedresultset(?) or as a Collection of some kind.
> Hopefully weblogic's container would effectively manage the memory use
> that this would involve, but I would save myself trips to the database
> (offsite).
>
> This must have been solved many times before, what do people think?
>
> ==========================================================================
> =
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the
> body
> of the message "signoff EJB-INTEREST". For general help, send email to
> [EMAIL PROTECTED] and include in the body of the message "help".
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
