Steve Summers wrote:

>I'm not sure if this is a quick question or not.
>
>If I have a fairly complex select with multiple joins, is there an
>advantage to creating a stored procedure to return its result set,
>rather than just creating a view?
>
>

Hi, Steves !

That's depend on how you create your SP.

I you have this query/view

Select
*
from
TableA join
TableB on (Tableb.SomeColumn = TableA.SomeColumn) left join
TableC on (TableC.OtherColumn = TableA.OtherColumn)

and a SP like

create procedure MySP returns bla, bla bla
begin
for
Select
*
from
TableA join
TableB on (Tableb.SomeColumn = TableA.SomeColumn) left join
TableC on (TableC.OtherColumn = TableA.OtherColumn)
do
Suspend;
end

the both will have similar performance.

If you could separate the parts of the query, and put logic in your SP
to do some calcs (aggregate fields) running for each record of the main
"for", create another "for" to handle left joins, etc. in other words
change the way the query was written, then you could have a good gain in
performance, maybe not... that depends....

The best answer is:

Try alternatives !

or

Post here your query and the generated plan, and some info about the
table/indices involved and someone could help you in more especific way.

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br