如何对此进行优化以减少执行时间

Question

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

Answer 1

我认为，就时间而言，最初的问题是，所有子查询(例如，SELECT SUM(CASE ...语句)的数量/方法可能不会被查询优化器很好地优化。

如果可能的话，你真的需要尝试简化这个方法。这是可能的。理想情况下，您可以将“SELECT”组件作为简单的SUM(CASE ...语句执行，而无需进行完整的子查询。这使查询优化器有机会(比方说)决定只读一次表，而不是读10次。

首先，我总是检查分组:每一行应该是什么？在这种情况下，每一行都是一个公司名称。你安排得很好。

下一步是优化聚合组件。以下是你问题中的一个例子(大多数似乎都是这样的)。为了帮助我，我对它的格式有点不同。

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,

据我所知，这是( a) hrd.created_datetime的过滤，( b)计算该范围内的行数(星期一)。

相反，这可以通过在情况中放置过滤(消除对完整子查询的需求)来简化。例如，

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

由于它已经按公司分组了，所以没有必要加入它/其他公司。这是由组在底部处理。

另外，下面是total_results值的一种方法

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

如果您对所有的聚合值都这样做，我相信您会得到很好的改进。

然而，我们可以更进一步。

看起来，您正在按相同的日期范围过滤每个计数/和值。与其将其包含在SUM(CASE)语句中，不如使用WHERE子句过滤原始数据，例如，

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

这意味着，与其读取整个表，然后计算整个表中的每个聚合(例如，许多行的总和为0)，相反，您只能读取表的更小部分(希望如此)，而只能在这个小得多的数据集中进行聚合计算。

编辑:输入-我在解决方案中留下了一些hrd表引用而不是hd。这些已改为hd。

更新以下评论-包括所有公司名称

若要获取所有公司名称(如果没有0，则使用上面的名称作为左联接的一部分)(其中左表是公司名称)。

这是一个示例-假设公司存储在“Company”表中，并使用CTE。你也可以做一个子查询。

; 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