Marius Labuschagne schrieb am 20.12.2010 um 08:38 (+0200):

>
> I want to find out from the experts what the effect of ROWS X TO Y is
> on the speed of a query.

I don't qualify as an expert but here's my take on it anyway. First,
this is documented here:

http://www.firebirdsql.org/refdocs/langrefupd21-select.html#langrefupd21-select-rows

> In my tests it seems that when implementing this, you gain significant
> speed increases in the query execution speed, but I don't want to just
> believe my results.
>
> When implementing a query like the following
>
> select SKU, ShortDesc
> from Stock
>
> vs
>
> select SKU, ShortDesc
> from Stock
> ROWS 1 TO 30
>
> on a technical level, is the second query really supposed to be much
> faster than the first?

I would say that you have reason to assume that the second query might
be faster because you don't specify an ORDER BY clause. The engine
can thus start returning rows immediately. In addition, you're selecting
the entire table in your first query and only 30 rows in the second one,
increasing the likelihood of the second query being faster because there
will be less data to transfer to the client.

> I make use of this in the development of a Smart/Client application
> with Client Data Sets, and am utilizing the ROWS x TO y to accomplish
> pagination on the Smart/Client application.

Okay, but without order? You could also have the smart client fetch and
cache the entire set but limited to the rows you want it to be able to
order by, and then fetch by id column based on the page the client is
navigating to.

> Logically it seems that the query needs to be executed in full, before
> a result set is returned, but looking at my performance tests, it
> looks like the query stops its execution as soon as it has satisfied
> the ROWS stipulation.

I think the engine detects that it doesn't need the whole set and hence
starts returning rows immediately.

--
Michael Ludwig