-2

I have below query written by someone targeting Microsoft SQL server 2017. I do not have access to database to create indexes etc., So only way I can do is optimize below query as efficient as possible.

My query here is,

Can I use Case and WHEN in inner query as used in the below query? Also am currently trying to remove nested queries by using inner join.

Any other ideas will be appreciated for the below queries. I am not asking to give final optimized query, but asking suggestion specific to below query.

SELECT CoverPremium.COVER_PREMIUM_SET_ID
    ,CoverPremium.POLICY_ID
    ,CoverPremium.ADDRESS_ID
    ,CoverPremium.STATUS_CODE
    ,CoverPremium.COVER_PREMIUM_MODEL_ID
    ,CoverPremium.SUB_PRODUCT_REF
    ,CoverPremium.COVER_PREMIUM_MODEL_REF
    ,CoverPremium.Description
    ,CoverPremium.PREMIUM_LEVEL_CODE
    ,CoverPremium.PREMIUM_PAYABLE
    ,CoverPremium.PREMIUM_DUE
    ,CoverPremium.COMMISSION_GST
    ,CoverPremium.COMMISSION
    ,CoverPremium.COMMISSION_TOTAL
    ,CoverPremium.TERRORISM_GST
    ,CoverPremium.TERRORISM_RI
    ,CoverPremium.STAMP_DUTY
    ,CoverPremium.GST
    ,CoverPremium.FSL
    ,CoverPremium.MODIFIED_PREMIUM
    ,CoverPremium.TECHNICAL_PREMIUM
    ,CoverPremium.BASE_PREMIUM
    ,CoverPremium.PREMIUM_PAYABLE_TERM
    ,CoverPremium.PREMIUM_DUE_TERM
    ,CoverPremium.COMMISSION_GST_TERM
    ,CoverPremium.COMMISSION_TERM
    ,CoverPremium.COMMISSION_TOTAL_TERM
    ,CoverPremium.TERRORISM_GST_TERM
    ,CoverPremium.TERRORISM_RI_TERM
    ,CoverPremium.STAMP_DUTY_TERM
    ,CoverPremium.GST_TERM
    ,CoverPremium.FSL_TERM
    ,CoverPremium.MODIFIED_PREMIUM_TERM
    ,CoverPremium.TECHNICAL_PREMIUM_TERM
    ,CoverPremium.BASE_PREMIUM_TERM
    ,CoverPremium.PREMIUM_PAYABLE_ANNUALISED
    ,CoverPremium.PREMIUM_DUE_ANNUALISED
    ,CoverPremium.COMMISSION_GST_ANNUALISED
    ,CoverPremium.COMMISSION_ANNUALISED
    ,CoverPremium.COMMISSION_TOTAL_ANNUALISED
    ,CoverPremium.TERRORISM_GST_ANNUALISED
    ,CoverPremium.TERRORISM_RI_ANNUALISED
    ,CoverPremium.STAMP_DUTY_ANNUALISED
    ,CoverPremium.GST_ANNUALISED
    ,CoverPremium.FSL_ANNUALISED
    ,CoverPremium.MODIFIED_PREMIUM_ANNUALISED
    ,CoverPremium.TECHNICAL_PREMIUM_ANNUALISED
    ,CoverPremium.BASE_PREMIUM_ANNUALISED
