2
\$\begingroup\$

The code is fetching records from a table called CSAT_SUMMARY_REPORTS[driver table] but due to the filters conditions applied which needs to be checked in another table called CSAT_REPORT_FIELDS.

  1. In this table the filters are in different rows so a group_concat is needed to satisfy the ANDed filter condition.

  2. Then it is joined with the diver table on the basis to joining condition.

Please help to tune this query as it is taking a lot of time to execute.

SELECT 
    FINAL_TABLE.VALUE,
    SUM(VERY_POOR) AS 'VERY_POOR',
    SUM(POOR) AS 'POOR',
    SUM(AVERAGE) AS 'AVERAGE',
    SUM(GOOD) AS 'GOOD',
    SUM(VERY_GOOD) AS 'VERY_GOOD',
    SUM(VERY_POOR) + SUM(POOR) + SUM(AVERAGE) + SUM(GOOD) + SUM(VERY_GOOD) AS 'TOTAL'
FROM
    (SELECT 
        TEMP.VALUE,
            CASE
                WHEN
                    SUM_REPORTID > 0
                        AND CSAT_CLIENT_STATUS = 'VERY_POOR'
                THEN
                    SUM_REPORTID
                ELSE 0
            END 'VERY_POOR',
            CASE
                WHEN
                    SUM_REPORTID > 0
                        AND CSAT_CLIENT_STATUS = 'POOR'
                THEN
                    SUM_REPORTID
                ELSE 0
            END 'POOR',
            CASE
                WHEN
                    SUM_REPORTID > 0
                        AND CSAT_CLIENT_STATUS = 'AVERAGE'
                THEN
                    SUM_REPORTID
                ELSE 0
            END 'AVERAGE',
            CASE
                WHEN
                    SUM_REPORTID > 0
                        AND CSAT_CLIENT_STATUS = 'GOOD'
                THEN
                    SUM_REPORTID
                ELSE 0
            END 'GOOD',
            CASE
                WHEN
                    SUM_REPORTID > 0
                        AND CSAT_CLIENT_STATUS = 'VERY_GOOD'
                THEN
                    SUM_REPORTID
                ELSE 0
            END 'VERY_GOOD'
    FROM
        (SELECT 
        TEMP.CSAT_CLIENT_STATUS, TEMP.SUM_REPORTID, TEMP.VALUE
    FROM
        (SELECT 
        TEMP.CSAT_CLIENT_STATUS,
            SUM(TEMP.COUNT_REPORTID) AS SUM_REPORTID,
            TEMP.VALUE
    FROM
        (SELECT 
        TEMP.VALUE,
            COUNT(TEMP.REPORTID_COUNT) AS COUNT_REPORTID,
            CSR.RESPONSEDATE,
            CASE
                WHEN CSR.CSATSCORE = 1 THEN 'VERY_POOR'
                WHEN CSR.CSATSCORE = 2 THEN 'POOR'
                WHEN CSR.CSATSCORE = 3 THEN 'AVERAGE'
                WHEN CSR.CSATSCORE = 4 THEN 'GOOD'
                ELSE 'VERY_GOOD'
            END 'CSAT_CLIENT_STATUS'
    FROM
        (SELECT 
        COUNT(CRF.REPORTID) REPORTID_COUNT,
            CRF.REPORTID,
            CRF.NAME,
            CRF.VALUE
    FROM
        (SELECT 
        TEMP.REPORTID,
            TEMP.CAMPAIGN_RESPONSE_ID,
            TEMP.CUSTOMERID,
            CRF.NAME,
            CRF.VALUE
    FROM
        (SELECT 
        GROUP_CONCAT(CRF.NAME) AS ALL_NAME,
            GROUP_CONCAT(CRF.VALUE) AS ALL_VALUE,
            CRF.CAMPAIGN_RESPONSE_ID,
            CRF.CUSTOMERID,
            CRF.REPORTID
    FROM
        (SELECT 
        CUSTOMERID, ID, CAMPAIGN_RESPONSE_ID
    FROM
        CSAT_SUMMARY_REPORT
    WHERE
        CUSTOMERID = 23) CSR
    INNER JOIN CSAT_REPORTS_FIELDS CRF ON CSR.CUSTOMERID = CRF.CUSTOMERID
        AND CSR.CUSTOMERID = 23
        AND CSR.ID = CRF.REPORTID
        AND CRF.CAMPAIGN_RESPONSE_ID = CSR.CAMPAIGN_RESPONSE_ID
        AND ISFILTER = 1
        AND ISSEGMENT = 1
    GROUP BY CAMPAIGN_RESPONSE_ID , CRF.CUSTOMERID) TEMP
    INNER JOIN CSAT_REPORTS_FIELDS CRF ON CRF.REPORTID = TEMP.REPORTID
        AND CRF.CAMPAIGN_RESPONSE_ID = TEMP.CAMPAIGN_RESPONSE_ID
    WHERE
        CRF.NAME LIKE '%Campaign%') TEMP
    INNER JOIN CSAT_REPORTS_FIELDS CRF ON TEMP.REPORTID = CRF.REPORTID
        AND TEMP.CAMPAIGN_RESPONSE_ID = CRF.CAMPAIGN_RESPONSE_ID
        AND TEMP.CUSTOMERID = CRF.CUSTOMERID
    WHERE
        CRF.ISFILTER = 1 AND CRF.ISSEGMENT = 1
            AND CRF.NAME LIKE '%Campaign%'
    GROUP BY CRF.REPORTID) TEMP
    INNER JOIN CSAT_SUMMARY_REPORT CSR ON CSR.ID = TEMP.REPORTID
    WHERE
        CSR.RESPONSEDATE BETWEEN '2016-01-23' AND '2016-01-29'
    GROUP BY TEMP.VALUE , CSAT_CLIENT_STATUS) TEMP
    GROUP BY TEMP.VALUE , CSAT_CLIENT_STATUS) TEMP
    GROUP BY TEMP.CSAT_CLIENT_STATUS , TEMP.VALUE) TEMP) FINAL_TABLE
