At 12:25 PM 22/11/2004 +0200, you wrote:

>Hello all,
>
>I ' ve noticed interesting , kind of disapointing ordering feature in
>stored proc :
>if I have stored proc something like :
>
>P_NAMES
>
>for select name,surname from people into :name,:surname do suspend;
>
>
>and make from it selection :
>select * from P_NAMES, in statistics I get reads from table PEOPLE ~
>1.00.000, but if I make
>selection with ordering
>select * from P_NAMES order by NAME,
>in statistics I get reads~ 290.000, this means it slows down performance
>about 3x!

Don't order the output of a SP, do the ordering inside it. This way,
indexes can be used:

for select name,surname from people
order by surname, name
into :name,:surname do suspend;

>Same is with much more compilcated procedures, whee I must get output
>only about 100 records, but must scan about 2.000.000 records, then
>perfomance significantly drops down.

For selectable SPs operating on huge sets, you should define input
arguments as WHERE criteria for the FOR...SELECT statement, so that you can
restrict the size of the sets.

SPs are not views, so don't define SPs with the intention of selecting them
with WHERE, ORDER BY or GROUP BY clauses. You will cause the entire set to
be generated and this *will* be slow.

In fact, for the above example, a view selected with an ORDER BY clause, or
just a simple dsql statement, will be much faster. Don't use SSPs to
replace what DSQL does easily.

./heLen