Hi Michael,

Thanks for taking the time to respond.

I find this extremely useful for Smart/Client development. One of the
obvious benefits is the Data Packet that flows back to the Client, its
much smaller than what one would have returned in normal circumstances.

Personally I do not make use of any order clauses in the Web Service
side, I leave the ordering of data to the Client Side. Further I try to
always supply some form of "where" clause to further limit the output
and increase the execution speed on the Web Server Side.

For interest sake, the application I am working on at the moment,
provides the feel of a typical Client/Server solution in terms of
response times, and that is accessing some web services hosted on a
dedicated Firebird Server in the cloud. Granted I am connected to the
internet with a 4Meg ADSL line.

Regards
Marius

On 20/12/2010 11:41, Michael Ludwig wrote:
>
> 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
>


[Non-text portions of this message have been removed]