On 2-1-2014 19:37, Jim Starkey wrote: > I presume everyone knows about standard SQL "select ... OFFSET <n> ROWS > ... FETCH <n> 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 row (or maybe a set of rows) from the open cursor, do some processing and then retrieve the next row (or set of rows), etc. This can increase throughput because while my application is processing the row(s), the server might continue to fill the cursor; instead of the application having to wait until the server has materialized the entire result set. > I believe that gives the optimizer everything it could want to know. > > My experience over 30 years is that giving the optimizer hints generally > halts improvements to the optimizer. Depending on the optimizer to > optimize is the only motivation to improve it. Building in optimizer > hints generally means you've thrown in the towel. Sometimes it is not about optimizing the server side, but optimizing for how the client will actually process the rows. And sometimes you don't need all rows, but you also don't know in advance how many rows you will need (the stop condition might depend on a specific value, or on checks with a different sub-system). Being able to say 'optimize for retrieving the first x rows' allows the server to select an execution plan that might not be the most efficient for retrieving all rows (at once), but is better from performance or throughput of the client. 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
