FWIW -
A solution I use is to store the keys of the larger resultset and page up
and down those. So...
1) Save all data for current page only. Store keys separately with session.
2) When the use pages (prev, next, first, last), create a SELECT ... WHERE
... IN ... statement for the page of keys being requested.
A simple cursor object maintains where we are in our key set for paging
purposes (and prev, next, first, last button activation).
Additional restrictions can be helpful:
- limit rows per page: we let our users select, but no more than 50 rows per
page. Any higher and the page rendering and edit validation processes take
a little too long.
- limit total rows returned: we limit to 1000 rows, although we do indicate
the total number that were found with the query. The suggestion being they
refine the query (which we allow them to do). Large result sets in our
context are not very usable.
Cheers,
Dan
--
Daniel Kirkdorffer
NACN IS: 425-580-6225
Sr. Consultant, Syllogistics LLC
Email: [EMAIL PROTECTED]
Web: http://www.syllogistics.com/
> ----------
> From: SoftLiban ZAKHARIA
> Michael[SMTP:[EMAIL PROTECTED]]
> Reply To: SoftLiban ZAKHARIA Michael
> Sent: Monday, December 20, 1999 1:22 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Large number of records in JSP !!!
>
> We are running into the same issues. When it gets to sorting, or
> searching!!
> We are allowing the user to select the column to sort on, or search! So
> your
> suggestion about returning enough information, we've tried, but have not
> yet
> found an optimized solution.
>
> Any suggestions would be greatly appreciated!!
>
> Michael G. Zakharia
> SoftLiban
> www.softsolutions.fr
>
> ----- Original Message -----
> From: Matthew Baird <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Monday, December 20, 1999 2:01 AM
> Subject: Re: Large number of records in JSP !!!
>
>
> > Not a bad solution, but what if the user wants to have the results
> returned
> > with a sorting applied to the recordset? In this case, you won't be able
> to
> > use the primaryKey value.
> >
> > The solution is to return enough information to maintain context between
> > requests, that includes, but is not limited to:
> > - Row to start at
> > - the 'where' clause data
> > - the sortby column name
> > - the sort order
> > - optionally, hasnext and has previous to build the next/prev buttons.
> >
> > hide this stuff on the form in the case of a post, or build into post
> > strings for get's.
> >
> > The nice thing is you get to remain stateless, but offer nicer
> > searching/sorting options for your users.
> >
> > If you need more help in the actual implementation, email me for some
> > source.
> >
> > regards,
> > Matthew
> >
> > -----Original Message-----
> > From: Matt Krevs [mailto:[EMAIL PROTECTED]]
> > Sent: Sunday, December 19, 1999 2:34 PM
> > To: [EMAIL PROTECTED]
> > Subject: Re: Large number of records in JSP !!!
> >
> >
> > Ok, maybe I was a bit lazy. This is roughly how we are implementing it
> >
> > 1. User submits query and specifies the max rows they want to receive
> (eg
> > 100)
> > 2. Server sets the max row count variable (varies between DBMS
> > implementations) to 100 and executes the query
> > 3. up to 100 rows are returned to the client
> >
> > 4. The user clicks the 'more' button
> > 5. The primary key value of the last row is sent to the server as a
> > parameter.
> > 6. The same query is sent to the server with the following pseudo WHERE
> > clause tacked on the end
> >
> > WHERE primary_key_column > 'primary key value of last row in results'.
> >
> > We dont do any resultsets caching as our whole architecture is
> stateless.
> >
> > ----Original Message-----
> > From: A mailing list about Java Server Pages specification and reference
> > [mailto:[EMAIL PROTECTED]]On Behalf Of Geert Van Damme
> > Sent: Monday, December 20, 1999 8:35 AM
> > To: [EMAIL PROTECTED]
> > Subject: Re: Large number of records in JSP !!!
> >
> >
> > There are a number of possible ways to handle this, and you don't give
> any
> > suggestions.
> > There's no problem in showing only the first 10 rows on the first page,
> the
> > main question is: how do you show the next 10 on the second page ;-)
> >
> > Here's how I see it
> > - Read the whole result set into memory, store that in the session. Easy
> to
> > implement. Not such a good idea in terms of performance or memory usage
> I
> > guess, unless you have a lot of users asking the same question.
> > - Store the results in a temporary table. Here you have the problem that
> a
> > temp table only exists within the scope of a connection. You cannot use
> the
> > same connection in several requests unless you store it in a session.
> > Especially for many-users high volume sites this isn't a very good idea.
> It
> > ruins all the benefit you have from the connection pooling. If for some
> > reason you do use 1 connection per user session, this certanly is an
> option.
> > - As unlikely as it seems at first, my best guess would be just do the
> query
> > again, discard the first x rows and show the following ones. The
> probability
> > that a user will select next is not that high, so performing the query
> again
> > isn't that bad. If doing the query is very resource consuming, it's your
> > first page's performance that will suffer the most ;-)
> > Don't care about caching, just provide enough RAM for the RDBMS. It will
> use
> > it's own caching.
> >
> > Does this sounnd reasonable? Any other solutions?
> >
> > How does it work on the real big search engines like altavista or
> dejanews?
> >
> >
> > Geert 'Darling' Van Damme
> >
> > > -----Original Message-----
> > > From: A mailing list about Java Server Pages specification and
> reference
> > > [mailto:[EMAIL PROTECTED]]On Behalf Of Matt Krevs
> > > Sent: zondag 19 december 1999 21:58
> > > To: [EMAIL PROTECTED]
> > > Subject: Re: Large number of records in JSP !!!
> > >
> > >
> > > Dont load the 1000s of records at once.
> > >
> > > Send the data back to the browser in small chunks, maybe 100 rows
> > > at a time.
> > >
> > > For example, if the user submits a query that matches 3000 rows,
> > > only return
> > > the first 100. Provide a 'more' button that progressively gets
> > > the next 100
> > > rows.
> > >
> > > Basically you need to handle this situation like most of the
> > > internet search
> > > engines do - eg yahoo, dejanews.
> > >
> > > -----Original Message-----
> > > From: A mailing list about Java Server Pages specification and
> reference
> > > [mailto:[EMAIL PROTECTED]]On Behalf Of Vaibhav Bhanot
> > > Sent: Saturday, December 18, 1999 10:13 PM
> > > To: [EMAIL PROTECTED]
> > > Subject: Large number of records in JSP !!!
> > >
> > >
> > > Hi All ,
> > > I am in a fix here...could anybody help me out...i am using jsp's
> > > at the client side and ORACLE as the backend...i have got around
> > > thousands of records in some of my tables...now i cannot show all the
> > > records to client if he queries the database...how should i handle
> this
> > > situation...
> > >
> > > vaibhav
> > >
> > > ==================================================================
> > > =========
> > > 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
> >
> >
> ==========================================================================
> =
> > 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
>
===========================================================================
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