FROM (
    SELECT NULL AS COVER_PREMIUM_SET_ID
        ,a1.POLICY_ID
        ,a1.ADDRESS_ID
        ,a1.STATUS_CODE
        ,CPM_CHILD.PARENT_ID AS COVER_PREMIUM_MODEL_ID
        ,a1.SUB_PRODUCT_REF
        ,CPM.COVER_PREMIUM_MODEL_REF
        ,CPM.NAME AS Description
        ,CPM.PREMIUM_LEVEL_CODE AS PREMIUM_LEVEL_CODE
        ,sum(a1.PREMIUM_PAYABLE) PREMIUM_PAYABLE
        ,sum(a1.PREMIUM_DUE) PREMIUM_DUE
        ,sum(a1.COMMISSION_GST) COMMISSION_GST
        ,sum(a1.COMMISSION) COMMISSION
        ,sum(a1.COMMISSION_TOTAL) COMMISSION_TOTAL
        ,sum(a1.TERRORISM_GST) TERRORISM_GST
        ,sum(a1.TERRORISM_RI) TERRORISM_RI
        ,sum(a1.STAMP_DUTY) STAMP_DUTY
        ,sum(a1.GST) GST
        ,sum(a1.FSL) FSL
        ,sum(a1.MODIFIED_PREMIUM) MODIFIED_PREMIUM
        ,sum(a1.TECHNICAL_PREMIUM) TECHNICAL_PREMIUM
        ,sum(a1.BASE_PREMIUM) BASE_PREMIUM
        ,sum(a1.PREMIUM_PAYABLE_TERM) PREMIUM_PAYABLE_TERM
        ,sum(a1.PREMIUM_DUE_TERM) PREMIUM_DUE_TERM
        ,sum(a1.COMMISSION_GST_TERM) COMMISSION_GST_TERM
        ,sum(a1.COMMISSION_TERM) COMMISSION_TERM
        ,sum(a1.COMMISSION_TOTAL_TERM) COMMISSION_TOTAL_TERM
        ,sum(a1.TERRORISM_GST_TERM) TERRORISM_GST_TERM
        ,sum(a1.TERRORISM_RI_TERM) TERRORISM_RI_TERM
        ,sum(a1.STAMP_DUTY_TERM) STAMP_DUTY_TERM
        ,sum(a1.GST_TERM) GST_TERM
        ,sum(a1.FSL_TERM) FSL_TERM
        ,sum(a1.MODIFIED_PREMIUM_TERM) MODIFIED_PREMIUM_TERM
        ,sum(a1.TECHNICAL_PREMIUM_TERM) TECHNICAL_PREMIUM_TERM
        ,sum(a1.BASE_PREMIUM_TERM) BASE_PREMIUM_TERM
        ,sum(a1.PREMIUM_PAYABLE_ANNUALISED) PREMIUM_PAYABLE_ANNUALISED
        ,sum(a1.PREMIUM_DUE_ANNUALISED) PREMIUM_DUE_ANNUALISED
        ,sum(a1.COMMISSION_GST_ANNUALISED) COMMISSION_GST_ANNUALISED
        ,sum(a1.COMMISSION_ANNUALISED) COMMISSION_ANNUALISED
        ,sum(a1.COMMISSION_TOTAL_ANNUALISED) COMMISSION_TOTAL_ANNUALISED
        ,sum(a1.TERRORISM_GST_ANNUALISED) TERRORISM_GST_ANNUALISED
        ,sum(a1.TERRORISM_RI_ANNUALISED) TERRORISM_RI_ANNUALISED
        ,sum(a1.STAMP_DUTY_ANNUALISED) STAMP_DUTY_ANNUALISED
        ,sum(a1.GST_ANNUALISED) GST_ANNUALISED
        ,sum(a1.FSL_ANNUALISED) FSL_ANNUALISED
        ,sum(a1.MODIFIED_PREMIUM_ANNUALISED) MODIFIED_PREMIUM_ANNUALISED
        ,sum(a1.TECHNICAL_PREMIUM_ANNUALISED) TECHNICAL_PREMIUM_ANNUALISED
        ,sum(a1.BASE_PREMIUM_ANNUALISED) BASE_PREMIUM_ANNUALISED
    FROM uat_cod_helo.COVER_PREMIUM_MODEL CPM
        ,uat_cod_helo.COVER_PREMIUM_MODEL CPM_CHILD
        ,uat_cod_helo.SUB_PRODUCT SP
        ,PRODUCT P
        ,PRODUCT_SUB_PRODUCT PSP
        ,(
            SELECT COVER_PREMIUM_PART.COVER_PREMIUM_SET_ID
                ,COVER_PREMIUM_SET.POLICY_ID
                ,CASE 
                    WHEN COVER_PREMIUM_SET.PREMIUM_LEVEL_CODE = 'PREMITEM'
                        THEN a.ADDRESS_ID
                    ELSE CASE 
                            WHEN COVER_PREMIUM_SET.PREMIUM_LEVEL_CODE IN (
                                    'SITSUM'
                                    ,'SUBPRODSUM'
                                    )
                                THEN (
                                        SELECT ADDRESS.ADDRESS_ID
                                        FROM ADDRESS
                                        WHERE COVER_PREMIUM_SET.PREMIUM_LEVEL_CODE IN (
                                                'SITSUM'
                                                ,'SUBPRODSUM'
                                                )
                                            AND ADDRESS.ADDRESS_REF = COVER_PREMIUM_SET.ENTITY_INSTANCE_REF
                                            AND ADDRESS.POLICY_ID = COVER_PREMIUM_SET.POLICY_ID
                                        )
                            END
                    END ADDRESS_ID
                ,a.STATUS_CODE
                ,(
                    SELECT CPM.COVER_PREMIUM_MODEL_ID
                    FROM COVER_PREMIUM_MODEL CPM
                    WHERE CPM.COVER_PREMIUM_MODEL_REF = COVER_PREMIUM_SET.COVER_PREMIUM_MODEL_REF
                    ) AS COVER_PREMIUM_MODEL_ID
                ,COVER_PREMIUM_SET.SUB_PRODUCT_REF
                ,COVER_PREMIUM_SET.COVER_PREMIUM_MODEL_REF
                ,COVER_PREMIUM_SET.DESCRIPTION
                ,COVER_PREMIUM_SET.PREMIUM_LEVEL_CODE
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'PREMIUM_PAYABLE'
                            THEN TRANSACTION_AMOUNT
                        END) PREMIUM_PAYABLE
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'PREMIUM_DUE'
                            THEN TRANSACTION_AMOUNT
                        END) PREMIUM_DUE
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION_GST'
                            THEN TRANSACTION_AMOUNT
                        END) COMMISSION_GST
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION'
                            THEN TRANSACTION_AMOUNT
                        END) COMMISSION
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION_TOTAL'
                            THEN TRANSACTION_AMOUNT
                        END) COMMISSION_TOTAL
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'TERRORISM_GST'
                            THEN TRANSACTION_AMOUNT
                        END) TERRORISM_GST
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'TERRORISM_RI'
                            THEN TRANSACTION_AMOUNT
                        END) TERRORISM_RI
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'STAMP_DUTY'
                            THEN TRANSACTION_AMOUNT
                        END) STAMP_DUTY
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'GST'
                            THEN TRANSACTION_AMOUNT
                        END) GST
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'FSL'
                            THEN TRANSACTION_AMOUNT
                        END) FSL
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'MODIFIED_PREMIUM'
                            THEN TRANSACTION_AMOUNT
                        END) MODIFIED_PREMIUM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'TECHNICAL_PREMIUM'
                            THEN TRANSACTION_AMOUNT
                        END) TECHNICAL_PREMIUM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'BASE_PREMIUM'
                            THEN TRANSACTION_AMOUNT
                        END) BASE_PREMIUM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'PREMIUM_PAYABLE'
                            THEN TERM_AMOUNT
                        END) PREMIUM_PAYABLE_TERM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'PREMIUM_DUE'
                            THEN TERM_AMOUNT
                        END) PREMIUM_DUE_TERM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION_GST'
                            THEN TERM_AMOUNT
                        END) COMMISSION_GST_TERM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION'
                            THEN TERM_AMOUNT
                        END) COMMISSION_TERM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION_TOTAL'
                            THEN TERM_AMOUNT
                        END) COMMISSION_TOTAL_TERM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'TERRORISM_GST'
                            THEN TERM_AMOUNT
                        END) TERRORISM_GST_TERM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'TERRORISM_RI'
                            THEN TERM_AMOUNT
                        END) TERRORISM_RI_TERM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'STAMP_DUTY'
                            THEN TERM_AMOUNT
                        END) STAMP_DUTY_TERM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'GST'
                            THEN TERM_AMOUNT
                        END) GST_TERM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'FSL'
                            THEN TERM_AMOUNT
                        END) FSL_TERM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'MODIFIED_PREMIUM'
                            THEN TERM_AMOUNT
                        END) MODIFIED_PREMIUM_TERM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'TECHNICAL_PREMIUM'
                            THEN TERM_AMOUNT
                        END) TECHNICAL_PREMIUM_TERM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'BASE_PREMIUM'
                            THEN TERM_AMOUNT
                        END) BASE_PREMIUM_TERM
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'PREMIUM_PAYABLE'
                            THEN FUTURE_ANNUALISED_AMOUNT
                        END) PREMIUM_PAYABLE_ANNUALISED
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'PREMIUM_DUE'
                            THEN FUTURE_ANNUALISED_AMOUNT
                        END) PREMIUM_DUE_ANNUALISED
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION_GST'
                            THEN FUTURE_ANNUALISED_AMOUNT
                        END) COMMISSION_GST_ANNUALISED
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION'
                            THEN FUTURE_ANNUALISED_AMOUNT
                        END) COMMISSION_ANNUALISED
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION_TOTAL'
                            THEN FUTURE_ANNUALISED_AMOUNT
                        END) COMMISSION_TOTAL_ANNUALISED
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'TERRORISM_GST'
                            THEN FUTURE_ANNUALISED_AMOUNT
                        END) TERRORISM_GST_ANNUALISED
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'TERRORISM_RI'
                            THEN FUTURE_ANNUALISED_AMOUNT
                        END) TERRORISM_RI_ANNUALISED
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'STAMP_DUTY'
                            THEN FUTURE_ANNUALISED_AMOUNT
                        END) STAMP_DUTY_ANNUALISED
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'GST'
                            THEN FUTURE_ANNUALISED_AMOUNT
                        END) GST_ANNUALISED
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'FSL'
                            THEN FUTURE_ANNUALISED_AMOUNT
                        END) FSL_ANNUALISED
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'MODIFIED_PREMIUM'
                            THEN FUTURE_ANNUALISED_AMOUNT
                        END) MODIFIED_PREMIUM_ANNUALISED
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'TECHNICAL_PREMIUM'
                            THEN FUTURE_ANNUALISED_AMOUNT
                        END) TECHNICAL_PREMIUM_ANNUALISED
                ,MAX(CASE 
                        WHEN PREMIUM_MODEL_PART_REF = 'BASE_PREMIUM'
                            THEN FUTURE_ANNUALISED_AMOUNT
                        END) BASE_PREMIUM_ANNUALISED
            FROM COVER_PREMIUM_SET
            LEFT JOIN (
                SELECT SITUATION_SECTION.SITUATION_SECTION_ID
                    ,SITUATION_SECTION.POLICY_ID
                    ,SITUATION_SECTION.ADDRESS_ID
                    ,SITUATION_SECTION.SITUATION_SECTION_REF
                    ,SITUATION_SECTION.STATUS_CODE
                FROM SITUATION_SECTION
                    ,SECTION
                WHERE SITUATION_SECTION.POLICY_ID = SECTION.POLICY_ID
                    AND SITUATION_SECTION.SECTION_ID = SECTION.SECTION_ID
                ) a ON COVER_PREMIUM_SET.SITUATION_SECTION_REF = a.SITUATION_SECTION_REF
                AND COVER_PREMIUM_SET.POLICY_ID = a.POLICY_ID
            LEFT JOIN COVER_PREMIUM_PART ON COVER_PREMIUM_SET.COVER_PREMIUM_SET_ID = COVER_PREMIUM_PART.COVER_PREMIUM_SET_ID
            GROUP BY COVER_PREMIUM_PART.COVER_PREMIUM_SET_ID
                ,COVER_PREMIUM_SET.POLICY_ID
                ,COVER_PREMIUM_SET.DESCRIPTION
                ,COVER_PREMIUM_SET.PREMIUM_LEVEL_CODE
                ,COVER_PREMIUM_SET.COVER_PREMIUM_MODEL_REF
                ,COVER_PREMIUM_SET.SUB_PRODUCT_REF
                ,a.SITUATION_SECTION_ID
                ,a.ADDRESS_ID
                ,a.STATUS_CODE
                ,COVER_PREMIUM_SET.ENTITY_INSTANCE_REF
            ) a1
    WHERE CPM.SUB_PRODUCT_ID = SP.SUB_PRODUCT_ID
        AND SP.SUB_PRODUCT_ID = PSP.SUB_PRODUCT_ID
        AND P.PRODUCT_ID = PSP.PRODUCT_ID
        AND P.PRODUCT_REF = 'SCTP_BUSINESS_PACK'
        AND CPM.PREMIUM_LEVEL_CODE = 'PREMRLUP'
        AND CPM_CHILD.PARENT_ID = CPM.COVER_PREMIUM_MODEL_ID
        AND CPM_CHILD.COVER_PREMIUM_MODEL_REF = a1.COVER_PREMIUM_MODEL_REF
    GROUP BY a1.POLICY_ID
        ,a1.ADDRESS_ID
        ,CPM_CHILD.PARENT_ID
        ,CPM.NAME
        ,CPM.PREMIUM_LEVEL_CODE
        ,a1.SUB_PRODUCT_REF
        ,CPM.COVER_PREMIUM_MODEL_REF
        ,a1.STATUS_CODE

    UNION

    SELECT COVER_PREMIUM_PART.COVER_PREMIUM_SET_ID
        ,COVER_PREMIUM_SET.POLICY_ID
        ,CASE 
            WHEN COVER_PREMIUM_SET.PREMIUM_LEVEL_CODE = 'PREMITEM'
                THEN a.ADDRESS_ID
            ELSE CASE 
                    WHEN COVER_PREMIUM_SET.PREMIUM_LEVEL_CODE IN (
                            'SITSUM'
                            ,'SUBPRODSUM'
                            )
                        THEN (
                                SELECT ADDRESS.ADDRESS_ID
                                FROM ADDRESS
                                WHERE COVER_PREMIUM_SET.PREMIUM_LEVEL_CODE IN (
                                        'SITSUM'
                                        ,'SUBPRODSUM'
                                        )
                                    AND ADDRESS.ADDRESS_REF = COVER_PREMIUM_SET.ENTITY_INSTANCE_REF
                                    AND ADDRESS.POLICY_ID = COVER_PREMIUM_SET.POLICY_ID
                                )
                    END
            END ADDRESS_ID
        ,a.STATUS_CODE
        ,(
            SELECT CPM.COVER_PREMIUM_MODEL_ID
            FROM COVER_PREMIUM_MODEL CPM
            WHERE CPM.COVER_PREMIUM_MODEL_REF = COVER_PREMIUM_SET.COVER_PREMIUM_MODEL_REF
            ) AS COVER_PREMIUM_MODEL_ID
        ,COVER_PREMIUM_SET.SUB_PRODUCT_REF
        ,COVER_PREMIUM_SET.COVER_PREMIUM_MODEL_REF
        ,COVER_PREMIUM_SET.DESCRIPTION
        ,COVER_PREMIUM_SET.PREMIUM_LEVEL_CODE
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'PREMIUM_PAYABLE'
                    THEN TRANSACTION_AMOUNT
                END) PREMIUM_PAYABLE
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'PREMIUM_DUE'
                    THEN TRANSACTION_AMOUNT
                END) PREMIUM_DUE
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION_GST'
                    THEN TRANSACTION_AMOUNT
                END) COMMISSION_GST
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION'
                    THEN TRANSACTION_AMOUNT
                END) COMMISSION
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION_TOTAL'
                    THEN TRANSACTION_AMOUNT
                END) COMMISSION_TOTAL
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'TERRORISM_GST'
                    THEN TRANSACTION_AMOUNT
                END) TERRORISM_GST
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'TERRORISM_RI'
                    THEN TRANSACTION_AMOUNT
                END) TERRORISM_RI
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'STAMP_DUTY'
                    THEN TRANSACTION_AMOUNT
                END) STAMP_DUTY
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'GST'
                    THEN TRANSACTION_AMOUNT
                END) GST
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'FSL'
                    THEN TRANSACTION_AMOUNT
                END) FSL
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'MODIFIED_PREMIUM'
                    THEN TRANSACTION_AMOUNT
                END) MODIFIED_PREMIUM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'TECHNICAL_PREMIUM'
                    THEN TRANSACTION_AMOUNT
                END) TECHNICAL_PREMIUM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'BASE_PREMIUM'
                    THEN TRANSACTION_AMOUNT
                END) BASE_PREMIUM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'PREMIUM_PAYABLE'
                    THEN TERM_AMOUNT
                END) PREMIUM_PAYABLE_TERM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'PREMIUM_DUE'
                    THEN TERM_AMOUNT
                END) PREMIUM_DUE_TERM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION_GST'
                    THEN TERM_AMOUNT
                END) COMMISSION_GST_TERM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION'
                    THEN TERM_AMOUNT
                END) COMMISSION_TERM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION_TOTAL'
                    THEN TERM_AMOUNT
                END) COMMISSION_TOTAL_TERM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'TERRORISM_GST'
                    THEN TERM_AMOUNT
                END) TERRORISM_GST_TERM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'TERRORISM_RI'
                    THEN TERM_AMOUNT
                END) TERRORISM_RI_TERM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'STAMP_DUTY'
                    THEN TERM_AMOUNT
                END) STAMP_DUTY_TERM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'GST'
                    THEN TERM_AMOUNT
                END) GST_TERM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'FSL'
                    THEN TERM_AMOUNT
                END) FSL_TERM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'MODIFIED_PREMIUM'
                    THEN TERM_AMOUNT
                END) MODIFIED_PREMIUM_TERM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'TECHNICAL_PREMIUM'
                    THEN TERM_AMOUNT
                END) TECHNICAL_PREMIUM_TERM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'BASE_PREMIUM'
                    THEN TERM_AMOUNT
                END) BASE_PREMIUM_TERM
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'PREMIUM_PAYABLE'
                    THEN FUTURE_ANNUALISED_AMOUNT
                END) PREMIUM_PAYABLE_ANNUALISED
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'PREMIUM_DUE'
                    THEN FUTURE_ANNUALISED_AMOUNT
                END) PREMIUM_DUE_ANNUALISED
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION_GST'
                    THEN FUTURE_ANNUALISED_AMOUNT
                END) COMMISSION_GST_ANNUALISED
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION'
                    THEN FUTURE_ANNUALISED_AMOUNT
                END) COMMISSION_ANNUALISED
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'COMMISSION_TOTAL'
                    THEN FUTURE_ANNUALISED_AMOUNT
                END) COMMISSION_TOTAL_ANNUALISED
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'TERRORISM_GST'
                    THEN FUTURE_ANNUALISED_AMOUNT
                END) TERRORISM_GST_ANNUALISED
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'TERRORISM_RI'
                    THEN FUTURE_ANNUALISED_AMOUNT
                END) TERRORISM_RI_ANNUALISED
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'STAMP_DUTY'
                    THEN FUTURE_ANNUALISED_AMOUNT
                END) STAMP_DUTY_ANNUALISED
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'GST'
                    THEN FUTURE_ANNUALISED_AMOUNT
                END) GST_ANNUALISED
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'FSL'
                    THEN FUTURE_ANNUALISED_AMOUNT
                END) FSL_ANNUALISED
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'MODIFIED_PREMIUM'
                    THEN FUTURE_ANNUALISED_AMOUNT
                END) MODIFIED_PREMIUM_ANNUALISED
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'TECHNICAL_PREMIUM'
                    THEN FUTURE_ANNUALISED_AMOUNT
                END) TECHNICAL_PREMIUM_ANNUALISED
        ,MAX(CASE 
                WHEN PREMIUM_MODEL_PART_REF = 'BASE_PREMIUM'
                    THEN FUTURE_ANNUALISED_AMOUNT
                END) BASE_PREMIUM_ANNUALISED
    FROM COVER_PREMIUM_SET
    LEFT JOIN (
        SELECT SITUATION_SECTION.SITUATION_SECTION_ID
            ,SITUATION_SECTION.POLICY_ID
            ,SITUATION_SECTION.ADDRESS_ID
            ,SITUATION_SECTION.SITUATION_SECTION_REF
            ,SITUATION_SECTION.STATUS_CODE
        FROM SITUATION_SECTION
            ,SECTION
        WHERE SITUATION_SECTION.POLICY_ID = SECTION.POLICY_ID
            AND SITUATION_SECTION.SECTION_ID = SECTION.SECTION_ID
        ) a ON COVER_PREMIUM_SET.SITUATION_SECTION_REF = a.SITUATION_SECTION_REF
        AND COVER_PREMIUM_SET.POLICY_ID = a.POLICY_ID
    LEFT JOIN COVER_PREMIUM_PART ON COVER_PREMIUM_SET.COVER_PREMIUM_SET_ID = COVER_PREMIUM_PART.COVER_PREMIUM_SET_ID
    GROUP BY COVER_PREMIUM_PART.COVER_PREMIUM_SET_ID
        ,COVER_PREMIUM_SET.POLICY_ID
        ,COVER_PREMIUM_SET.DESCRIPTION
        ,COVER_PREMIUM_SET.PREMIUM_LEVEL_CODE
        ,COVER_PREMIUM_SET.COVER_PREMIUM_MODEL_REF
        ,COVER_PREMIUM_SET.SUB_PRODUCT_REF
        ,a.SITUATION_SECTION_ID
        ,a.ADDRESS_ID
        ,a.STATUS_CODE
        ,COVER_PREMIUM_SET.ENTITY_INSTANCE_REF
    ) CoverPremium
