| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
04-22-10, 14:10
|
|
Registered User
|
|
Join Date: Jan 2010
Posts: 26
|
|
|
performance issue with the select query
|
|
Hi,
I have written below query, but it takes around 20 minutes to fetch records.
SELECT INCDNT.INCDNT_ID,INCDNT.STS_CD,INCDNT****LE_24_IND ,INDV.GIVEN_NM,INDV.SURNM,INDV.INDV_ID
FROM PQ5.INCDNT_LCTR INCDNT, PQ5.INDV_LCTR INDV WHERE INCDNT.STS_CD IN (2010,2070) AND INDV.TXN_ID=1
AND INDV.INCDNT_TXN_TYP='L' AND INDV.INDV_ID = 1 AND INDV.REC_SAVE_TYP = 'W' AND INCDNT.INCDNT_ID=INDV.INCDNT_ID
AND INCDNT.INCDNT_ID NOT IN
(
SELECT INCDNT_ID FROM PQ5.LCTR_BTCH_STAGING WHERE INCDNT_ID IS NOT NULL UNION
SELECT INCDNT_ID FROM PQ5.ABM_DEP_STAGING WHERE INCDNT_ID IS NOT NULL UNION
SELECT INCDNT_ID FROM PQ5.INCDNT_CHG_ERR_MSG_RELTN WHERE ERR_STS_CD = 2016 UNION
SELECT DISTINCT A.INCDNT_ID FROM PQ5.INCDNT_LCTR A , PQ5.SRVC_PLATF_SESS_TXN_ACCT_DTL B,PQ5.SRVC_PLATF_SESS C where B.INCDNT_ID = A.INCDNT_ID
AND C.SESS_NO = B.SESS_NO
AND C.SESS_TYP IN ('B','C') AND A****LE_24_IND = '1'
)
ORDER BY INCDNT.INCDNT_ID
Is there any way so that the performance can be increased. If i will create a view for this will it help in performance?
Thanks
Surjya
|
|
04-22-10, 14:40
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
small improvement
This one will work better:
Code:
SELECT INCDNT.INCDNT_ID,INCDNT.STS_CD,INCDNT****LE_24_IND ,
INDV.GIVEN_NM,INDV.SURNM,INDV.INDV_ID
FROM PQ5.INCDNT_LCTR INCDNT, PQ5.INDV_LCTR INDV
WHERE
INCDNT.STS_CD IN (2010,2070)
AND INDV.TXN_ID=1
AND INDV.INCDNT_TXN_TYP='L' AND INDV.INDV_ID = 1
AND INDV.REC_SAVE_TYP = 'W'
AND INCDNT.INCDNT_ID=INDV.INCDNT_ID
AND INCDNT.INCDNT_ID NOT IN
(SELECT A.INCDNT_ID
FROM PQ5.INCDNT_LCTR A ,
PQ5.SRVC_PLATF_SESS_TXN_ACCT_DTL B,
PQ5.SRVC_PLATF_SESS C
where B.INCDNT_ID = A.INCDNT_ID
AND C.SESS_NO = B.SESS_NO
AND C.SESS_TYP IN ('B','C') AND A****LE_24_IND = '1' )
AND INCDNT.INCDNT_ID NOT IN
(SELECT INCDNT_ID FROM PQ5.INCDNT_CHG_ERR_MSG_RELTN
WHERE ERR_STS_CD = 2016)
AND INCDNT.INCDNT_ID NOT IN
(SELECT INCDNT_ID FROM PQ5.LCTR_BTCH_STAGING
WHERE INCDNT_ID IS NOT NULL )
AND INCDNT.INCDNT_ID NOT IN
(SELECT INCDNT_ID FROM PQ5.ABM_DEP_STAGING
WHERE INCDNT_ID IS NOT NULL )
)
ORDER BY INCDNT.INCDNT_ID
Lenny
|
|
04-22-10, 14:43
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
|
|
Also you can use NOT EXISTS instead of INCDNT.INCDNT_ID NOT IN .
Lenny
|
|
04-22-10, 14:59
|
|
Registered User
|
|
Join Date: Jan 2010
Posts: 26
|
|
Thanks Lenny,
It became three times faster.
|
|
04-22-10, 15:40
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
What about this ?
Quote:
Originally Posted by surjyakp
Thanks Lenny,
It became three times faster.
|
This one could be much better then previous:
Code:
SELECT INCDNT.INCDNT_ID,INCDNT.STS_CD,INCDNT****LE_24_IND ,
INDV.GIVEN_NM,INDV.SURNM,INDV.INDV_ID
FROM PQ5.INCDNT_LCTR INCDNT, PQ5.INDV_LCTR INDV
WHERE
INCDNT.STS_CD IN (2010,2070)
AND INDV.TXN_ID=1
AND INDV.INCDNT_TXN_TYP='L' AND INDV.INDV_ID = 1
AND INDV.REC_SAVE_TYP = 'W'
AND INCDNT.INCDNT_ID=INDV.INCDNT_ID
AND Not Exists
(SELECT 1
FROM PQ5.INCDNT_LCTR A ,
PQ5.SRVC_PLATF_SESS_TXN_ACCT_DTL B,
PQ5.SRVC_PLATF_SESS C
where B.INCDNT_ID = A.INCDNT_ID
AND C.SESS_NO = B.SESS_NO
AND C.SESS_TYP IN ('B','C') AND A****LE_24_IND = '1'
and INCDNT.INCDNT = A.INCDNT_ID
)
AND Not Exists
(SELECT 1
FROM PQ5.INCDNT_CHG_ERR_MSG_RELTN A
WHERE ERR_STS_CD = 2016
and INCDNT.INCDNT = A.INCDNT_ID
)
AND Not Exists
(SELECT 1 FROM PQ5.LCTR_BTCH_STAGING A
WHERE INCDNT_ID IS NOT NULL
and INCDNT.INCDNT = A.INCDNT_ID
)
AND Not Exists
(SELECT 1 FROM PQ5.ABM_DEP_STAGING A
WHERE INCDNT_ID IS NOT NULL
and INCDNT.INCDNT = A.INCDNT_ID
)
ORDER BY INCDNT.INCDNT_ID
Lenny
|
|
04-22-10, 16:54
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Solution #3: MIX of NOT EXISTS and NOT IN
Also, it could be MIX of NOT EXISTS and NOT IN statements.
Depends on how many rows return INNER subselects.
Lenny
|
|
04-23-10, 09:08
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
Another item that could be removed from the one sub-select is an unneeded table.
Code:
AND Not Exists
(SELECT 1
FROM PQ5.INCDNT_LCTR A ,
PQ5.SRVC_PLATF_SESS_TXN_ACCT_DTL B,
PQ5.SRVC_PLATF_SESS C
where B.INCDNT_ID = A.INCDNT_ID
AND C.SESS_NO = B.SESS_NO
AND C.SESS_TYP IN ('B','C') AND A****LE_24_IND = '1'
and INCDNT.INCDNT = A.INCDNT_ID
)
Could be replaced with:
Code:
AND Not Exists
(SELECT 1
FROM PQ5.SRVC_PLATF_SESS_TXN_ACCT_DTL B,
PQ5.SRVC_PLATF_SESS C
where C.SESS_NO = B.SESS_NO
AND C.SESS_TYP IN ('B','C') AND A****LE_24_IND = '1'
and INCDNT.INCDNT = B.INCDNT_ID
)
Dave
|
|
04-23-10, 09:22
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Removed because of my misunderstanding.
|
Last edited by tonkuma; 04-23-10 at 09:28.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
performance issue with the select query
