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]>