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