On 2-1-2014 12:55, Dmitry Yemanov wrote:
> The good question is how Firebird behaves now from this point of view.
> Mostly it uses the ALL ROWS approach, and the underlying cost based
> optimization algorithms also deal with complete stream cardinalities.
> There are some heuristics based deviations like using the ORDER plan
> even when SORT would be faster, and originally I was thinking that we
> have some intermediate mode (DEFAULT / UNKNOWN ROWS), but later I came
> to conclusion that usage of more advanced statistics (e.g. index
> clustering factor that's already available in GSTAT output in v3) will
> change the behaviour in the right direction. So we may treat Firebird
> basically preferring the ALL ROWS mode now and becoming even stricter in
> this regard in the future.
It would be interesting to know which is better for performance, given
isc_dsql_fetch, a FIRST (x) ROWS might perform better when small fetch
sizes are used.
> This makes it necessary to allow the FIRST ROWS mode when it's needed.
> I'm proposing the following:
>
> 1) FIRST ROWS mode is implicitly used when the FIRST / ROWS clause is in
> game. Also, FIRST ROWS mode is implicitly used for EXISTS / ANY subqueries.
>
> 2) An explicit clause is introduced to force the non-default mode (ALL
> ROWS for FIRST-claused queries and FIRST ROWS for other queries). Hating
> Oracle-like pseudo-comments with embedded hints and preferring explicit
> syntax, I suggest (inspired by DB2):
>
> SELECT ...
> [OPTIMIZE FOR {FIRST | ALL} ROWS]
I'd actually prefer comments like Oracle does. I think it is better for
portability.
Explicit syntax like this might be an additional burden on Jaybird as
that includes a statement parser as well for part of its functionality
(although a quick check shows it doesn't really apply to SELECT).
...
> As so far we speak about optimization of the final (user retrievable)
> datasets, it makes sense to use the OPTIMIZE clause at the top level
> only, i.e. for select statements but not for select expressions. This is
> similar to how WITH LOCK and FOR UPDATE clauses are used. However, full
> featured hints should be available to select expressions as well. I'm
> not sure whether we should choose a "protecting" syntax now and extend
> it (compatibly!) later, or allow FIRST / ALL ROWS rules for subqueries
> since the beginning, or parse the hints and throw context based errors
> if necessary.
If we go with comments, I'd suggest that throwing exceptions for hints
should be optional (for portability), maybe using a dpb item to turn it
off (default on), or a server- or database specific config option.
Mark
--
Mark Rotteveel
------------------------------------------------------------------------------
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