GROUP BY FINAL_TABLE.VALUE
\$\endgroup\$
  • 1
    \$\begingroup\$ Welcome to Code Review! We may be able to give you better advice if you also include the output of EXPLAIN SELECT …. \$\endgroup\$ – 200_success Feb 10 '16 at 5:56
3
\$\begingroup\$

FROM

If you properly indent your code, you will realize how deeply nested it is. You have roughly 8-9 nested FROM clauses. Consider improving the logic. Do you really need to drill that deep to get the data logic you are after?

It's hard to suggest specific improvements without knowing your data. Chances are that you can drill more at the first few nestings and discard some of the deeper ones.

Here is a naked version of the first part of your query (formatting added to illustrate):

SELECT ...
FROM (
    SELECT ...
    FROM (
        SELECT ...
        FROM (
            SELECT...
            FROM (
                SELECT...
                FROM (
                    SELECT...
                    FROM (
                        SELECT...
                        FROM (
                            SELECT... 
                            FROM (
                                SELECT...
                                FROM CSAT_SUMMARY_REPORT
                                WHERE
                                CUSTOMERID = 23
                            ) AS CSR
                            INNER JOIN CSAT_REPORTS_FIELDS CRF ON CSR.CUSTOMERID = CRF.CUSTOMERID
                            --etc.

Formatting

Consider this formatting:

(SELECT 
    TEMP.VALUE,
        CASE
            WHEN
                SUM_REPORTID > 0
                    AND CSAT_CLIENT_STATUS = 'VERY_POOR'
            THEN
                SUM_REPORTID
            ELSE 0
        END 'VERY_POOR',

Personally, I would make your CASE statements more compact, and include the AS keyword before your column alias for clarity:

        CASE
            WHEN SUM_REPORTID > 0 AND CSAT_CLIENT_STATUS = 'VERY_POOR' 
                THEN SUM_REPORTID
            ELSE 0 END AS 'VERY_POOR',
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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