Home » RDBMS Server » Performance Tuning » query problem
| query problem [message #482145] |
Wed, 10 November 2010 01:07  |
narang79
Messages: 137
Registered: June 2010
|
Senior Member |
|
|
sir when we run this following query it take 3 mins
if i remove account table which is joined with party master
then it take 2 sec only
if we run single query with joined account & party then it take 2 sec
how i can fast this query sir
SELECT DISTINCT PM.PARTY_CODE,PM.PARTY_NAME,PM.PARTY_CODE STATUS_CUST,PM.DEFAULTPURCHASER
FROM PR_INDENT_DET ID_DET,PR_INDENT_MAST ID_MAST,ITEM ITM,
EMPLOYEE EMP,(
SELECT POI.INDENT_NO,POI.INDENT_DATE,
POI.ITEM_CODE,POI.QTY_ORD - NVL(T.EXTENDED_QTY,0) QTY_ORD
FROM PURCHASE_ORDER PO,PURCHASE_ORDER_ITEM POI,
(SELECT PO_NO,PO_DATE,ITEM_CODE,SUM(NVL(EXTENDED_QTY,0)) EXTENDED_QTY,INDENT_NO,INDENT_DATE FROM TBL_PO_ADJUSTMENT
GROUP BY PO_NO,PO_DATE,ITEM_CODE,INDENT_NO,INDENT_DATE)T
WHERE PO.PO_NO = POI.PO_NO
AND PO.PO_DATE = POI.PO_DATE
AND POI.PO_NO = T.PO_NO(+)
AND POI.PO_DATE = T.PO_DATE(+)
AND POI.INDENT_NO = T.INDENT_NO(+)
AND POI.INDENT_DATE = T.INDENT_DATE(+)
AND POI.ITEM_CODE = T.ITEM_CODE(+)
AND PO.CANC_STATUS = 'N'
AND PO.CCODE ='AL'--:GLOBAL.MYCODE
AND NVL(POI.APPROVAL_STATUS,'A')='A'
ORDER BY 2 DESC
) POI,PARTY_MASTER PM, ACCOUNT A,
(
SELECT Q.QUOT_COMP_NO,Q.QUOT_COMP_DATE,
Q.SUPP_CODE,ITEM.PART_NO,ITEM.CCODE
FROM QUOT_COMPARISON_DTL Q,QUOT_COMPARISON_MST QM,(
SELECT MAX(Q.QUOT_COMP_NO) QUOT_COMP_NO,
Q.QUOT_COMP_DATE,Q.ITEM_CODE,M.CCODE
FROM QUOT_COMPARISON_DTL Q,QUOT_COMPARISON_MST M,(
SELECT MAX(QM.QUOT_COMP_DATE) QUOT_COMP_DATE,ITEM_CODE,CCODE
FROM QUOT_COMPARISON_DTL QD,QUOT_COMPARISON_MST QM
WHERE QM.QUOT_COMP_NO = QD.QUOT_COMP_NO
AND QM.QUOT_COMP_DATE = QD.QUOT_COMP_DATE
AND QM.CCODE = 'AL'--:GLOBAL.MYCODE
GROUP BY QD.ITEM_CODE,QM.CCODE
) MAX_DATE
WHERE M.QUOT_COMP_NO = Q.QUOT_COMP_NO
AND M.QUOT_COMP_DATE = Q.QUOT_COMP_DATE
AND Q.QUOT_COMP_DATE = MAX_DATE.QUOT_COMP_DATE
AND Q.ITEM_CODE = MAX_DATE.ITEM_CODE
AND M.CCODE = 'AL'--:GLOBAL.MYCODE
GROUP BY Q.QUOT_COMP_DATE,Q.ITEM_CODE,M.CCODE
) MAX_NO,ITEM
WHERE QM.QUOT_COMP_NO = Q.QUOT_COMP_NO
AND QM.QUOT_COMP_DATE = Q.QUOT_COMP_DATE
AND QM.CCODE ='AL'--:GLOBAL.MYCODE
AND Q.QUOT_COMP_NO = MAX_NO.QUOT_COMP_NO
AND Q.QUOT_COMP_DATE = MAX_NO.QUOT_COMP_DATE
AND Q.ITEM_CODE = MAX_NO.ITEM_CODE
AND SUPP_TYPE = 'V1'
AND Q.ITEM_CODE = ITEM.ITEM_CODE
AND ITEM.CCODE = 'AL'--:GLOBAL.MYCODE
) IVD
WHERE ID_MAST.INDENT_NO = ID_DET.INDENT_NO
AND A.SUPP_CODE = PM.PARTY_CODE
AND A.CCODE=PM.CCODE
AND A.FREEZE='N'
AND ID_MAST.INDENT_DATE = ID_DET.INDENT_DATE
AND ID_DET.ITEM_CODE = ITM.ITEM_CODE
AND ID_MAST.CCODE ='AL'--:GLOBAL.MYCODE
AND ITM.CCODE = 'AL'--:GLOBAL.MYCODE
AND ID_MAST.APPROVAL_STATUS = 'A'
AND ID_MAST.FYCODE ='10-11'--:GLOBAL.MYFINY
AND ID_MAST.EMPLOYEE_CODE = EMP.EMPLOYEE_CODE
AND ITM.PART_NO = IVD.PART_NO
AND IVD.SUPP_CODE = PM.PARTY_CODE
AND ID_DET.INDENT_NO = POI.INDENT_NO(+)
AND ID_DET.INDENT_DATE = POI.INDENT_DATE(+)
AND ID_DET.ITEM_CODE = POI.ITEM_CODE(+)
AND ITM.PART_NO IN (SELECT PART_NO FROM ITEM WHERE STORE_CODE ='0001'--:PURCHASE_ORDER.STORE_CODE
)
AND ITM.STORE_CODE ='0001'--:PURCHASE_ORDER.STORE_CODE
GROUP BY ID_DET.IND_SEQ_NO,ID_DET.INDENT_NO,ID_DET.INDENT_DATE,ID_DET.ITEM_CODE,ID_DET.ITEM_RATE,
ID_DET.IND_QTY,ID_MAST.EMPLOYEE_CODE,ITM.ITEM_NAME,EMP.EMPLOYEE_NAME,ID_DET.IND_PURPOSE,
IVD.SUPP_CODE,ITM.STORE_CODE,PM.PARTY_CODE,PM.PARTY_NAME,PM.DEFAULTPURCHASER
HAVING (ID_DET.IND_QTY - NVL(SUM(POI.QTY_ORD),0))>0
ORDER BY 2
|
|
|
|
|
|
|
|
|
|
| Re: query problem [message #482154 is a reply to message #482152] |
Wed, 10 November 2010 01:47  |
narang79
Messages: 137
Registered: June 2010
|
Senior Member |
|
|
select distinct pm.party_code,
pm.party_name,
pm.party_code status_cust,
pm.defaultpurchaser
from pr_indent_det id_det,
pr_indent_mast id_mast,
item itm,
employee emp,
(
select poi.indent_no,poi.indent_date,
poi.item_code,
poi.qty_ord - nvl(t.extended_qty,0) qty_ord
from purchase_order po,
purchase_order_item poi,
(select po_no,po_date,item_code,
sum(nvl(extended_qty,0)) extended_qty,indent_no,
indent_date from tbl_po_adjustment
group by po_no,po_date,item_code,
indent_no,indent_date)t
where po.po_no = poi.po_no
and po.po_date = poi.po_date
and poi.po_no = t.po_no(+)
and poi.po_date = t.po_date(+)
and poi.indent_no = t.indent_no(+)
and poi.indent_date = t.indent_date(+)
and poi.item_code = t.item_code(+)
and po.canc_status = 'n'
and po.ccode ='al'--:global.mycode
and nvl(poi.approval_status,'a')='a'
order by 2 desc
) poi,party_master pm, account a,--problem table-------
(
select q.quot_comp_no,q.quot_comp_date,
q.supp_code,item.part_no,item.ccode
from quot_comparison_dtl q,
quot_comparison_mst qm,
(
select max(q.quot_comp_no) quot_comp_no,
q.quot_comp_date,q.item_code,m.ccode
from quot_comparison_dtl q,
quot_comparison_mst m,
(
select max(qm.quot_comp_date) quot_comp_date,
item_code,ccode
from quot_comparison_dtl qd,
quot_comparison_mst qm
where qm.quot_comp_no = qd.quot_comp_no
and qm.quot_comp_date = qd.quot_comp_date
and qm.ccode = 'al'--:global.mycode
group by qd.item_code,qm.ccode
)
max_date
where m.quot_comp_no = q.quot_comp_no
and m.quot_comp_date = q.quot_comp_date
and q.quot_comp_date = max_date.quot_comp_date
and q.item_code = max_date.item_code
and m.ccode = 'al'--:global.mycode
group by q.quot_comp_date,
q.item_code,m.ccode
) max_no,
item
where qm.quot_comp_no = q.quot_comp_no
and qm.quot_comp_date = q.quot_comp_date
and qm.ccode ='al'--:global.mycode
and q.quot_comp_no = max_no.quot_comp_no
and q.quot_comp_date = max_no.quot_comp_date
and q.item_code = max_no.item_code
and supp_type = 'v1'
and q.item_code = item.item_code
and item.ccode = 'al'--:global.mycode
) ivd
where id_mast.indent_no = id_det.indent_no
and a.supp_code = pm.party_code
and a.ccode=pm.ccode
and a.freeze='n'
and id_mast.indent_date = id_det.indent_date
and id_det.item_code = itm.item_code
and id_mast.ccode ='al'--:global.mycode
and itm.ccode = 'al'--:global.mycode
and id_mast.approval_status = 'a'
and id_mast.fycode ='10-11'--:global.myfiny
and id_mast.employee_code = emp.employee_code
and itm.part_no = ivd.part_no
and ivd.supp_code = pm.party_code
and id_det.indent_no = poi.indent_no(+)
and id_det.indent_date = poi.indent_date(+)
and id_det.item_code = poi.item_code(+)
and itm.part_no in (select part_no from item where store_code ='0001'--:purchase_order.store_code
)
and itm.store_code ='0001'--:purchase_order.store_code
group by id_det.ind_seq_no,id_det.indent_no,
id_det.indent_date,id_det.item_code,
id_det.item_rate,
id_det.ind_qty,id_mast.employee_code,
itm.item_name,emp.employee_name,id_det.ind_purpose,
ivd.supp_code,itm.store_code,
pm.party_code,pm.party_name,pm.defaultpurchaser
having (id_det.ind_qty - nvl(sum(poi.qty_ord),0))>0
order by 2
|
|
|
|
| Re: query problem [message #482164 is a reply to message #482154] |
Wed, 10 November 2010 03:43 |
 |
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
This is your 94 post and don`t you know what are required for tuning?
Read the posting guide lines as well as performance tuning guide requirements in this forum and come back here
sriram
|
|
|
|
Goto Forum:
Current Time: Thu Feb 20 17:17:03 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
