"McDaniel, Joe R." wrote:
> 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.
No good for *very* big resultsets (100.000+ rows), the serialized CachedRowSet
would be unmanageable and the client PC would collapse.
We know, our client PCs used to have 32 MB and they *did* collapse.
Some of the tables we use have millions or records.
BTW, this CachedRowSet from Sun is lacking a lot of functionality
ADO 2.5 disconnected resultset already has.
From: Randy Stafford [mailto:[EMAIL PROTECTED]]
> 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).
No good as Randy explains.
> 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.
No good as Randy explains.
> 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.
No good for *very* big resultsets.
> 4. Read the whole result set and cache it somehow. Downside is memory
> consumption.
No good for *very* big resultsets.
> 5. Make the user refine his query to yield a smaller result set.
Not practical or user-friendly.
> 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.
No good, this solution if wasting a lot of server resources. The average user
does
not really want to navigate around 100.000 rows, what we provide is something
like this:
+ getCount(String) allows the client to submit a query
and returns a count of how many records fit that query
+ getPage(String,int,int) allows the client to submit a query,
a starting position and a pagesize and returns a page of records
This simple scheme fits nicely with Swing components such as JList, JTable...
A JList model will use "getCount" to answer the "getRowCount" question and then
"getPage" to answer the "getElement" question depending on the user viewing
area,
no need to serialize the whole resultset or keep any state at the server.
The client caches 2-3 pages of data to allow the user for scrolling.
Usually the user makes a general query, sees how many thousands of records
fit the query, then redefines the query until he or she gets the right data.
The client keeps the state, i.e. the client knows what query to perform
(including sorting and grouping etc) and also where to start and how many
records
to get back.
No state at all is kept at the server.
A new query is perfomed when the "getPage" method is remotely invoked,
then a while loop is used to build the selected page, dropping the rows before
the
starting position and breaking after the right number of records have been
packed
into a Vector for the client.
This strategy is poor with JDBC 1.22 because "next" forces to loop until the
start position,
but works best when using JDBC 2.0 navigation like the "absolute" method.
Even better if you can put the JDBC code inside the database, Oracle 8i for
instance.
We deal with those million rows tables just fine, both with Swing and HTML/JSP
clients.
Best regards
Javier Borrajo
www.tid.es
===========================================================================
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".
