I was thinking along those lines as well, but AFAIK there is not ANSI
standard way to limit the size of a result set. My preference is to not use
DB-specific functionality, but if push comes to shove it sounds like I'll
have to go down that route. Before I do that I'd like to explore
behind-the-scenes performance implications w/ the Statement.setMaxRows()
method. Thanks, Andras!
----- Original Message -----
From: "Andras Balogh" <[EMAIL PROTECTED]>
To: "Struts Users Mailing List" <[EMAIL PROTECTED]>
Sent: Monday, November 19, 2001 7:29 AM
Subject: Re: Statement object
> Hi,
>
> You might try using LIMIT:
> SELECT LastName FROM Students LIMIT 10,20
>
> This works in MYSQL and Postgres but is not SQL standard as far as i
> know.
>
>
> Andras.
>
>
>
>
>
> On Mon, 2001-11-19 at 19:11, John M. Corro wrote:
> > I'm trying to enhance performance on pages the produce listings from a
given DB table (ie search result pages or pages that list only 10 products
out of 100 at a time ). Due to the nature of user interaction, it seems the
best way is to execute a new query for each page (rather then retrieve all
records on the first page and store them to memory which is what's done
now). That is, as the user hits "Previous 20..." or "Next 20 ...", a new
connection to the DB is opened and a new ResultSet is retrieved/displayed.
> >
> > My question is in regards to the Statement.setMaxRows() method.
Consider the following scenario:
> >
> > - Table 'Students' has 1000 records
> > - I use the following code to retrieve the first 100 student records:
> > Connection connection = ...;
> > PreparedStatement ps = connection.prepareStatement("SELECT LastName FROM
Students");
> > ps.setMaxRows(100);
> > ResultSet rs = ps.executeQuery();
> >
> > In this scenario, does the PreparedStatement object actually retrieve
all 1000 records, but then only load the first 100 into the ResultSet thus
"silently dropping excess rows" (as is described by Javadocs)? If so, that
would seem like only marginal performance gains would be realized and that
I'd be better off capping that size of the ResultSet at the SQL query
itself.
> >
> > My hope is that the PreparedStatement object is smart enough to only
retrieve 100 records. Anybody have any experiences w/ it?
> >
> > John M. Corro
> > Cornerstone Consulting
> > 414.212.3500
>
>
>
> --
> To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
>
>
>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
