Alan,

A> SELECT FIRST X SKIP Y * FROM SP_NAME(PARAM)

A> Now can someone tell me if the engine does indeed work out from the SP how
A> to do such an efficient read as in the first example, even when the body of
A> the SP can be quite complicated? Or does the engine return all records to
A> the SP, and then the gap of records to the client?
A> From looking at the Performance Analysis, it would suggest that the engine
A> does indeed short circuit the SP and read only the FIRST X records. This is
A> not just an anomaly is it?

AFAIU it keeps fetching records from the SP as long as it gets the
needed amount, that is if you select the first 3 records then the SP
code is executed until the third suspend statement call.

Suppose your SP returns 1000 record; if my understanding is correct
then

select first 100 skip 900 * from sp(param)

and

select * from sp(param)

should perform approximately the same.
Ciao
--
Nando mailto:nandod@...