CODEWITHSUNDEEP
sqlsql-serverperformancet-sql
Humayun Shabbir
Humayun Shabbir

Reputation: 3269

How to optimise an SQL query that use multiple subqueries and aggregate functions?

I have an SQL query as follows. It serves the purpose but it is very slow and a bit complicated as it have many aggregate functions and sub queries. I find it complicated and very slow.

Here is the query:

SELECT
    dd.period,
    DATEADD(day,1,DATEADD(month,-12,MAX(dso.date_clearing))) AS startdate, 
    MAX(dso.date_clearing) AS lastdate, 
    ROUND(SUM(dso.DSO_actual_calc)/SUM(dso.amount_received_group_currency),1) AS dso,
    ROUND(SUM(dso.DSO_overdue_calc)/SUM(dso.amount_received_group_currency),1) AS dsooverdue,
   (SELECT
        ROUND(SUM(dso1.DSO_actual_calc)/SUM(dso1.amount_received_group_currency),1)
    FROM fact_dso_cleared_items as dso1
        INNER JOIN dim_date dd1
            ON dso1.date_clearing = dd1.the_date
    WHERE dso1.date_clearing BETWEEN
        DATEADD(day,1,DATEADD(month,-12,MAX(dso.date_clearing)))
             AND MAX(dso.date_clearing))) AS dso_rltm,
   (SELECT
        ROUND(SUM(dso2.DSO_overdue_calc)/SUM(dso2.amount_received_group_currency), 1)
    FROM fact_dso_cleared_items as dso2
        INNER JOIN dim_date as dd2
            ON dso2.date_clearing = dd2.the_date
    WHERE dso2.date_clearing BETWEEN
        DATEADD(day,1,DATEADD(month,-12,MAX(dso.date_clearing)))
        AND MAX(dso.date_clearing))) AS dso_overdue_rltm
FROM fact_dso_cleared_items AS dso
    INNER JOIN dim_date dd
        ON dso.date_clearing = dd.the_date
WHERE dd.period IN('2012/01','2012/02','2012/03','2012/04','2012/05','2012/06',
                   '2012/07','2012/08','2012/09','2012/10','2012/11','2012/12'))
GROUP BY dd.period
ORDER BY dd.period

Here is diagram for this query that shows tables and fields.

database/query diagram

And here are results of this query.

query results

What are the areas I should start improving in this query for simplification and performance?

Upvotes: 0

Views: 97

Answers (1)

Hockenberry
Hockenberry

Reputation: 462

First attempt. I'm trying to kill the multiple aggregate functions, because you are using DATEADD(Day, 1, DATEADD(Month, -12, MAX(dso.date_clearing))) multiple times. I think a with query would help to.

First attempt.

SELECT *

    ,dso_rltm           = (SELECT ROUND(SUM(dso1.DSO_actual_calc) / SUM(dso1.amount_received_group_currency), 1)
                           FROM fact_dso_cleared_items as dso1
                           INNER JOIN dim_date dd1 ON dso1.date_clearing = dd1.the_date
                           WHERE dso1.date_clearing BETWEEN data.startdate AND data.lastdate)

   ,dso_overdue_rltm    = (SELECT ROUND(SUM(dso2.DSO_overdue_calc) / SUM(dso2.amount_received_group_currency), 1)
                           FROM fact_dso_cleared_items as dso2
                           INNER JOIN dim_date as dd2 ON dso2.date_clearing = dd2.the_date 
                           WHERE dso2.date_clearing BETWEEN data.startdate AND data.lastdate)
FROM (
    SELECT

         period     = dd.period
        ,startdate  = DATEADD(Day, 1, DATEADD(Month, -12, MAX(dso.date_clearing))) 
        ,lastdate   = MAX(dso.date_clearing) 
        ,dso        = ROUND(SUM(dso.DSO_actual_calc) / SUM(dso.amount_received_group_currency), 1)
        ,dsooverdue = ROUND(SUM(dso.DSO_overdue_calc) / SUM(dso.amount_received_group_currency), 1)

    FROM fact_dso_cleared_items AS dso
    INNER JOIN dim_date dd ON dso.date_clearing = dd.the_date
    WHERE dd.period IN('2012/01','2012/02','2012/03','2012/04','2012/05','2012/06','2012/07','2012/08','2012/09','2012/10','2012/11','2012/12'))
    GROUP BY dd.period
    ) data
ORDER BY data.period

Upvotes: 1

Related Questions