Fine tuning UNION ALL query

Satish5 · Posted: Thu Jun 25, 2009 9:16 am

Hi,
I am getting timeout S322(after 5 minutes of CPU time) on following query in a program. Thru SPUFI I found that result set will have over a million records. Please suggest a solution so that I can run program in lesser time without time abend. Here I am talking about an easytrieve program in which following query is given in JOB statement i.e. data from this query is driving the program. In addition to this there are 3 more simple select queries, fetching 2-3 fields. Please let me know if more information is required.

SELECT
A.CORP_CODE ,
B.IC_ITEM_NO ,
B.IC_ITEM_DESC_1
FROM
XXXBLHD A ,XXXBLLN B
WHERE A.CORP_CODE = B.CORP_CODE
AND A.CUST_AR_OFFICE_CODE = B.CUST_AR_OFFICE_CODE
AND A.SALES_OFFICE = B.SALES_OFFICE
AND A.SALES_ORDR_NO = B.SALES_ORDR_NO
AND A.INVOICE_NO = B.INVOICE_NO
AND A.CUST_TYPE_CODE <> '06'
AND A.WAREHOUSE_NUMBER NOT IN('01','99')
AND A.INVOICE_DATE >= :W-RUN-DATE-2

UNION ALL

SELECT
C.CORP_CODE ,
D.IC_ITEM_NO ,
D.IC_ITEM_DESC_1
FROM
ARCBLHD C ,ARCBLLN D
WHERE C.CORP_CODE = D.CORP_CODE
AND C.CUST_AR_OFFICE_CODE = D.CUST_AR_OFFICE_CODE
AND C.SALES_OFFICE = D.SALES_OFFICE
AND C.SALES_ORDR_NO = D.SALES_ORDR_NO
AND C.INVOICE_NO = D.INVOICE_NO
AND C.CUST_TYPE_CODE <> '06'
AND C.WAREHOUSE_NUMBER NOT IN('01','99')
AND C.INVOICE_DATE >= :W-RUN-DATE-2
ORDER BY
CORP_CODE ,
CUST_AR_OFFICE_CODE ,
WAREHOUSE_NUMBER ,
IC_ITEM_NO ,
SALES_ORDR_NO ,
INVOICE_NO
INTO
:BLD-CORP-CODE ,
:BLN-IC-ITEM-NO ,
:BLN-IC-ITEM-DESC-1

Thanks,
Satish

dick scherrer · Posted: Thu Jun 25, 2009 9:22 am

Hello,

Suggest you redesign the process so that a million rows is not returned for a single query. . .

Just because something like this can be written and executed, does not mean it should be written and run.

Satish5 · Posted: Thu Jun 25, 2009 9:52 am

I thought about that redesigning.
Break the whole task into 4 steps

1. Run query on XXXBLHD and XXXBLLN and write the result data into a tempvsam.
2. Run query on ARCBLHD and ARCBLLNand APPEND the result data to tempvsam created in previous step.
3. sort tempvsam on required fields.
4. read tempvsam into program and do the processing and prinht report

Here I have a doubt. Tempvsam will contain over one million records and reading that sequentailly might again result into time abend.

Please share your comments on this approach and if you have a better approach then please do share that.

Thanks,
Satish

dick scherrer · Posted: Thu Jun 25, 2009 9:58 pm

Hello,

The creation and a million inserts into a vsam file would add undesired overhead - not counting the actual read later.

I'd experiment with writing temp qsam files in step 1 & 2. Then, i'd sort them together by the "key". Then, i'd read the sorted temo qsam file do whatever processing/reporting is required.

ashimer · Posted: Fri Jun 26, 2009 10:22 pm

As a turnaround solution ...

your conditions

AND C.CUST_TYPE_CODE <> '06'
AND C.WAREHOUSE_NUMBER NOT IN('01','99')
AND C.INVOICE_DATE >= :W-RUN-DATE-2

is common in both select queries ... remove this and put this outside and inline view ... the view should have only the select queries with join ... this suggestion is because of non -equal conditions which normally does not help in a proper index scan ( assuming one of these cols are part of your index )
wht is the order by doing in your second select ?

Satish5 · Posted: Wed Jul 01, 2009 9:10 am

The main problem was that a table was accessed on partial key using a cursor and that table was an archive table contaning approx. 15 Million recs. I wrote a batch SPUFI and extracted records on some conditions from that archive table into a tempvsam(less than 1 million recs qualified for result set) and then read that tempvsam in program. This reduced the time significantly and job executed in just over a minute of CPU time.

Thanks Dick Scherrer and Ashimer for your help.

Regards,
Satish

dick scherrer · Posted: Wed Jul 01, 2009 9:31 am

You're welcome - thank you for posting how you modified the process to improve the performance

d