Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2010
    Posts
    26

    Unanswered: 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

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb 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

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Also you can use NOT EXISTS instead of INCDNT.INCDNT_ID NOT IN .

    Lenny

  4. #4
    Join Date
    Jan 2010
    Posts
    26
    Thanks Lenny,
    It became three times faster.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool What about this ?

    Quote Originally Posted by surjyakp View Post
    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

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool 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

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Removed because of my misunderstanding.
    Last edited by tonkuma; 04-23-10 at 10:28.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •