On Tue, Jan 7, 2014 at 3:45 PM, Mark Rotteveel wrote:
>
> 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 dec
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 SELEC
On 01/07/2014 01:35 PM, Dmitry Yemanov wrote:
> 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.
>
>
True. I.e. historical form of giving 'FIRST' -
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
---
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
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 applic
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
> 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 conce
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.
-
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.
--
Написано в почтовом клиенте бра
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
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,
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 eit
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
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
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 t
>
> 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
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
--
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) rathe
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;
>
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
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, i
Ann,
> 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 num
On 01/06/2014 12:41 AM, Ann Harrison wrote:
Alex,
> 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
Alex,
> > 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.
> >
>
> Ann, from server POV you are definitely
On 01/04/2014 12:08 AM, Ann Harrison wrote:
> 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
On Fri, Jan 3, 2014 at 5:26 AM, Mark Rotteveel 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
On 2-1-2014 20:50, Dmitry Yemanov wrote:
> 02.01.2014 21:43, Mark Rotteveel wrote:
> >
>> 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.
>
> What do you mean by "small fetch sizes"
On 2-1-2014 19:37, Jim Starkey wrote:
> I presume everyone knows about standard SQL "select ... OFFSET ROWS
> ... FETCH ROWS", et al?
That is not the same as an optimizer hint. An optimizer hint for FIRST
(or FIRST n ROWS) indicates that you don't need all rows at once,
because you retrieve a
02.01.2014 21:43, Mark Rotteveel wrote:
>
> 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.
What do you mean by "small fetch sizes"? If it's about incomplete fetch,
i.e. only few row
On 1/2/2014 8:10 AM, Simonov Denis wrote:
> Dmitry Yemanov писал(а) в своём письме Thu, 02 Jan
> 2014 15:55:57 +0400:
>
>
>> 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 clau
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 deviation
Dmitry Yemanov писал(а) в своём письме Thu, 02 Jan
2014 15:55:57 +0400:
>
> 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 implic
All,
We had this topic raising from time to time, resulted in CORE-1488. So
far there was no appropriate discussion and hence no decision made,
although one might argue that the optimizer should be clever enough to
make hints unnecessary. The PGSQL guys share the same opinion, BTW ;-)
As for m
34 matches
Mail list logo
