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"? If it's about incomplete fetch,
> i.e. only few rows are fetched and then the cursor is closed, then your
> assumption is likely to be correct.

That as well, but what I meant is the use 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
...

With a hint it could work like:
Client: execute query optimize for first row
Server materializes 1 row
Client: fetch size=1
Server: returns row 1
(and continues materializing all rows)
Client: fetch size=1
Server: returns row 2
...

>> I'd actually prefer comments like Oracle does. I think it is better for
>> portability.
>
> Given e.g. FIRST vs TOP vs ROW_NUMBER mess developed by different
> vendors, I wouldn't care much about cross-database portability. Hints
> are outside the SQL standard, but many other features are not there
> either. I hope you don't suggest to implement everything non-standard in
> the comments, do you?

No, but I think optimizer hints are not part of the query, but a form of 
metadata, and having it as part of the query syntax feels 'wrong' somehow.

> SQL is a declarative language, so let's allow users to declare what they
> need using the language itself.

If it needs to be part of the query syntax, I'd prefer something similar 
to SQL Servers OPTION-clause: 
http://msdn.microsoft.com/en-us/library/ms190322.aspx and 
http://msdn.microsoft.com/en-us/library/ms181714.aspx

To me it seems to be more extensible, and might also be easier for the 
parser (especially if more options are added), and - with some changes - 
the current PLAN syntax could be integrated into that as well.

>> 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.
>
> Comments throwing exceptions from inside is not the feature I'm going to
> be an author of, sorry ;-)

A warning that the optimizer hint was ignored because it has a syntax 
error then :P

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

Reply via email to