SELECT TOP n, Statement.setMaxRows(), Statement.setFetchSize()

**************************Answer 1********************************************

SetFetchSize() has nothing to do with what you get back from a query. It is only a hint from the coder to the driver (which the driver may ignore) about how many rows to fetch from the DBMS at a time. In other words, if the query returns 100 rows and you set fetch size to 10, the driver will fetch ten rows to start, and when you call next() the eleventh time, the driver will go to the DBMS and get the next ten rows. This trades client memory for minimizing client-DBMS roundtrips. A given driver and DBMS may or may not be able to alter their fetch size, so the driver may ignore your hint.

SELECT TOP is a specific one-time query criterion, implemented and satisfied by the DBMS. The driver knows nothing about it and gets whatever rows the DBMS sends. The setMaxRows() call means the driver will simply count the rows returned, and give you access only to the number you set. The DBMS still sends all the data, and the driver may still have to read all the data in order to clear the line.

Also note that this statement setting remains until reset. If you knowingly or unknowingly re-use the statement (such as if there is a statement cache in the driver or pool implementation) you will retain this non-default behavior. This means that it is better to use "SELECT TOP" when you know you need it. The other option is to write standard SQL (albeit fancy) that gets you only the rows you want. "SELECT TOP" if used with an "ORDER BY" clause may require the DBMS to internally select all the data, and make an internal temp table with all of it, and then sort it according to your "ORDER BY" clause, if there's no appropriate index to the original data

HTH,

Joe Weinstein at BEA Systems.

**************************Answer 2*********************************************

setFetchSize(int) defines the number of rows that will be read from the database when the ResultSet needs more rows. The method in the java.sql.Statement interface will set the 'default' value for all the ResultSet derived from that Statement; the method in the java.sql.ResultSet interface will override that value for a specific ResultSet. Since database fetches can be expensive in a networked environment, fetch size has an impact on performance.

setMaxRows(int) sets the limit of the maximum nuber of rows in a ResultSet object. If this limit is exceeded, the excess rows are "silently dropped". That's all the API says, so the setMaxRows method may not help performance at all other than to decrease memory usage. A value of 0 (default) means no limit.

Since we're talking about interfaces, be careful because the implementation of drivers is often different from database to database and, in some cases, may not be implemented or have a null implementation. Always refer to the driver documentation.