>
>
>>It looks that procedure does not cache result set, but strangely somehow
>>recalculates it on runtime.
>>
>>
>
>Exactly. A stored procedure runs in its entirety each time it is
>called. So you should pass input parameters that are used in a WHERE
>clause inside the SP to restrict the number of rows that the procedure has
>to touch.
>
>./heLen
>
>
>
>

The point is , that procedure itself does more reads, than is expected.
For example proc P1 :
After I called P1, apears dataset DP1 with count reads from tables
for example 100.000, and result rows =10;
If I wish to order it (select * from P1 order by xxx ), it should order
dataset DP1, and reads should remain 100.000.


But instead it creates dataset DP2 , with reads > 250.000 !
This is the point, that stored procedure with order by clause causes big
OVERHEAD, and there is no escape from that.

Adomas Urbanavicius