CODEWITHSUNDEEP
sqlsql-serveroptimizationquery-performance
priyanka shirsath
priyanka shirsath

Reputation: 3

how to optimize this one to reduce execution time

Use ReportingDb
select hd.company_name as CompanyName,
        COALESCE((select sum(case datepart(dw, hrd.created_datetime)
             when 1 then 1 else 0
        end) from dbo.HdSurvey_Result_Details hrd where hd.company_id = hrd.company_id and (hrd.created_datetime between '2019-10-10 10:46:19' AND '2020-10-10 10:46:19')),0) as Monday,
       COALESCE((select sum(case datepart(dw,hrd.created_datetime)
             when 2 then 1 else 0
        end)from dbo.HdSurvey_Result_Details hrd where hd.company_id = hrd.company_id and (hrd.created_datetime between '2019-10-10 10:46:19' AND '2020-10-10 10:46:19')),0) as Tuesday,
         COALESCE((select sum(case datepart(dw,hrd.created_datetime)
             when 3 then 1 else 0
        end)from dbo.HdSurvey_Result_Details hrd where hd.company_id = hrd.company_id and (hrd.created_datetime between '2019-10-10 10:46:19' AND '2020-10-10 10:46:19')),0) as Wednesday,
         COALESCE((select sum(case datepart(dw,hrd.created_datetime)
             when 4 then 1 else 0
        end)from dbo.HdSurvey_Result_Details hrd where hd.company_id = hrd.company_id and (hrd.created_datetime between '2019-10-10 10:46:19' AND '2020-10-10 10:46:19')),0) as Thursday,
         COALESCE((select sum(case datepart(dw,hrd.created_datetime)
             when 5 then 1 else 0
        end)from dbo.HdSurvey_Result_Details hrd where hd.company_id = hrd.company_id and(hrd.created_datetime between '2019-10-10 10:46:19' AND '2020-10-10 10:46:19')),0) as Friday,
         COALESCE((select sum(case datepart(dw,hrd.created_datetime)
             when 6 then 1 else 0
        end)from dbo.HdSurvey_Result_Details hrd where hd.company_id = hrd.company_id and (hrd.created_datetime between '2019-10-10 10:46:19' AND '2020-10-10 10:46:19')),0) as Saturday,
         COALESCE((select sum(case datepart(dw,hrd.created_datetime)
             when 7 then 1 else 0
        end)from dbo.HdSurvey_Result_Details hrd where hd.company_id = hrd.company_id and (hrd.created_datetime between '2019-10-10 10:46:19' AND '2020-10-10 10:46:19')),0) as Sunday,
        COALESCE((select COUNT_BIG(*) from dbo.HdSurvey_Result_Details hrd where hd.company_id = hrd.company_id and(hrd.created_datetime between '2019-10-10 10:46:19' AND '2020-10-10 10:46:19')),0) as TotalResults
from HdSurvey_Result_Details as hd
group by hd.company_name,hd.company_id

Upvotes: 0

Views: 81

Answers (1)

seanb
seanb

Reputation: 6685

I think the initial issue in terms of time is that the amount/approach for all the sub-queries (e.g., the SELECT SUM(CASE ... statements) may not be optimised very well by the query optimiser.

You really need to try to simplify the approach if possible. Which is possible. Ideally, you can do the 'SELECT' components as simple SUM(CASE ... statements without being full sub-queries. This gives the query optimiser the chance to (say) decide to only read the table once rather than 10 times.

To start with, I always check the grouping: What is supposed to be in each row? In this case, each row is a company name. You've set this up well.

The next step is to optimise the aggregate components. Here's an example from your question (most seem to be this way). I've formatted it a bit differently to help me.

COALESCE(
    (select  sum(case datepart(dw, hrd.created_datetime)
               when 1 then 1 else 0
               end) 
       from  dbo.HdSurvey_Result_Details hrd 
       where hd.company_id = hrd.company_id and (hrd.created_datetime between '2019-10-10 10:46:19' AND '2020-10-10 10:46:19')
    ), 
    0) as Monday,

From what I can tell, this is a) filtering by hrd.created_datetime, and b) counting the number of rows within that range where it's a Monday.

Instead, this could be much simplified by putting the filtering within the CASE - (removing the the need for a full sub-query) e.g.,

SUM(CASE WHEN datepart(dw, hd.created_datetime) = 1 AND (hd.created_datetime between '2019-10-10 10:46:19' AND '2020-10-10 10:46:19') THEN 1 ELSE 0 END) AS Monday

As it's already grouped by the company, there's no need to join it/etc to the company. This is handled by the GROUP BY at the bottom.

Also, here's an approach for the total_results value

SUM(CASE WHEN (hd.created_datetime between '2019-10-10 10:46:19' AND '2020-10-10 10:46:19') THEN 1 ELSE 0 END) as TotalResults

If you do these for the all the aggregated values, I believe you will get a good improvement.

However, we can go further.

It appears that you're filtering every count/sum value by the same date range. Instead of including it in the SUM(CASE) statements, just filter your original data with a WHERE clause e.g.,

select  hd.company_name as [CompanyName],
        SUM(CASE WHEN datepart(dw, hd.created_datetime) = 1 THEN 1 ELSE 0 END) AS [Monday],
        -- add similar rows for Tuesday to Sunday
        -- Total results no longer needs the SUM(CASE) as all rows match
        COUNT_BIG(*) as [TotalResults]
from  HdSurvey_Result_Details as hd
WHERE (hd.created_datetime between '2019-10-10 10:46:19' AND '2020-10-10 10:46:19')
group by hd.company_name, hd.company_id

This means that instead of reading the whole table, and then each aggregation being calculated across the whole table (e.g., a lot of rows as 0 in your SUMs), you can instead only read a much smaller part of the table (hopefully) and only do the aggregate calculations on this much smaller data set.

EDIT: Typos - I left some hrd table references rather than hd in solutions. These have been changed to hd.

UPDATE following comments - to include all company names

To get all company names, with 0s if not there, use the above as part of a LEFT JOIN (where the left table is company names).

This is an example - assuming companies are stored in a 'Company' table and with a CTE. You could also do a sub-query.

; WITH A AS
    (select hd.company_Id,   -- Note - changed this to company_id rather than company_name       
            SUM(CASE WHEN datepart(dw, hd.created_datetime) = 1 THEN 1 ELSE 0 END) AS [Monday],
            -- add similar rows for Tuesday to Sunday
            -- Total results no longer needs the SUM(CASE) as all rows match
            COUNT_BIG(*) as [TotalResults]
    from  HdSurvey_Result_Details as hd
    WHERE (hd.created_datetime between '2019-10-10 10:46:19' AND '2020-10-10 
    10:46:19')
    group by hd.company_id      -- Also changed this to company_id
    )
SELECT c.company_name,
       COALESCE(A.[Monday],0) AS Monday,
       --- other days
       COALESCE(A.[TotalResults],0) AS TotalResults
FROM   companies AS c
       LEFT OUTER JOIN A on c.company_id = A.company_id

Upvotes: 1

Related Questions