A simple multi-verb request takes over 80 minutes to execute, while a similar single-verb request takes about 5 minutes to execute. Both process the same number of millions of rows. Any ideas on why this is occurring?

Multi-verb request:

JOIN DATE1    IN FILE1 TO DATE     IN TIMEDIM AS J1
JOIN CATEGORY IN FILE1 TO CATEGORY IN CCAT    AS J2
-RUN
-IF &FOCERRNUM NE 0 GOTO FOC_ERROR;

DEFINE FILE FILE1
AMOUNT1/P17.2BC =
  IF TTYPE EQ 'Dumpty' THEN AMT_IN * DAYS_IN_MTH ELSE AMT_IN;
END
-RUN
-IF &FOCERRNUM NE 0 GOTO FOC_ERROR;

TABLE FILE FILE1
SUM
LST.DAYS_IN_FQTM NOPRINT
BY FISCALYQ

SUM
COMPUTE AMOUNT/P17.2BC = IF TTYPE EQ 'Dumpty'
  THEN AMOUNT1 / LST.DAYS_IN_FQTM ELSE AMOUNT1; AS AMT_IN

BY FISCALYQ AS DATE1
BY PROVINCE
BY CATEGORY

ON TABLE HOLD AS H001
END

Single-verb request:

JOIN DATE1    IN FILE1 TO DATE     IN TIMEDIM AS J1
JOIN CATEGORY IN FILE1 TO CATEGORY IN CCAT    AS J2
-RUN
-IF &FOCERRNUM NE 0 GOTO FOC_ERROR;

DEFINE FILE FILE1
AMOUNT1/P17.2BC =
  IF TTYPE EQ 'Dumpty' THEN AMT_IN * DAYS_IN_MTH ELSE AMT_IN;
DAYS_IN_FQT/I4 =
  IF DATE EQ '&CURR_DATE' THEN &DAYS_IN_FQTM ELSE DAYS_IN_FQTR;
END
-RUN
-IF &FOCERRNUM NE 0 GOTO FOC_ERROR;

TABLE FILE FILE1
SUM
COMPUTE AMOUNT/P17.2BC = IF TTYPE EQ 'Dumpty'
  THEN AMOUNT1 / LST.DAYS_IN_FQT ELSE AMOUNT1; AS AMT_IN

BY FISCALYQ AS DATE1
BY PROVINCE
BY CATEGORY

ON TABLE HOLD AS H001
END

Actually, it now seems both take 88 minutes, this seems to be due to the LST.

Francis

I know that MATCH FILE usually is not running at the speed of light, but have you tried running two two request and then matching the output from these? It would most likely be slower than a single-verb request, but if can cut the time from the original 80 minutes, it could be worth a try.

Håkan

Håkan, thanks for the suggestion. I've been debugging this mainframe fex for days. It now appears to me that in the single-verb request, I had changed the order of the BY statements and that is what's increasing the processing time.

I'm working with a huge database and during the multi-fex load process there are no indexes which may explain why the order of the BY statements is important.