Re: What's the difference of "SELECT TOP n ...", Statement.setMaxRows() and Statement.setFetchSize() ?


Jet Mah wrote:
Hi all,

I know there is one sql script "SELECT TOP n ..." only in the SQL
Server, but what's the difference of the "SELECT TOP n " script,
Statement.setMaxRows() and Statement.setFetchSize()? Thanks a lot.

Regards,
Jet

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.

.

Relevant Pages
Loading