有没有办法提高这个查询(聚合)的性能？

Question

下面是查询：

SELECT  sdd.CompanyID 
            ,sdd.ClassID 
            ,sdd.PeriodID, SUM(sdd.Volume) AS VolumeTotal, SUM(sdd.Dollars) AS DollasTotal
            ,COUNT(LogID) as LogIDCount

    FROM (SELECT dp.CompanyID 
                ,ds.ClassID 
                ,fs.PeriodID, fs.LogID, sum(fs.Volume) AS Volume,sum(fs.Dollars) AS Dollars
    FROM DW.FactSupplyDataDetail fs     WITH (NOLOCK)
        JOIN DW.DimPLProvider dp    WITH (NOLOCK) 
            ON fs.PLProviderID = dp.PLProviderID
        JOIN DW.DimSupply ds    WITH (NOLOCK) 
            ON fs.SupplyID = ds.SupplyID
        WHERE fs.PeriodID between 201901 and 201907
        GROUP BY dp.CompanyID 
                ,ds.ClassID 
                ,fs.PeriodID,fs.LogID) sdd
    GROUP BY sdd.CompanyID 
            ,sdd.ClassID 
            ,sdd.PeriodID

以下是查询的执行计划：

https://www.brentozar.com/pastetheplan/?id=rkoxSEjEH

DW.FactSupplyDataDetail has 10590237 records
DW.DimPLProvider has 5071 records
DW.DimSupply has 81001 records

result of a query is 1992094

Answer 1

检查一下

1. 表FactSupplyDataDetail的索引开始于PeriodID
2. 表DimSupply的索引开始于SupplyID
3. 表DimPLProvider的索引开始于PLProviderID

表的索引是从列开始的，这意味着您的索引(idx_xxx_)定义为：

CREATE INDEX idx_xxx on TABLE (列、其他列或空列表)；