Hi,

i have an experimental stored procedure GETACTIONS with the
following simple query in it

FOR
SELECT
A.ID
FROM ACTIONS A
INNER JOIN PRIORITIES PR ON (A.PRIORITYID = PR.ID)
INNER JOIN STATUSES S ON (A.STATUSID = S.ID)
INNER JOIN USRS U ON A.CREATORID = U.ID
ORDER BY A.ID
INTO
:ACTIONID
DO
...

when i execute the procedure i get the following performance info
which is fine.


----- Performance info ------
Prepare time = 20ms
Execute time = 50ms

all the reads from the tables are indexed.

As i add left join to the query

FOR
SELECT
A.ID
FROM ACTIONS A
INNER JOIN PRIORITIES PR ON (A.PRIORITYID = PR.ID)
INNER JOIN STATUSES S ON (A.STATUSID = S.ID)
INNER JOIN USRS U ON A.CREATORID = U.ID
left outer join CONTACTS C ON A.CONTACTID = C.ID
ORDER BY A.ID
INTO
:ACTIONID
DO
...

the performance info was

------ Performance info ------
Prepare time = 10ms
Execute time = 170ms

and i become to have non-indexed reads from the Actions table. Every
left join i add to the query leads to even more serios performance
issues. On the 5 left join i have 2 sec exec time, and this is
serious speed penalty.

I'll be glad if somebody can help me in anyway in this problem and
to give me solutions and advices.

BEst wishes, Stanislav Stratiev