WHERE CoverPremium.POLICY_ID = 77780029
ORDER BY CoverPremium.ADDRESS_ID
    ,CoverPremium.COVER_PREMIUM_SET_ID
1
  • My first impression was that this query is generated by some ORM tool but on closer inspection it is definitely not. You say: "by someone targeting Microsoft SQL server 2017" - this must be a joke, as this query uses non-ANSI join syntax almost everywhere with ANSI join syntax occasionally thrown in. It was also written in the time before PIVOT operator existed.
    – Alex
    Dec 4, 2019 at 11:16

1 Answer 1

4

Hard for me to give specific performance suggestions when I can barely follow the code.

Below are readability suggestions:

  1. Change all joins to ANSI syntax.
  2. Convert MAX(CASE WHEN .... constructs to PIVOT.

Constructs like:

MAX(CASE 
    WHEN PREMIUM_MODEL_PART_REF = 'PREMIUM_DUE'
        THEN TRANSACTION_AMOUNT
    END) PREMIUM_DUE

are "old-style" row to column pivot. Given that PIVOT is a built in operator, it may even improve performance somewhat.

Some generic tips on performance:

  1. UNION - Do you have duplicates to remove? If not, use UNION ALL
  2. Avoid doing GROUP BY on the product of many table joins. I suggest you only group the bare minimum of tables you need and then join the output to other tables. (Same would apply to PIVOT)
  3. Splitting your query into multiple smaller queries (store intermediate results in temp tables or table variables) may be worth considering.

Not the answer you're looking for? Browse other questions tagged or ask your own question.