On Fri, Jan 3, 2014 at 5:26 AM, Mark Rotteveel <m...@lawinegevaar.nl> wrote:
>
>
> ...case where isc_dsql_fetch is
> used with a small fetch size in comparison toe the entire result set.
>
> I assume the current optimization is:
> Client: execute query
> Server materializes all rows
> Client: fetch size=1
> (wait until all rows are materialized)
> Server: returns row 1
> Client: fetch size=1
> Server: returns row 2
>
For the sake of the naive following this discussion, Mark's assessment
of the operation of the Firebird runtime is not general. He's correct about
the plan created by the optimizer when there is an ORDER BY clause
without any syntactic restriction on the size of the result (e.g. no FIRST
clause, no ROWS clause, etc.)
Without an ORDER BY, Firebird returns enough data to fill the fetch
buffer, returns the buffer and begins gathering more rows.
With an ORDER BY and a restriction on the number of rows retrieved
and an index that can be used to emulate the sort required to order the
results, Firebird returns enough data to fill the fetch buffer, returns the
buffer, and begins gathering more rows.
Only the case of an ORDER BY that doesn't specify a limit on the number
of rows or that cannot be emulated by following an index, does Firebird
assemble the whole result before returning data to the client. Obviously,
at least to me, if you've got to sort the data, you have to read all of it.
Furthermore, despite the everyone's instinct, it's a good deal faster in
the general case to read a table in an optimal order and sort the data
in memory that to read the data in index order - random order relative
to storage.
Good luck,
Ann
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
