Hello,

Why does an inner join to a selectable stored procedure cache the
stored procedure first then execute the inner join
and on a left join it executes the the left side of the join then for
each row on the left it executes the stored procedure.

Is this desired behavior of the query planner? If it is how can I
change it, or am I doing something wrong?

The left join is the query we need to work since that will give us all
profiles and we can determine activity against
accounts. We are trying to apply the KISS principle here, but will
Frebird cause us to create job security but creating a query from hell.

Here are the stats and the queries and at the end is the stored procedure:

Query (LEFT JOIN) Stats
------------------------------------------------
SELECT
PROFILE.name,
SP_ROLLUP_TEST.*
FROM
PROFILE
LEFT JOIN SP_ROLLUP_TEST('2004-06-01', '2004-06-04') ON
(profile.profileno = sp_rollup_test.profileno )


Plan
------------------------------------------------
PLAN JOIN (PROFILE NATURAL,SORT (TICKET_DATA INDEX (TICKET_DATA_IDX4)))

Adapted Plan
------------------------------------------------
PLAN JOIN (PROFILE NATURAL,SORT (TICKET_DATA INDEX (TICKET_DATA_IDX4)))

Query Time
------------------------------------------------
Prepare : 0.00 ms
Execute : 73,145.00 ms
Avg fetch time: 43.72 ms

Memory
------------------------------------------------
Current: 2,127,320
Max : 4,221,420
Buffers: 2,048

Operations
------------------------------------------------
Read : 41
Writes : 0
Fetches: 8,930,959

******************************************************************************************

Query (INNER JOIN) Stats
------------------------------------------------
SELECT
PROFILE.name,
SP_ROLLUP_TEST.*
FROM
PROFILE
INNER JOIN SP_ROLLUP_TEST('2004-06-01', '2004-06-04') ON
(profile.profileno = sp_rollup_test.profileno )


Plan
------------------------------------------------
PLAN JOIN (SORT (TICKET_DATA INDEX (TICKET_DATA_IDX4)),PROFILE INDEX
(PK_PROFILE))

Adapted Plan
------------------------------------------------
PLAN JOIN (SORT (TICKET_DATA INDEX (TICKET_DATA_IDX4)),PROFILE INDEX
(PK_PROFILE))

Query Time
------------------------------------------------
Prepare : 0.00 ms
Execute : 60.00 ms
Avg fetch time: 0.27 ms

Memory
------------------------------------------------
Current: 2,127,320
Max : 4,221,420
Buffers: 2,048

Operations
------------------------------------------------
Read : 0
Writes : 0
Fetches: 6,912

********************************************************************************

Selectable Stored Procedure:

CREATE PROCEDURE SP_ROLLUP_TEST ( START_DATE DATE, END_DATE DATE)
RETURNS ( PROFILENO INTEGER, TRAVEL_CATEGORY_ID VARCHAR(128),
TOTALFARE DOUBLE PRECISION)
AS
BEGIN
FOR SELECT
TICKET_DATA.CLIENT_LINKNO,
TICKET_DATA.UDID,
SUM(TICKET_DATA.TOTALFARE)
FROM
TICKET_DATA
WHERE
TICKET_DATA.ISSUEDATE BETWEEN :START_DATE AND :END_DATE
GROUP BY
TICKET_DATA.CLIENT_LINKNO,
TICKET_DATA.UDID
INTO :PROFILENO, :TRAVEL_CATEGORY_ID, :TOTALFARE
DO BEGIN
SUSPEND;
END
END


Sorry about the long email.

TIA
-kurt