Hello,

I have a report that runs within 30 seconds when I select a single Period 'yyyymm', but if I try to run a range of periods like 3 periods it takes 10 minutes to run. Now now... I do expect the answer to be 'YOU NEED TO KNOW HOW TO PROGRAM BETTER'. But sh$%#$ the only difference is a range.

****THE REEAALLYY SLOW CODE:
JOIN
INNER BPDETFOC.SEG01.GRP IN BPDETFOC TO UNIQUE
PGRPFOC.SEG01.KEY IN PGRPFOC AS J0
END
JOIN
INNER BPDETFOC.SEG01.PLANT IN BPDETFOC TO UNIQUE
LOCFOC.SEG01.LOCATION_KEY IN LOCFOC AS J1
END
JOIN
INNER LOCFOC.SEG01.LOCATION_KEY IN BPDETFOC
TO UNIQUE BUSFOC.SEG01.BUSINESS_KEY IN BUSFOC AS J2
END
JOIN
INNER BPDETFOC.SEG01.COMPANY IN BPDETFOC TO UNIQUE
COMPFOC.SEG01.COMPANY_CODE IN COMPFOC AS J3
END
-*
DEFINE FILE BPDETFOC
COMPANY/A32 = COMPFOC.SEG01.COMPANY_CODE | ' - ' | COMPFOC.SEG01.COMPANY_DESCRIPTION;
YRMO/YYM=BPDETFOC.SEG01.DATE;
END
SET ASNAMES = ON
TABLE FILE BPDETFOC
PRINT
'BUSFOC.SEG01.CORPORATE'
'BUSFOC.SEG01.DIVISION'
'BUSFOC.SEG01.BUSINESS'
'BUSFOC.SEG01.PLANT_NAME_FOR_DASHBOARD' AS 'BUS_PLANT_NAME'
'LOCFOC.SEG01.REGION'
'LOCFOC.SEG01.COUNTRY' AS 'LOC_COUNTRY'
'LOCFOC.SEG01.AREA'
'LOCFOC.SEG01.PLANT_NAME_FOR_DASHBOARD' AS 'LOC_PLANT_NAME'
'COMPFOC.SEG01.COMPANY'
'COMPFOC.SEG01.COUNTRY' AS 'COM_COUNTRY'
'BPDETFOC.SEG01.HEADER_TEXT'
'BPDETFOC.SEG01.VENDOR'
'BPDETFOC.SEG01.VENDOR_INDUSTRY'
'BPDETFOC.SEG01.DESCRIPTION'
'BPDETFOC.SEG01.DOC_NUMBER'
'BPDETFOC.SEG01.DATE'
'BPDETFOC.SEG01.AMOUNT'
'BPDETFOC.SEG01.AMOUNT_CURRENCY'
'BPDETFOC.SEG01.BLOCKED_PRICE'
'BPDETFOC.SEG01.BLOCKED_QUANTITY'
'BPDETFOC.SEG01.BLOCKED_DATE'
'BPDETFOC.SEG01.INVOICE_DATE'
'BPDETFOC.SEG01.ENTRY_DATE'
'BPDETFOC.SEG01.DUE_DATE'
'PGRPFOC.SEG01.NAME'
-*----------------------------------------
-IF &RPTTYPE EQ 'PRK' THEN GOTO PARKED;
WHERE BPDETFOC.SEG01.BLK EQ 'X';
-GOTO ENDPRKBLK;
-*
-PARKED
WHERE BPDETFOC.SEG01.PRK EQ 'X';
-ENDPRKBLK
-*----------------------------------------
WHERE YRMO GE &YRMO_START AND YRMO LE &YRMO_END; <<<******* MY CHANGE



****THE REALLY FAST CODE IS*****

WHERE YRMO EQ &YRMO;

This message has been edited. Last edited by: Kerry,

Firstly, on your last report, do a COUNT * WHERE range-condition to see how many rows you are trying to bring back. The more rows, the longer it takes.

Secondly, it wouldn't hurt to trace your program and check out the generated SQL.

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
SET XRETRIEVAL=OFF

The XRETRIEVAL will keep the program from running and just parse the SQL. If you get more than one SQL statement or a FOCUS-managed join, etc., that will be where your problem is. Please post your results.

Or is this a FOCUS file? If yes, please post your master. You might be screening on a low-level segment.

I bet the data field is not getting passed to the DBMS because it is a define to YYM which I do not believe is getting passed.

Hello Ginny, BPDETFOC is a focus file... the master file is:

FILENAME=BPDETFOC, SUFFIX=FOC , $
SEGMENT=SEG01, SEGTYPE=S5, $
FIELDNAME=DATE, ALIAS=E01, USAGE=YYMD, FIELDTYPE=I, $
FIELDNAME=COMPANY, ALIAS=E02, USAGE=A4, $
FIELDNAME=PLANT, ALIAS=E03, USAGE=A4, $
FIELDNAME=GRP, ALIAS=E04, USAGE=A3, $
FIELDNAME=FOCLIST , ALIAS=E00, USAGE=I5, $
FIELDNAME=DOC_NUMBER, ALIAS=E05, USAGE=A10, $
FIELDNAME=HEADER_TEXT, ALIAS=E06, USAGE=A25, $
FIELDNAME=VENDOR, ALIAS=E07, USAGE=A10, $
FIELDNAME=DESCRIPTION, ALIAS=E08, USAGE=A35, $
FIELDNAME=VENDOR_INDUSTRY, ALIAS=E09, USAGE=A20, $
FIELDNAME=AMOUNT, ALIAS=E10, USAGE=P10.2, $
FIELDNAME=AMOUNT_CURRENCY, ALIAS=E11, USAGE=A5, $
FIELDNAME=PRK, ALIAS=E12, USAGE=A1, $
FIELDNAME=BLK, ALIAS=E13, USAGE=A1, $
FIELDNAME=BLOCKED_PRICE, ALIAS=E14, USAGE=A1, $
FIELDNAME=BLOCKED_QUANTITY, ALIAS=E15, USAGE=A1, $
FIELDNAME=BLOCKED_DATE, ALIAS=E16, USAGE=A1, $
FIELDNAME=INVOICE_DATE, ALIAS=E17, USAGE=YYMD, $
FIELDNAME=ENTRY_DATE, ALIAS=E18, USAGE=YYMD, $
FIELDNAME=DUE_DATE, ALIAS=E19, USAGE=YYMD, $

I ran the trace... I did not get data as you said... but I did not get any code in the output either.

Crack the joins in half and take two passes at your. Likely even your single date value will be faster.

J.

Might want to break the date out into a separate segment too.

The problem is that the index on DATE cannot be used because the WHERE clause references a DEFINEd portion of DATE. Try manipulting your start and end variables so they include a complete YYMD date and change your WHERE clause to test against those complete dates.

quote:

WHERE YRMO GE &YRMO_START AND YRMO LE &YRMO_END;

try:

WHERE YRMO GE &YRMO_START;
WHERE YRMO LE &YRMO_END;

You have two DM variables with the starting and ending dates. I would append 01 to them to create proper YYMD dates and use them in the WHERE statement. I avoid DEFINE based WHERE statements as much as possible.

-SET &YYMD_START = &YRMO_START | '01';
-SET &YYMD_END   = &YRMO_END | '01';

WHERE BPDETFOC.SEG01.DATE GE &YYMD_START AND BPDETFOC.SEG01.DATE LT &YYMD_END;

(Note the LT instead of the LE)