What database are you using?
In Oracle you can use the 'rownum' psuedo column to limit the number
of rows returned. You could change your query to:
String query =
"select * from table1 where rownum<=100 order by trans_date desc";
This will return at most 100 rows. As already mentioned though, the
database needs to examine all the rows to propertly ordered the data
anyway so I don't think it buys much performance. The only advantage
is your could take your 'count' variable out unless it is needed for
some other reason than knowing when to stop fetching from the
ResultSet.
Paul
>
> Change your query to "Select top 100 * from ..." returns the first 100
> records.
>
> > -----Original Message-----
> > From: List Resident [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, January 02, 2001 3:06 PM
> > To: [EMAIL PROTECTED]
> > Subject: Re: How to set resultSet size
> >
> >
> > Content-Type: text/plain; charset=us-ascii; format=flowed
> > Content-Transfer-Encoding: 7bit
> >
> > "S. Chen" wrote:
> > >
> > > String query = "select * from table1 order by trans_date desc";
> > > [...]
> > > while (rs.next()&& count <100) {
> > > [...]
> > >
> > > The problem is soon enough there will be over 10,000
> > > transactions in the database. If I do it this way, it will be
> > > not efficient. so I want to use the method setFetchSize(100).
> >
> > This isn't inefficient on most databases. You've paid the price
> > of the ORDER BY clause, and for most real DBMSes the code you
> > have is fine.
> >
> > Also, setFetchSize has very little to do with this. The fetch
> > size is just an internal performance hint to the JDBC driver, and
> > should not be set based on application requirements. The prefetch
> > row count should be based on row size, packet size, network
> > congestion, etc.
> >
> > More relevant, perhaps, is setMaxRows, if your driver supports
> > it, or SQL equivalents. You should be warned, though, that
> > limiting the number of rows returned from a query with an ORDER
> > BY clause may not work. In Oracle, for example, the limiting is
> > done *before* the sorting. You can check your DBMS documentation
> > to see if it supports any performance enhancements for limiting
> > result set size that behave properly with ORDER BY, or
> > investigate other database-specific tuning, if really necessary.
> > Like most performance enhancements, it probably isn't.
> >
> > John L
> >
> > ==============================================================
> > =============
> > To unsubscribe: mailto [EMAIL PROTECTED] with body:
> > "signoff JSP-INTEREST".
> > For digest: mailto [EMAIL PROTECTED] with body: "set
> > JSP-INTEREST DIGEST".
> > Some relevant FAQs on JSP/Servlets can be found at:
> >
> > http://java.sun.com/products/jsp/faq.html
> > http://www.esperanto.org.nz/jsp/jspfaq.html
> > http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
> > http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
> >
>
> ===========================================================================
> To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
> For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST".
> Some relevant FAQs on JSP/Servlets can be found at:
>
> http://java.sun.com/products/jsp/faq.html
> http://www.esperanto.org.nz/jsp/jspfaq.html
> http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
> http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
>
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
