An approach I use is the following:
- Limit how many rows you return on any query. If the query returns more
rows than your limit, then suggest a more limiting query constraint. I
limit to 1000 rows.
- Limit the number of rows you display per page. Realistically, displaying
too many rows in an HTML page, especially if you use HTML TABLEs to format
the output, will severely impact performance and memory on the client's
desktop. Too many rows and the data takes overly long to render. Some
people don't have enough memory to display very large pages. So I limit to
50 rows per page, sometimes making the page size setting a user preference.
Of course it all depends on how much data/HTML you return per row.
- I store the keys from my resultset with the session object, and from
next/prev request to next/prev request, I access the appropriate keys
instead of re-running the query (via a SELECT ... IN ... query). This
reduces the storage per user, and it produces faster paging. It also means
that the user prev/next pages through the resultset of the original query,
and will not pick up new rows that meet that original query until a brand
new original query is performed. This is a tradeoff we can live with.
- I've established a cursor object that keeps track of my position so I can
correctly access the appropriate stored keys as I prev/next/first/last move
through my result data.
So an example of the above might be a query that returns 800 rows. Here is
basically what I do (note: I do this stuff in a servlet, because that's the
way I architect my JSP systems - JSP>Servlet>JSP):
- store the keys for all 800 rows in a Vector I store with the session,
- store the first 50 rows of data from the same query resultset in an object
I pass to my web page for immediate display,
- hook my cursor object to the key Vector,
- display the page.
If the use selects to move to the next page of the results I:
- use my cursor object to determine the next 50 keys from my key Vector and
create a SELECT FROM ... WHERE ... IN (key51, key52, ..., key100) and
execute the query,
- update my cursor
- store the rows of data from the key query in an object I pass to my web
page for immediate display,
- display the page.
And so on. I can bounce around my results as I wish this way, forward,
backward, first, last, jump to a specific starting row, etc...
I probably spent about a month of my time developing the classes I use to do
all this before I began developing my first JSP project, and I've used them
ever since (for the past 14-15 months). My data objects are generic and
store all the resultset meta data I need (type, length, nullable, name), so
I don't have to create new data beans for every data view I have.
HTH,
Dan
--
Daniel Kirkdorffer
NACN IS: 425-580-6225
Sr. Consultant, Syllogistics LLC
Email: [EMAIL PROTECTED]
Web: http://www.syllogistics.com/
> ----------
> From: Ian[SMTP:[EMAIL PROTECTED]]
> Reply To: Ian
> Sent: Monday, November 15, 1999 10:13 AM
> To: [EMAIL PROTECTED]
> Subject: How should support for scrolling large lists be designed?
>
> Hi Craig and all:
>
> This must be an area of concern for many people!
>
> If a site has potentially thousands of simultaneous users, each doing a
> general search that potentially has a resultset of thousands of rows - how
> should the paging/scrolling of the resultset be designed? What level of
> detail are people saving in the resultset if they use this approach?
>
> How do the major search engines support page prev/next? Are they really
> storing a session-specific resultset? Even when there are thousands of
> potential rows?
>
> I have seen a reference to one solution that advocates storing a
> second-level key to the resultset and defining a second cursor that can
> reposition at a specific page by using this key - but this seems like a
> LOT
> of extra work to me, and it doesn't support paging backwards very well.
>
>
> Thanks for any ideas/experiences,
>
> Ian
>
>
> ----- Original Message -----
> From: Craig R. McClanahan <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Monday, November 15, 1999 9:49 AM
> Subject: Re: JDBC from JSP
>
>
> > "Loganathan, Kamalesh" wrote:
> >
> > > My application is a reporting & monitoring application. Personally, I
> don't
> > > want to use any database related classes in jsp page. Instead, I would
> like
> > > to create report (wrapper ?) beans and use them in the page. But,
> should I
> > > create a bean for each row (entity) or use a manager bean as wrapper
> class
> > > for resultset ??
> > >
> > > Any comments ??
> > >
> >
> > If you are using the ResultSet, either directly or indirectly via a
> wrapper bean,
> > there are some implications:
> >
> > * The ResultSet must remain open (potentially across multiple
> > requests if you allow the user to scroll forwards and backwards
> > by pages like a search engine does).
> >
> > * Therefore, the Statement from which this ResultSet was created
> > must remain open.
> >
> > * Therefore, the Connection from which this Statement was created
> > should remain exclusively allocated to this user (to avoid problems
> > with transactional commits and rollbacks affecting the results).
> >
> > On the other hand, if you create business object beans to represent your
> results,
> > you can close the ResultSet and Statement, and return the connection to
> your
> > connection pool, as soon as the query completes. This can substantially
> increase
> > the number of users your application can support with a given number of
> > connections.
> >
> > Craig McClanahan
> >
> >
> ==========================================================================
> =
> > To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
> JSP-INTEREST".
> > FAQs on JSP can be found at:
> > http://java.sun.com/products/jsp/faq.html
> > http://www.esperanto.org.nz/jsp/jspfaq.html
> >
>
> ==========================================================================
> =
> To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
> JSP-INTEREST".
> FAQs on JSP can be found at:
> http://java.sun.com/products/jsp/faq.html
> http://www.esperanto.org.nz/jsp/jspfaq.html
>
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
FAQs on JSP can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
