this is my first question here. Although I've been helped out from this forum over a hundred times. I was having difficulties in optimizing sql query. It takes hours to execute. Record set is also large enough. The query was not written by me. So just to find the bottle neck I tried removing conditional clauses but that doesn't make any difference. Indexing the ID's done.
Can any sql guru here could throw some light on it. Is there any fine tuning room left in the query below? The server hosting the database in DB2. I'm not too pro in sql. Thanks as always. Regards, Nuh
This is the query:
SELECT
HEALTH_INSURANCE.RISK_DETAIL_ID ,
POLICY_RISK_COVER.RISK_COVER_ID ,
HEALTH_INSURANCE.RD_POLICY_SYSTEM_NO ,
RD_POLICY_END_NO_IDX ,
HEALTH_INSURANCE.RD_POLICY_ID ,
HEALTH_INSURANCE.RD_LEVEL1_ID ,
HEALTH_INSURANCE.RD_SUM_INSURED_AMT_LC ,
HEALTH_INSURANCE.RD_PREMIUM_AMT_LC ,
POLICY_RISK_COVER.PREMIUM_AMOUNT_FC ,
POLICY_RISK_COVER.SUM_INSURED_AMT_FC ,
HEALTH_INSURANCE.RD_REC_TYPE ,
HEALTH_INSURANCE.RD_EFFECT_FROM_DT ,
HEALTH_INSURANCE.RD_EFFECT_TO_DT ,
HEALTH_INSURANCE.RD_END_EFFECT_FROM_DT ,
HEALTH_INSURANCE.SEX_MAS_CD ,
HEALTH_INSURANCE.MARITAL_STATUS_CD ,
HEALTH_INSURANCE.EMP_CATG ,
HEALTH_INSURANCE.NO_OF_DEPENDENTS ,
CAST((
CASE
WHEN HEALTH_INSURANCE.AUTHORITY_LETTER_NO IS NULL
THEN HEALTH_INSURANCE.EMP_AL_NO
ELSE HEALTH_INSURANCE.AUTHORITY_LETTER_NO
END) AS INT) AS EMP_AL_NO ,
HEALTH_INSURANCE.DOB ,
HEALTH_INSURANCE.EFF_DATE ,
HEALTH_INSURANCE.EFF_DATE2 ,
HEALTH_INSURANCE.NAME ,
CAST((SUBSTR(HEALTH_INSURANCE.RELATIONSHIP_CD, 5,2)) AS INT) AS
RELATIONSHIP_CD_S ,
HEALTH_INSURANCE.RELATIONSHIP_CD ,
HEALTH_INSURANCE.DESIGNATION ,
HEALTH_INSURANCE.BRANCH ,
HEALTH_INSURANCE.BANK_ACCOUNT ,
HEALTH_INSURANCE.BANK_BRANCH_NAME ,
HEALTH_INSURANCE.PRE_EXISTING_AILMENT ,
HEALTH_INSURANCE.AUTHORITY_LETTER_NO ,
HEALTH_INSURANCE.AGE ,
HEALTH_INSURANCE.REGION ,
HEALTH_INSURANCE.CNIC ,
HEALTH_INSURANCE.CO_CODE ,
HEALTH_INSURANCE.EMP_LOCATION ,
HEALTH_INSURANCE.SUB_LOCATION ,
CATEGORY_LIMIT_HEADER.CLH_SYSTEM_NO ,
CATEGORY_LIMIT_HEADER.CTH_SYS_ID ,
CATEGORY_LIMIT_HEADER.CTH_POL_SYS_ID ,
CATEGORY_LIMIT_HEADER.CTH_END_NO_IDX ,
CATEGORY_LIMIT_HEADER.CTH_END_SR_NO ,
CATEGORY_LIMIT_HEADER.CTH_CATEGORY ,
CATEGORY_LIMIT_DETAIL.CLD_SYS_ID ,
CATEGORY_LIMIT_DETAIL.CLDH_SYS_ID ,
CATEGORY_LIMIT_DETAIL.CLD_COVER_CD ,
CATEGORY_LIMIT_DETAIL.CLD_END_IDX ,
CATEGORY_LIMIT_DETAIL.CLD_COVER_DESC ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_TYPE_LIMIT ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_REL ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_FROM ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_TO ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_RB_LIMIT ,
CATEGORY_LIMIT_DETAIL.CLD_CATEGORY_LIMIT_FC ,
CATEGORY_LIMIT_DETAIL.CLD_CATEGORY_PREM_FC
FROM
DB2ADMIN.HEALTH_INSURANCE AS HEALTH_INSURANCE
INNER JOIN DB2ADMIN.POLICY_RISK_COVER AS POLICY_RISK_COVER
ON HEALTH_INSURANCE.RD_POLICY_SYSTEM_NO = POLICY_RISK_COVER.
RC_POLICY_SYSTEM_NO AND
TRIM(RD_LEVEL1_ID) = TRIM(RC_LEVEL1_ID)
INNER JOIN DB2ADMIN.CATEGORY_LIMIT_HEADER AS CATEGORY_LIMIT_HEADER
ON HEALTH_INSURANCE.RD_POLICY_ID = CATEGORY_LIMIT_HEADER.
CTH_POL_SYS_ID AND
HEALTH_INSURANCE.EMP_CATG = CATEGORY_LIMIT_HEADER.CTH_CATEGORY
INNER JOIN DB2ADMIN.CATEGORY_LIMIT_DETAIL AS
CATEGORY_LIMIT_DETAIL
ON CATEGORY_LIMIT_HEADER.CTH_SYS_ID = CATEGORY_LIMIT_DETAIL.
CLDH_SYS_ID AND
POLICY_RISK_COVER.RISK_COVER_CD = CATEGORY_LIMIT_DETAIL.
CLD_COVER_CD AND
HEALTH_INSURANCE.RELATIONSHIP_CD = CATEGORY_LIMIT_DETAIL.
CLD_CLM_REL
WHERE
COALESCE(HEALTH_INSURANCE.AGE, 1) BETWEEN CATEGORY_LIMIT_DETAIL.
CLD_CLM_AGE_FROM AND
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_TO