Thread: Limiting number of rows returned at a time in select query

Hi All.

Is there a way inside a query (or connection) to limit the amount of
records returned each chunk by the server? At the moment, I have 22
million records trying to be returned in one-go as the result set. I
have a .NET driver that has a FetchSize option which allows the above
(say returning 10k rows at a time without holding them all in memory);
I'm not sure though if that's a property of the driver / server, as
none of the other interfaces I have seem to include it. It just makes
sense it might be server-side; although, if it's not I'm quite happy
to be corrected.

Any pointers are appreciated.

Cheers,

Jon.

On Thu, May 24, 2007 at 05:32:25AM -0700, Jon Clements wrote:
> have a .NET driver that has a FetchSize option which allows the above
> (say returning 10k rows at a time without holding them all in memory);
> I'm not sure though if that's a property of the driver / server, as
> none of the other interfaces I have seem to include it. It just makes

I expect it's the driver doing cursor work for you without you
needing to make it happen explicitly.  I think some interations of
the JDBC driver do something similar, but I can't recall for certain
(and I'm not looking at the docs right now).  Anyway, if it _is_
doing a cursor for you behind the scenes, that's almost certainly
what you want.

A
--
Andrew Sullivan  | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
        --H.W. Fowler