Hi all,

I need HELP on optmising this very critical query in my system :

I have a stored procedure that joins two tables(TRAF_ORG and
TRAF_TER) and then looks up a third table (CASCADEID)
for some mappings. When I execute the procedure, I see that the plan
used by IB is this :

PLAN (CASCADEID INDEX (RDB$63))JOIN (TRAF_ORG ORDER RDB$PRIMARY25,TRAF_TER
INDEX (RDB$PRIMARY26))JOIN (TRAF_ORG ORDER RDB$PRIMARY25,TRAF_TER INDEX
(RDB$PRIMARY26))


cascadeid is the third table I use and its a small table, how can I force
the join for the third table to happen last ?
I am attaching the stored proc definition also :

ALTER PROCEDURE "CHART_100"
(
"PMDATESTART" VARCHAR(10),
"PMDATEEND" VARCHAR(10),
"CASCADEIDV" VARCHAR(10),
"SECTORV" INTEGER,
"FAV" INTEGER
)
RETURNS
(
"PMDATE" INTEGER,
"ATTEMPTS" NUMERIC(15, 2)
)
AS
DECLARE VARIABLE BSC INT ;
DECLARE VARIABLE BTS INT ;
DECLARE VARIABLE TRAF_ORG_ATT_VOC FLOAT ;
DECLARE VARIABLE TRAF_ORG_ATT_SMS FLOAT ;
DECLARE VARIABLE TRAF_TER_ATT_VOC FLOAT ;
DECLARE VARIABLE TRAF_ORG_ATT_DAT FLOAT ;
DECLARE VARIABLE TRAF_TER_ASR_SMS FLOAT ;
DECLARE VARIABLE TRAF_TER_ATT_DAT FLOAT ;
BEGIN

SELECT BSC, BTS FROM CASCADEID WHERE CASCADEID= :CASCADEIDV INTO :BSC,
:BTS;

FOR SELECT TRAF_ORG.PMDATE, SUM(TRAF_ORG.ATT_VOC), SUM(TRAF_ORG.ATT_SMS),
SUM(TRAF_TER.ATT_VOC), SUM(TRAF_ORG.ATT_DAT), SUM(TRAF_TER.ASR_SMS),
SUM(TRAF_TER.ATT_DAT)
FROM TRAF_ORG JOIN TRAF_TER ON (TRAF_ORG.BSC = TRAF_TER.BSC ) AND
(TRAF_ORG.BTS = TRAF_TER.BTS ) AND (TRAF_ORG.SECTOR = TRAF_TER.SECTOR )
AND (TRAF_ORG.FA = TRAF_TER.FA ) AND (TRAF_ORG.PMDATE = TRAF_TER.PMDATE )
AND (TRAF_ORG.PMHOUR = TRAF_TER.PMHOUR )
WHERE TRAF_ORG.PMDATE BETWEEN :PMDATESTART AND :PMDATEEND AND
TRAF_ORG.PMHOUR=24 AND TRAF_ORG.SECTOR IN (SELECT INDEXLIST FROM
GETSELECTEDINDEX(:SECTORV , 3)) AND TRAF_ORG.FA IN (SELECT INDEXLIST FROM
GETSELECTEDINDEX(:FAV , 12))
GROUP BY TRAF_ORG.PMDATE
INTO :PMDATE, :TRAF_ORG_ATT_VOC, :TRAF_ORG_ATT_SMS, :TRAF_TER_ATT_VOC,
:TRAF_ORG_ATT_DAT, :TRAF_TER_ASR_SMS, :TRAF_TER_ATT_DAT DO BEGIN ATTEMPTS
= TRAF_ORG_ATT_VOC + TRAF_ORG_ATT_DAT + TRAF_ORG_ATT_SMS +
TRAF_TER_ATT_VOC + TRAF_TER_ATT_DAT + TRAF_TER_ASR_SMS;

SUSPEND; END END ^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;

TIA,
amrita .


[Non-text portions of this message have been removed]