Slow performance in SELECT with joins of multiple table
Hi Guys
Can any one of you help me in improving the performance of the query:
SELECT DISTINCT A.BUSINESS_UNIT ,A.PROJECT_ID ,A.ACTIVITY_ID
,A.RESOURCE_ID
,A.RESOURCE_ID_FROM ,A.BUSINESS_UNIT_GL ,A.JOURNAL_ID
,A.JOURNAL_DATE
,A.UNPOST_SEQ ,A.JOURNAL_LINE ,B.FISCAL_YEAR ,B.ACCOUNTING_PERIOD
,A.ACCOUNT ,A.ALTACCT , A.DEPTID --,%subrec(CF16_AN_SBR, A)
,A.BUS_UNIT_GL_FROM ,A.CURRENCY_CD ,A.STATISTICS_CODE
,A.LEDGER_GROUP
,A.ANALYSIS_TYPE ,A.RESOURCE_TYPE ,A.RESOURCE_CATEGORY
,A.RESOURCE_SUB_CAT
,A.RES_USER1 ,A.RES_USER2 ,A.RES_USER3 ,A.RES_USER4 ,A.RES_USER5
,A.TRANS_DT
,A.ACCOUNTING_DT ,A.OPRID ,A.DTTM_STAMP ,A.JRNL_LN_REF
,A.OPEN_ITEM_STATUS ,A.LINE_DESCR
,A.JRNL_LINE_STATUS ,A.JOURNAL_LINE_DATE ,A.FOREIGN_CURRENCY
,A.RT_TYPE ,A.FOREIGN_AMOUNT
,A.RATE_MULT ,A.RATE_DIV ,A.CUR_EFFDT ,A.PROCESS_INSTANCE
,A.PC_DISTRIB_STATUS ,A.GL_DISTRIB_STATUS
,A.PROJ_TRANS_TYPE ,A.PROJ_TRANS_CODE ,A.RESOURCE_STATUS ,A.DESCR
,A.SYSTEM_SOURCE ,A.UNIT_OF_MEASURE
,A.EMPLID ,A.EMPL_RCD ,A.SEQ_NBR ,A.TIME_RPTG_CD ,A.JOBCODE
,A.COMPANY ,A.BUSINESS_UNIT_AP ,A.VENDOR_ID
,A.VOUCHER_ID ,A.VOUCHER_LINE_NUM ,A.APPL_JRNL_ID ,A.PYMNT_CNT
,A.DST_ACCT_TYPE ,A.PO_DISTRIB_STATUS
,A.BUSINESS_UNIT_PO ,A.REQ_ID ,A.REQ_LINE_NBR ,A.REQ_SCHED_NBR
,A.REQ_DISTRIB_NBR ,A.PO_ID
,A.DUE_DATE ,A.LINE_NBR ,A.SCHED_NBR ,A.DISTRIB_LINE_NUM
,A.AM_DISTRIB_STATUS ,A.BUSINESS_UNIT_AM
,A.ASSET_ID ,A.PROFILE_ID ,A.COST_TYPE ,A.BOOK ,A.INCENTIVE_ID
,A.MSTONE_SEQ ,A.CONTRACT_NUM
,A.CONTRACT_LINE_NUM ,A.CONTRACT_PPD_SEQ ,A.BI_DISTRIB_STATUS
,A.BUSINESS_UNIT_BI ,A.BILLING_DATE
,A.INVOICE ,A.REV_DISTRIB_STATUS ,A.BUSINESS_UNIT_AR ,A.CUST_ID
,A.ITEM ,A.ITEM_LINE ,A.ITEM_SEQ_NUM ,A.DST_SEQ_NUM
,A.BUSINESS_UNIT_IN ,A.SCHED_LINE_NO ,A.DEMAND_LINE_NO
,A.INV_ITEM_ID ,A.PAY_END_DT ,A.BUSINESS_UNIT_OM ,A.ORDER_NO
,A.ORDER_INT_LINE_NO ,A.EX_DOC_ID ,A.EX_DOC_TYPE
,A.RESOURCE_QUANTITY ,A.RESOURCE_AMOUNT ,A.BUDGET_HDR_STATUS
,A.KK_AMOUNT_TYPE ,A.KK_TRAN_OVER_FLAG ,A.KK_TRAN_OVER_OPRID
,A.KK_TRAN_OVER_DTTM ,A.BUDGET_OVER_ALLOW
,A.BUDGET_LINE_STATUS ,A.BUDGET_DT ,A.LEDGER ,A.BD_DISTRIB_STATUS
,A.BUSINESS_UNIT_BD ,A.FA_STATUS ,A.TIME_SHEET_ID
,A.SHEET_ID ,A.DT_TIMESTAMP ,A.VCHR_DIST_LINE_NUM ,A.PM_REVIEWED
,A.PRICED_RATE
FROM PS_PROJ_RESOURCE A , PS_CAL_DETP_TBL B , PS_BUS_UNIT_TBL_PC C
, PS_SET_CNTRL_REC D
WHERE A.ACCOUNTING_DT BETWEEN B.BEGIN_DT AND B.END_DT
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND C.CALENDAR_ID = B.CALENDAR_ID
AND A.BUSINESS_UNIT = D.SETCNTRLVALUE
AND D.RECNAME = 'CAL_DEFN_TBL'
AND B.SETID = D.SETID
I tried doing it by putting into subqueries with EXISTS ,but seems to
be EXISTS makes the performance poor.
Please help me.
Thanks
how about .....UPDATE STATISTICS;
set explain on;
<do the select>
will provide the sql execution plan which can be helpfull in this case
together with
the # of rows for each tables etc.
Superboer.
nita schreef:
> Hi Guys
> Can any one of you help me in improving the performance of the query:
>
> SELECT DISTINCT A.BUSINESS_UNIT ,A.PROJECT_ID ,A.ACTIVITY_ID
> ,A.RESOURCE_ID
> ,A.RESOURCE_ID_FROM ,A.BUSINESS_UNIT_GL ,A.JOURNAL_ID
> ,A.JOURNAL_DATE
> ,A.UNPOST_SEQ ,A.JOURNAL_LINE ,B.FISCAL_YEAR ,B.ACCOUNTING_PERIOD
> ,A.ACCOUNT ,A.ALTACCT , A.DEPTID --,%subrec(CF16_AN_SBR, A)
> ,A.BUS_UNIT_GL_FROM ,A.CURRENCY_CD ,A.STATISTICS_CODE
> ,A.LEDGER_GROUP
> ,A.ANALYSIS_TYPE ,A.RESOURCE_TYPE ,A.RESOURCE_CATEGORY
> ,A.RESOURCE_SUB_CAT
> ,A.RES_USER1 ,A.RES_USER2 ,A.RES_USER3 ,A.RES_USER4 ,A.RES_USER5
> ,A.TRANS_DT
> ,A.ACCOUNTING_DT ,A.OPRID ,A.DTTM_STAMP ,A.JRNL_LN_REF
> ,A.OPEN_ITEM_STATUS ,A.LINE_DESCR
> ,A.JRNL_LINE_STATUS ,A.JOURNAL_LINE_DATE ,A.FOREIGN_CURRENCY
> ,A.RT_TYPE ,A.FOREIGN_AMOUNT
> ,A.RATE_MULT ,A.RATE_DIV ,A.CUR_EFFDT ,A.PROCESS_INSTANCE
> ,A.PC_DISTRIB_STATUS ,A.GL_DISTRIB_STATUS
> ,A.PROJ_TRANS_TYPE ,A.PROJ_TRANS_CODE ,A.RESOURCE_STATUS ,A.DESCR
> ,A.SYSTEM_SOURCE ,A.UNIT_OF_MEASURE
> ,A.EMPLID ,A.EMPL_RCD ,A.SEQ_NBR ,A.TIME_RPTG_CD ,A.JOBCODE
> ,A.COMPANY ,A.BUSINESS_UNIT_AP ,A.VENDOR_ID
> ,A.VOUCHER_ID ,A.VOUCHER_LINE_NUM ,A.APPL_JRNL_ID ,A.PYMNT_CNT
> ,A.DST_ACCT_TYPE ,A.PO_DISTRIB_STATUS
> ,A.BUSINESS_UNIT_PO ,A.REQ_ID ,A.REQ_LINE_NBR ,A.REQ_SCHED_NBR
> ,A.REQ_DISTRIB_NBR ,A.PO_ID
> ,A.DUE_DATE ,A.LINE_NBR ,A.SCHED_NBR ,A.DISTRIB_LINE_NUM
> ,A.AM_DISTRIB_STATUS ,A.BUSINESS_UNIT_AM
> ,A.ASSET_ID ,A.PROFILE_ID ,A.COST_TYPE ,A.BOOK ,A.INCENTIVE_ID
> ,A.MSTONE_SEQ ,A.CONTRACT_NUM
> ,A.CONTRACT_LINE_NUM ,A.CONTRACT_PPD_SEQ ,A.BI_DISTRIB_STATUS
> ,A.BUSINESS_UNIT_BI ,A.BILLING_DATE
> ,A.INVOICE ,A.REV_DISTRIB_STATUS ,A.BUSINESS_UNIT_AR ,A.CUST_ID
> ,A.ITEM ,A.ITEM_LINE ,A.ITEM_SEQ_NUM ,A.DST_SEQ_NUM
> ,A.BUSINESS_UNIT_IN ,A.SCHED_LINE_NO ,A.DEMAND_LINE_NO
> ,A.INV_ITEM_ID ,A.PAY_END_DT ,A.BUSINESS_UNIT_OM ,A.ORDER_NO
> ,A.ORDER_INT_LINE_NO ,A.EX_DOC_ID ,A.EX_DOC_TYPE
> ,A.RESOURCE_QUANTITY ,A.RESOURCE_AMOUNT ,A.BUDGET_HDR_STATUS
> ,A.KK_AMOUNT_TYPE ,A.KK_TRAN_OVER_FLAG ,A.KK_TRAN_OVER_OPRID
> ,A.KK_TRAN_OVER_DTTM ,A.BUDGET_OVER_ALLOW
> ,A.BUDGET_LINE_STATUS ,A.BUDGET_DT ,A.LEDGER ,A.BD_DISTRIB_STATUS
> ,A.BUSINESS_UNIT_BD ,A.FA_STATUS ,A.TIME_SHEET_ID
> ,A.SHEET_ID ,A.DT_TIMESTAMP ,A.VCHR_DIST_LINE_NUM ,A.PM_REVIEWED
> ,A.PRICED_RATE
> FROM PS_PROJ_RESOURCE A , PS_CAL_DETP_TBL B , PS_BUS_UNIT_TBL_PC C
> , PS_SET_CNTRL_REC D
> WHERE A.ACCOUNTING_DT BETWEEN B.BEGIN_DT AND B.END_DT
> AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
> AND C.CALENDAR_ID = B.CALENDAR_ID
> AND A.BUSINESS_UNIT = D.SETCNTRLVALUE
> AND D.RECNAME = 'CAL_DEFN_TBL'
> AND B.SETID = D.SETID>
> I tried doing it by putting into subqueries with EXISTS ,but seems to
> be EXISTS makes the performance poor.
>
> Please help me.
>
> Thanks