GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

How to increase the performance of the query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to increase the performance of the query

    Hi all,

    The below mentioned query is inefficient in performance . can any body help me to increase the performance of the query mentioned below

    SELECT T1.GRP_ID
    , T1.SGRP_ID
    , T1.INS_CARR_ID
    , T1.XXPGM_ID
    , T1.CTRCT_ID
    , T1.CTRCT_FRM_DT
    , T1.CTRCT_THRU_DT
    , T1.XXCTRCT_ST_CD
    , T1.CTRCT_PROC_END_DT
    FROM MSSGCO3V T1
    , MSSGRP5V T2
    WHERE T1.XXRGN_ID = '25'
    AND T1.XXCTRCT_ST_CD = 'H'
    AND T1.CTRCT_PROC_END_DT = 99999999
    AND T2.XXSGRP_ST_CD = 'AC'
    AND T2.GRP_ID = T1.GRP_ID
    AND T2.SGRP_ID = T1.SGRP_ID
    AND T1.CTRCT_ID IN
    (SELECT T3.CTRCT_ID
    FROM MSSGCO3V T3
    WHERE T3.XXRGN_ID = T1.XXRGN_ID
    AND T3.GRP_ID = T1.GRP_ID
    AND T3.INS_CARR_ID = T1.INS_CARR_ID
    AND T3.XXPGM_ID = T1.XXPGM_ID
    AND T3.CTRCT_FRM_DT = T1.CTRCT_FRM_DT
    AND T3.CTRCT_PROC_END_DT = T1.CTRCT_PROC_END_DT
    GROUP BY T3.CTRCT_ID
    HAVING COUNT(*) > 1)
    ORDER BY T1.GRP_ID
    , T1.CTRCT_ID
    , T1.SGRP_ID
    , T1.INS_CARR_ID
    , T1.XXPGM_ID


    Thanks in advance ...

    Regards
    Anil

  • #2
    1. explain SELECT T3.CTRCT_ID
    FROM MSSGCO3V T3
    WHERE T3.XXRGN_ID = T1.XXRGN_ID
    AND T3.GRP_ID = T1.GRP_ID
    AND T3.INS_CARR_ID = T1.INS_CARR_ID
    AND T3.XXPGM_ID = T1.XXPGM_ID
    AND T3.CTRCT_FRM_DT = T1.CTRCT_FRM_DT
    AND T3.CTRCT_PROC_END_DT = T1.CTRCT_PROC_END_DT
    GROUP BY T3.CTRCT_ID
    HAVING COUNT(*) > 1

    2. explain SELECT T1.GRP_ID ... , T1.XXPGM_ID

    Comment


    • #3
      i have a table called mssgco3v in that i need select the contract id that shares a contract id with another subgroup with in the same group.

      for example:

      GRP_ID SGRP_ID INS_CARR_ID XXPGM_ID CTRCT_ID CTRCT_FRM_DT

      1 GR1 K M 7392 20060101
      1 GR2 K M 7392 20060101
      6 504 K M 6465 20040101
      6 505 K M 6465 20040101
      6 507 k M 6465 20040101
      I need to selcet the ctrct id shared by more than 1 sgrp(subgroup)with in same grp_id(group) in the table mssgco3v.

      Comment


      • #4
        GRP_ID SGRP_ID INS_CARR_ID XXPGM_ID CTRCT_ID CTRCT_FRM_DT

        1 GR1 K M 7392 20060101
        1 GR2 K M 7392 20060101
        6 504 K M 6465 20040101
        6 505 K M 6465 20040101
        6 507 K M 6465 20040101

        Comment

        Working...
        X