Re: [Firebird-devel] Some aspects of the optimizer hints
Ann Harrison писал(а) в своём письме Mon, 06 Jan 2014 20:38:32 +0400: > The FIRST keyword was added to Firebird's SQL in version 1.0 or 1.1 - > in 2000 0r 2001, but the use of FIRST in InterBase goes back much > further. > It was part of GDML - and it's purpose was to signal that the query > should > be optimized to walk the index (navigate, in the internals) rather than > create > a bitmap and access records in storage order. There's no reason to > limit > the number of record a query returns - when you've seen enough, just > close > itt. The semantic significance of FIRST and its relatives is "I want the > first > records quickly." > So, historically, no, it's neither a workaround nor a hack. Parsing SQL > comments ... that sounds like a hack to me. Adding yet another > non-standard keyword when one already exists and does what you want > doesn't appeal all that much either. Analysis of comments and its impact on the plan really looks like the hacker way. Enter a keyword for a custom optimizer hints needed in any case. No matter how clever the optimizer it can still be wrong (and it is not only the choice of strategies FIRST / ALL ROWS). Tips exist in one form or another in many RDBMS (Oracle, MS SQL, DB2 ...) -- Написано в почтовом клиенте браузера Opera: http://www.opera.com/mail/ -- 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
Re: [Firebird-devel] Some aspects of the optimizer hints
On 01/06/2014 08:38 PM, Ann Harrison wrote: Dmitry, > Right. And if you want to give the optimizer a hint that it should choose > the second plan, change the query like this: > > select first 100 * from MOVIES where COMMENTS like '%yacht% order > by NAME; > > If you think your client may want more than a million rows, increase > that number. I mentioned this approach in my initial message and it does not look good to me. I treat it as a workaround / hack, not a solution. The FIRST keyword was added to Firebird's SQL in version 1.0 or 1.1 - in 2000 0r 2001, but the use of FIRST in InterBase goes back much further. It was part of GDML - and it's purpose was to signal that the query should be optimized to walk the index (navigate, in the internals) rather than create a bitmap and access records in storage order. There's no reason to limit the number of record a query returns - when you've seen enough, just close itt. The semantic significance of FIRST and its relatives is "I want the first records quickly." So, historically, no, it's neither a workaround nor a hack. Parsing SQL comments ... that sounds like a hack to me. Adding yet another non-standard keyword when one already exists and does what you want doesn't appeal all that much either. So you suggest to use FIRST without number of records? I see almost no problems with this approach - the only small question is weather btyacc can handle such grammar well. Also, there may be (in fact, there are) customers who need the FIRST ROWS strategy being the default one. And rewriting a majority of their queries to include the dummy FIRST clause is not something they can consider seriously. So would you consider a connection option? A transaction option? Or even config file option. Not related with SQL IMHO. -- 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.clktrkFirebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Some aspects of the optimizer hints
Ann, > The FIRST keyword was added to Firebird's SQL in version 1.0 or 1.1 - > in 2000 0r 2001, but the use of FIRST in InterBase goes back much further. > It was part of GDML - and it's purpose was to signal that the query should > be optimized to walk the index (navigate, in the internals) rather than > create > a bitmap and access records in storage order. There's no reason to limit > the number of record a query returns - when you've seen enough, just close > it. The semantic significance of FIRST and its relatives is "I want > the first records quickly." Quite interesting, thanks. This is really close to what I'm trying to discuss. Although this seems to be lost with time, as InterBase 6.0 had FIRST clause in GDML acting as a row limit and Firebird's FIRST clause just reused the same BLR implementation. And the optimizer didn't have any indications to treat blr_first as a hint that time. > So, historically, no, it's neither a workaround nor a hack. Here I may agree (knowing the history). > Parsing SQL comments ... that sounds like a hack to me. And here I agree as well. Although we've already seen other opinions. > Adding yet another non-standard keyword when one already exists and does what > you want > doesn't appeal all that much either. But the FIRST clause in both SQL and GDML does not act as a hint for the past 15 years (at least). It can surely be considered by the optimizer (and this is already committed to v3.0), but its documented semantics is quite different. > So would you consider a connection option? A transaction option? As stated in my initial message, I'm open for opinions in this regard. It could be specified per database, or per connection, or maybe even per transaction (although I doubt it's really needed). But regardless the global level FIRST ROWS is specified as the default behavior, users must be able to alter it to ALL ROWS at the statement level, if required. And it's impossible using the existing syntax. Dmitry -- 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
Re: [Firebird-devel] Some aspects of the optimizer hints
07.01.2014 12:28, Alex wrote: > Or even config file option. Not related with SQL IMHO. It *is* related as soon as you need to alter from the default FIRST ROWS to custom FIRST ROWS in some particular query. Dmitry -- 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
[Firebird-devel] Firebird Interbase Database engine hacks or rtfm
Notes on database security assesment http://www.slideshare.net/qqlan/firebird-interbase-database-engine-hacks-or-rtfm -- 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
Re: [Firebird-devel] Some aspects of the optimizer hints
> > 5) ALL ROWS mode is the default one for queries without FIRST / ROWS > clause. Whether the default mode can be altered per database (via > configuration file) or per session is discussable. > > Dmitry Strategy ALL ROWS default optimizer I consider correct. Nevertheless, I believe that it is necessary to introduce a parameter in the configuration database level which could be returned to the old behavior where the optimizer to use a strategy FIRST ROWS. This can be useful for the operation of existing applications that have been stored for such behavior. -- 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
Re: [Firebird-devel] Some aspects of the optimizer hints
07.01.2014 12:46, Dmitry Yemanov wrote: > > It *is* related as soon as you need to alter from the default FIRST ROWS > to custom FIRST ROWS in some particular query. Read: "custom ALL ROWS", sorry. Dmitry -- 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
Re: [Firebird-devel] Some aspects of the optimizer hints
On Mon, 6 Jan 2014 11:38:32 -0500, Ann Harrison wrote: > The FIRST keyword was added to Firebird's SQL in version 1.0 or 1.1 - > in 2000 0r 2001, but the use of FIRST in InterBase goes back much further. > It was part of GDML - and it's purpose was to signal that the query should > be optimized to walk the index (navigate, in the internals) rather than > create > a bitmap and access records in storage order. There's no reason to limit > the number of record a query returns - when you've seen enough, just close > itt. The semantic significance of FIRST and its relatives is "I want the > first > records quickly." As I see it, optimizer hints are a different beast from limiting the result set using FIRST. The first is a way to influence decisions of the optimizer, and the second describes what is to be delivered (number of rows; which might have a default optimizer behavior, which might be overridden by an optimizer hint(!)). I'd prefer a generic (and 'extensible') way for optimizer hints, because we are currently discussing 'first n rows' only, but that isn't the only possible optimization that could be added. See for example the way SQL Server hints work as I posted in an earlier mail. > So, historically, no, it's neither a workaround nor a hack. Parsing SQL > comments ... that sounds like a hack to me. Adding yet another > non-standard keyword when one already exists and does what you want > doesn't appeal all that much either. Yes, using comments might be a hack. But it provides a separation between describing what you want, and providing the optimizer with hints on how you think it might choose a better plan, and it doesn't 'pollute' the statement syntax. Mark -- 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
Re: [Firebird-devel] Some aspects of the optimizer hints
07.01.2014 13:25, Simonov Denis пишет: > > Strategy ALL ROWS default optimizer I consider correct. Nevertheless, I > believe that it is necessary to introduce a parameter in the configuration > database level which could be returned to the old behavior where the > optimizer to use a strategy FIRST ROWS. This can be useful for the > operation of existing applications that have been stored for such behavior. The legacy optimizer never used the FIRST ROWS strategy by default and I was trying to explain that in my original posting. It just didn't absolutely follow the ALL ROWS mode (in all possible considerations), but it was close enough. Dmitry -- 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
Re: [Firebird-devel] Some aspects of the optimizer hints
On Tue, 07 Jan 2014 12:28:11 +0400, Alex wrote: >> So, historically, no, it's neither a workaround nor a hack. Parsing SQL >> comments ... that sounds like a hack to me. Adding yet another >> non-standard keyword when one already exists and does what you want >> doesn't appeal all that much either. > > So you suggest to use FIRST without number of records? I see almost no > problems with this approach - the only small question is weather btyacc > can handle such grammar well. I don't like this, it would be a hint for a specific optimization abusing the syntax of something else. I'd prefer a general optimization hint clause that *contains* the optimization hint (and allows for future additions). Mark -- 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
Re: [Firebird-devel] Some aspects of the optimizer hints
I've been following this thread with interest. I can't resist entering my humble opinion. Den 2014-01-07 10:38 skrev Mark Rotteveel såhär: > As I see it, optimizer hints are a different beast from limiting the > result set using FIRST. The first is a way to influence decisions of the > optimizer, and the second describes what is to be delivered (number of > rows; which might have a default optimizer behavior, which might be > overridden by an optimizer hint(!)). This makes sense, but I also think what Jim Starkey said makes sense, i.e. that introducing optimizer hints in essence means you've given up on creating a good automatic optimizer. In the long run it means that for a DBA/developer to be able to create good performing queries you will increasingly need specific FB knowledge about FB specific and inherently non standard optimizer hints. This is not good, and I think it should be considered only as a last resort after trying every other viable solution. What's the problem we're actually trying to solve here? Dmitry is asking for a way to let the server know if the user wants to put priority on 1) "quickest possible response time for first N records" as opposed to priority on 2) "quickest possible response time for the entires result set". This is something that, as Dmitry said, cannot be deduced from the actual SQL, unless FIRST or ROWS is used. So, when would you need one or the other? As far as I can see, 2) would be "the normal case", and 1) would be used only in the the following two cases: 1a) You only want the forst N records, possibly not being able to determine N beforehand. You would normally use FIRST N or ROWS N (preferred nowadays, isn't it?), but if N isn't known beforehand, this is not possible. 1b) You want to display/start using "something" as quickly as possible and then keep fetching more records (possibly the entires result set) in the background. Case 1a) with known N is already possible using FIRST/ROWS. Case 1a) with unknown N could be supported by allowing some variant of FIRST/ROWS with unspecified N, e.g. "FIRST ANY". Case 1b) is a bit harder. You would like to tell the engine that it's important to be able to quicky START working with the result set, but less important to be able to quickly FINISH working with ALL of it (and also important enough that you're prepared to put extra net load on the server to achieve it, as pointed out by Ann). As far as I can see this is something that is inherently a pure optimizer thing, whichever way you look at it. So to be able to support it, we do need some syntax that specific for this purpose and nothing else. The though passed my mind, that perhaps this case might be uncommon enough that it would be alright to have to resort to specifying the complete plan, but the major drawback of this is that the plan has to be "complete", i.e. you need to write a possibly large and complex plan to achieve a change in only a small part of it. Would it be possible to allow specification of only the part of the plan that you want to modify and leave the rest "default"? I can't see it myself... At the end of the day, if the plan clause cannot be modified as suggested, I do think that this would be a useful feature, but is it useful enough to warrant implementation of special syntax? I have no strong opinion here... But, if it is to be implemented, I see it as something very similar to the already existing plan clause, because that also is FB specific, and only deals with the optimizer as opposed to the actual query semantics. Could it be made a variant of plan? As I see it you either want to give the optimizer a hint, or you will dictate the full plan, so I see no reason to support both hint and plan. In other words, there would be no conflict to allow for both hint and a complete plan (mutually exclusive) inside the plan clause. Would this be possible: plan hint "Movies" order "IX_MovieName" This would tell the optimizer that the query should try to use index retrieval for the "Movies" table, which is what we're after. I like this approach because it avoids implementation of some new keyword/syntax outside the already optimizer-specific plan clause. I also like it because it has some other potential uses. In the support forum I often see suggestions to add 0 or similar to avoid using some index. This could also be achieved using plan hints: plan hint "Movies" natural or plan hint "Movies" index "SomeBetterIndex" to avoid using some less-than-optimal index on the table "Movies". If this is not possible or "voted down", I at least strongly vote for some SQL implementation. Parsing comments is a muck-up. Regards, Kjell -- -- Kjell Rilbe DataDIA AB E-post: kj...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64 -- Rapidly troubleshoot problems before they affect your
Re: [Firebird-devel] Some aspects of the optimizer hints
Kjell,
> This makes sense, but I also think what Jim Starkey said makes sense,
> i.e. that introducing optimizer hints in essence means you've given up
> on creating a good automatic optimizer.
Jim is not alone in thinking this way. This is why I explicitly
mentioned that I don't want to discuss either necessity or
implementation of "full featured" hints in this discussion.
> So, when would you need one or the other? As far as I can see, 2) would
> be "the normal case", and 1) would be used only in the the following two
> cases:
>
> 1a) You only want the forst N records, possibly not being able to
> determine N beforehand. You would normally use FIRST N or ROWS N
> (preferred nowadays, isn't it?), but if N isn't known beforehand, this
> is not possible.
>
> 1b) You want to display/start using "something" as quickly as possible
> and then keep fetching more records (possibly the entires result set) in
> the background.
A more common approach might be to fetch the remaining rows by user's
demand. For example, many Delphi applications using DBGrid and its
friends initially fetch only rows fitting just a couple of screens and
continue fetching once user scrolls the grid down.
> Would it be possible to allow specification of only the part of the plan
> that you want to modify and leave the rest "default"?
I'd suggest to let the legacy PLAN resting in piece. It's already misses
many optimizer features and will outdate more and more with years.
> Would this be possible:
>
> plan hint "Movies" order "IX_MovieName"
> plan hint "Movies" natural
> plan hint "Movies" index "SomeBetterIndex"
This could be suitable for generic hints, but not for the FIRST ROWS vs
ALL ROWS choice, as it affects many optimizer decisions at once.
Something like PLAN FOR {ALL | FIRST} ROWS might be a yet another
syntactical option here, although personally I'd prefer to separate
"planning" from "hinting" at the syntax level. So far it was expected
that input (explicitly specified in syntax) and output (reported by the
engine) plans mean the same, but it won't be the case anymore with the
aforementioned approach.
Dmitry
--
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
Re: [Firebird-devel] Some aspects of the optimizer hints
Kjell Rilbe писал(а) в своём письме Tue, 07 Jan 2014 18:40:08 +0400: I agree with Dimitri. Direction plan entirely and hints for the optimizer should be separated. But as a place to OPTIMIZE ... I suggest at the end of SQL query as well as for the plan. -- Написано в почтовом клиенте браузера Opera: http://www.opera.com/mail/ -- 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
Re: [Firebird-devel] Some aspects of the optimizer hints
07.01.2014 17:12, Simonov Denis wrote: > as a place to OPTIMIZE ... I suggest at the end > of SQL query as well as for the plan. Why it have to have fixed position? Cannot btyacc syntax parser handle a clause anywhere?.. -- WBR, SD. -- 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
Re: [Firebird-devel] Some aspects of the optimizer hints
> Why it have to have fixed position? Cannot btyacc syntax parser handle a > clause anywhere?.. Course parser can parse and tips from various places, but I look at it from the perspective of the application developer. It is much easier to remove or comment out the tips when they are concentrated in one place request, than to seek out across a query that may not be quite small. -- 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
Re: [Firebird-devel] RFC: Data page allocation algorithm
>>> I would not give up the compiled binary (for Windows x86). Very >>> interesting to compare the performance of cold filling cache. >> >>I'll prepare binaries at this weekend. > >Here they are: > > http://web.firebirdsql.org/downloads/rabbits/hvlad/Firebird-3.0.0.30811_Win32-Extents.7z > http://web.firebirdsql.org/downloads/rabbits/hvlad/Firebird-3.0.0.30811_x64-Extents.7z > >Note, this build works with changed ODS12, so you should create database > for testing > using this build. With great help of Pavel Zotov i've found and fixed few bugs in that build, so i uploaded an updated binaries for testing: http://web.firebirdsql.org/downloads/rabbits/hvlad/Firebird-3.0.0.30819_Win32-Extents.7z http://web.firebirdsql.org/downloads/rabbits/hvlad/Firebird-3.0.0.30819_x64-Extents.7z Regards, Vlad -- 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
Re: [Firebird-devel] Some aspects of the optimizer hints
On 1/7/2014 1:14 PM, Simonov Denis wrote: >> Why it have to have fixed position? Cannot btyacc syntax parser handle a >> clause anywhere?.. > > Course parser can parse and tips from various places, but I look at it > from the perspective of the application developer. It is much easier to > remove or comment out the tips when they are concentrated in one place > request, than to seek out across a query that may not be quite small. > > > Semantically significant comments are such a mistake. The fact that other system use them don't make them a good idea. Comments are comments and are meant to be ignored. What are you supposed to do if you see a syntax or semantic error is a hint-comment? Given an error? Guess at the users intent? Quietly ignore it? FIRST has some real problems. You could do it with the non-standard Firebird FIRST, but extending the SQL standard clauses to allow the number of rows to be omitted is a blatant violation of the standard, which is not a good idea. The FIRST clause in GDML goes all the way back to Datatrieve-11 V1.0. It's extremely convenient for interactive use. I used it to signal navigational index walking in, maybe, 1986 or 1987 for dBase emulation for Ashton-Tate. So we're not really talking semantics here, just the syntax. "FIRST " does the trick. It works as well with standard SQL as Firebird SQL. I haven't a clue why the optimizer isn't handling it correctly, if, in fact, it isn't. Semantically significant comments are among the worst general language ideas in the history of computing. Outside of the database world, has anyone ever run into a real language that supports anything like it? Guy, something is either part of the language or it isn't. -- 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
Re: [Firebird-devel] Some aspects of the optimizer hints
On 7-1-2014 20:21, Jim Starkey wrote: > On 1/7/2014 1:14 PM, Simonov Denis wrote: >>> Why it have to have fixed position? Cannot btyacc syntax parser handle a >>> clause anywhere?.. >> >> Course parser can parse and tips from various places, but I look at it >>from the perspective of the application developer. It is much easier to >> remove or comment out the tips when they are concentrated in one place >> request, than to seek out across a query that may not be quite small. > > Semantically significant comments are such a mistake. The fact that > other system use them don't make them a good idea. Comments are > comments and are meant to be ignored. What are you supposed to do if > you see a syntax or semantic error is a hint-comment? Given an error? > Guess at the users intent? Quietly ignore it? You are responding to a post that says having it in one place makes it easy to comment out (as in: disable), not about having semantically significant comments. > FIRST has some real problems. You could do it with the non-standard > Firebird FIRST, but extending the SQL standard clauses to allow the > number of rows to be omitted is a blatant violation of the standard, > which is not a good idea. > > The FIRST clause in GDML goes all the way back to Datatrieve-11 V1.0. > It's extremely convenient for interactive use. I used it to signal > navigational index walking in, maybe, 1986 or 1987 for dBase emulation > for Ashton-Tate. So we're not really talking semantics here, just the > syntax. "FIRST " does the trick. It works as well with > standard SQL as Firebird SQL. I haven't a clue why the optimizer isn't > handling it correctly, if, in fact, it isn't. I agree that SELECT FIRST n should be an indication for the optimizer to use a specific optimization, however I'd like to reiterate my position that it isn't a hint, it describes an expected output. 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 100 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. 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
Re: [Firebird-devel] Some aspects of the optimizer hints
On 7-1-2014 16:07, Dmitry Yemanov wrote: > Kjell, > >> This makes sense, but I also think what Jim Starkey said makes sense, >> i.e. that introducing optimizer hints in essence means you've given up >> on creating a good automatic optimizer. > > Jim is not alone in thinking this way. This is why I explicitly > mentioned that I don't want to discuss either necessity or > implementation of "full featured" hints in this discussion. I do think you should consider the implications of having full featured hints in the future, if only not to make a poor choice now when it comes to syntax. 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
[Firebird-devel] Odp: Some aspects of the optimizer hints
>>I'd suggest to let the legacy PLAN resting in piece. It's already misses many
>>optimizer features and will outdate more and more with years.
Hi, Dmitry
Why plan can not by extended for hint purposes? This is natural place for FB
users to tell optimizer the better way of query execution. This can be created
as visally extended but really different keyword like PLAN HINT ..., PLAN HINT
..., PLAN here the old featured plan ..
Regards,
Karol Bieniaszewski
- Reply message -
Od: "Dmitry Yemanov"
Do: "For discussion among Firebird Developers"
Temat: [Firebird-devel] Some aspects of the optimizer hints
Data: wt., sty 7, 2014 16:07
Kjell,
> This makes sense, but I also think what Jim Starkey said makes sense,
> i.e. that introducing optimizer hints in essence means you've given up
> on creating a good automatic optimizer.
Jim is not alone in thinking this way. This is why I explicitly
mentioned that I don't want to discuss either necessity or
implementation of "full featured" hints in this discussion.
> So, when would you need one or the other? As far as I can see, 2) would
> be "the normal case", and 1) would be used only in the the following two
> cases:
>
> 1a) You only want the forst N records, possibly not being able to
> determine N beforehand. You would normally use FIRST N or ROWS N
> (preferred nowadays, isn't it?), but if N isn't known beforehand, this
> is not possible.
>
> 1b) You want to display/start using "something" as quickly as possible
> and then keep fetching more records (possibly the entires result set) in
> the background.
A more common approach might be to fetch the remaining rows by user's
demand. For example, many Delphi applications using DBGrid and its
friends initially fetch only rows fitting just a couple of screens and
continue fetching once user scrolls the grid down.
> Would it be possible to allow specification of only the part of the plan
> that you want to modify and leave the rest "default"?
I'd suggest to let the legacy PLAN resting in piece. It's already misses
many optimizer features and will outdate more and more with years.
> Would this be possible:
>
> plan hint "Movies" order "IX_MovieName"
> plan hint "Movies" natural
> plan hint "Movies" index "SomeBetterIndex"
This could be suitable for generic hints, but not for the FIRST ROWS vs
ALL ROWS choice, as it affects many optimizer decisions at once.
Something like PLAN FOR {ALL | FIRST} ROWS might be a yet another
syntactical option here, although personally I'd prefer to separate
"planning" from "hinting" at the syntax level. So far it was expected
that input (explicitly specified in syntax) and output (reported by the
engine) plans mean the same, but it won't be the case anymore with the
aforementioned approach.
Dmitry
--
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
--
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.clktrkFirebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Some aspects of the optimizer hints
08.01.2014 00:49, Mark Rotteveel wrote: > > I do think you should consider the implications of having full featured > hints in the future, if only not to make a poor choice now when it comes > to syntax. And I suggested how the OPTIMIZE clause can be extended later, if required. Dmitry -- 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
Re: [Firebird-devel] Odp: Some aspects of the optimizer hints
08.01.2014 10:30, liviusliv...@poczta.onet.pl wrote: > Why plan can not by extended for hint purposes? It can, the question is whether it should. > This is natural placefor FB users to tell optimizer > the better way of query execution. Personally, I see an important difference between planning (in how it historically looked like) and hinting (in how it's being discussed), so I'd prefer separate clauses for these actions. That said, I'm not going to insist if the majority here would favor the PLAN clause being used also for hinting. Dmitry -- 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
