How to increase the performance of the query

MySQL & MariaDB Other MySQL® Questions
1

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

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.

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