Re: experience sharing: select query returns more records than necessary

On Tue, Jan 20, 2009 at 9:43 AM, Kent Tong <kent(at)cpttm(dot)org(dot)mo> wrote:

>
>
>
> Oliver Jowett wrote:
> >
> > Dave Cramer wrote:
> > Though, I would suggest that if you really only care about the first 50
> > results, then put a LIMIT 50 in your query! The query planner may come
> > up with a better plan if you do that, too ..
> >
> > If you want to "stream" the whole resultset, then fetchsize is certainly
> > the way to do it.
> >
>
> Yeah, I was actually settling on "LIMIT 50" before coming across
> setFetchSize().
> However, the support for LIMIT varies from one DBMS to another. To keep
> the code as portable as possible, I decided to go with setFetchSize().
>
> Kent,
>

Check the difference in the plans. Using limit can invoke an index that
might not otherwise be used. It can make orders of magnitude differences in
these kinds of queries.

Dave