On 1/7/2014 3:45 PM, Mark Rotteveel wrote:
> The fact that the optimizer will (or should) optimize it in a specific
> way does not mean you should abuse also it to get the optimizer to use
> a specific optimization. It can lead to hard to find bugs in the
> future, for example if you used SELECT FIRST 1000000 to trick the
> optimizer (aka "I am never going to have more than a million"), fun
> ensues when against all expectations you do pass the 1 million mark.
> And yes, you can take the position that hints make for lazy
> optimizers, on the other hand sometimes optimizers simply make bad
> plans that I'd like to address 'now', and not 10 years down the road
> when Oracle, Microsoft or the Firebird team finally finds time to do
> something with my bug report. In my mind, a specific syntax for
> optimization hints is better than abusing features that will trigger
> specific optimizer behaviour: a separate syntax makes explicit that it
> is an optimization (attempt), and future maintainers of your code
> won't think you were an idiot for abusing 'feature X' in a way that
> doesn't make sense, and they (or you) will be better aware of
> consequences of changing it. And yes, you could document the abuse,
> but I think most people forget to comment or will think it obvious.
> With the SQL Server syntax optimizing for retrieval of the first 150
> rows is done with: SELECT ... FROM someTable ... OPTION (FAST 150) It
> also contains an interesting option (OPTIMIZE FOR) for 'helping' the
> optimizer decide on a plan by providing exemplar values for parameters.
Let's get a little serious. The LIMIT/OFFSET (FIRST in Firebird) is
used when displaying rows by page for display. In these cases, there is
an obvious number of rows to ask for. There are cases where it is clear
that going to effort to retrieve unwanted rows in an otherwise optimal
manner is a losing strategy. I hope we're on the same page so far.
A preposterous use case was presented where somebody was going to burn
hundreds of thousands of DVDs, but wanted to optimize the time for the
first to start burning. Despite the fact that it is a ludicrous case
that should not be used as the basis for system design, we pointed out
that FIRST <large number> would accomplish the task, which it will. To
argue that somebody might not pick a number "large enough" and result in
an application malfunction is beyond lame.
No database application should be dependent on an optimizer to function
correctly, just quickly and efficiently. An optimizer can be reasonably
expected to pick the best execution strategy given the data available to
it. As database architects, it is our job to define interfaces that
provide the information that the optimizer needs to perform its work.
The LIMIT/OFFSET clauses and syntactic variants fill the need precisely.
Personally, I don't like SQL. I've never liked SQL. I much prefer to
use a language that I'm free to extend to implement semantics that I
feel are important, hence the bipolar GDML/SQL nature of Interbase /
Firebird. The mantra of Interbase was "SQL: We don't fix, improve it,
or extend it. We just implement it. We innovate in GDML."
My next database system, an implementation of the amorphous data model,
is designed for highly parallel execution across an arbitrary number of
machines. The interface language (not SQL!) is designed to express
semantics in such a way that the compiler and runtime can send pieces
hither, thither, and yon to where ever the data might reside. There
will be no provision for hints, plans, or anything of the like. "Tell
me what you want, and I'll figure out how to do it.". Humans should not
be in the business of telling computers how best to do things.
Grumph.
-- The Wolf
------------------------------------------------------------------------------
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
