We use SQL Server, and we use your option (2). As long as you use scrollable
cursors, you're not actually retrieving everything, just what you need. You
can also use TOP to help limit the query. Some of our queries are against
tables with many millions of rows, and the result sets can contain millions
of entries. Still, as long as the database is set up properly, the queries
and retrieval are fast.
--
Martin Cooper
----- Original Message -----
From: "Mindaugas Idzelis" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, June 16, 2001 11:36 AM
Subject: RE: General DATABASE programming question
>
> > I know it sounds wasteful, but personally I would think about getting a
> > page at a time. This is more work on the DBMS as they go to later pages,
> > but many times they don't go past the first page. Otherwise you will
> > need to keep the resultset in the session and tie up memory.
> >
> > A lot of people like to use the pager tag from JSP tags for this sort of
> > thing.
>
> My original goal is to ask the database for a page of information at a
time.
> However, MS SQL server does not let you display your results starting at
an
> offset.
>
> In my web app's search method, I load the ResultSet into a LinkedList and
> use the struts iterate taglib to display the results to the JSP tags pager
> taglib.
>
> So, I see two ways of resolving this. 1) Do the query one time and store
the
> LinkedList in the session -- effectively caching the entire results in
> memory. 2) Query the database each time and skip over the first n results
> and discarding the last x results.
>
> Neither of these solutions are really ideal since the the ammount of
results
> I expect to return are in 1,000 range.
>
> So, is it time to pick a better database? Thanks.
>
>
> > -- Ted Husted, Husted dot Com, Fairport NY USA.
> > -- Custom Software ~ Technical Services.
> > -- Tel 716 737-3463.
> > -- http://www.husted.com/about/struts/
> >
> >
> > Mindaugas Idzelis wrote:
> > >
> > > Thanks for all your answers. This has been very nerve wrecking.
> > I think I've
> > > come up with a solution that may work in a DB independent way.
> > I'll use a
> > > scrollable resultset. I won't iterate through all of it, only
> > portions at a
> > > time. I think most underlying JDBC drivers use "cursors" to
> > implement the
> > > scrollable resultset. Is this a valid approach?
> > >
> > > --min
>
