跨多个子表查询优化的总计（总和/计数）

我用大量数据填充了数据库，并做了一些有趣的发现。

使用这些过滤索引在我的系统中返回结果大约需要 7 秒，而使用 Kin 建议的非过滤索引需要超过 1 分钟。

create nonclustered index [fnc_PettyExpenses] on [dbo].[PettyExpenses] (
        EventRegistrationId
    ) include (
     AmountPaid
    ,CommercialValueAmount
    )
WHERE [IsDisputed] = 0 AND [IsUndisputed] = 0;
go

create nonclustered index [fnc_HirePremisesExpenses] on [dbo].[HirePremisesExpenses] (
        EventRegistrationId
    ) include (
    AmountPaid
    ,CommercialValueAmount
    )
WHERE [IsDisputed] = 0 AND [IsUndisputed] = 0;
go

create nonclustered index [fnc_AdvertisingExpenses] on [dbo].[AdvertisingExpenses] (
        EventRegistrationId
    ) include (
     AmountPaid
    ,CommercialValueAmount
    )
WHERE [IsDisputed] = 0 AND [IsUndisputed] = 0;
go

create nonclustered index [fnc_ServiceExpenses] on [dbo].[ServiceExpenses] (
        EventRegistrationId
    ) include (
    AmountPaid
    ,CommercialValueAmount
    )
WHERE [IsDisputed] = 0 AND [IsUndisputed] = 0;
go

create nonclustered index [fnc_GoodsSuppliedExpenses] on [dbo].[GoodsSuppliedExpenses] (
        EventRegistrationId
    ) include (
     AmountPaid
    ,CommercialValueAmount
    )
WHERE [IsDisputed] = 0 AND [IsUndisputed] = 0;
go

create nonclustered index [fnc_TravelHireVehicleExpenses] on [dbo].[TravelHireVehicleExpenses] (
        EventRegistrationId
    ) include (
     AmountPaid
    ,CommercialValueAmount
    )
WHERE [IsDisputed] = 0 AND [IsUndisputed] = 0;
go

加上 Kin 推荐的索引（你可能已经有了，因为它似乎是表的主键）：

create nonclustered index [nc_EventRegistrations] on dbo.EventRegistrations (EventRegistrationId);

事实证明，就流逝的时间而言，不存在明显的赢家。

您的查询形式使用MERGE JOINs 获得了一个非常好的并行计划。

使用UNION ALLs 的查询得到一个更简单的串行计划。

就 IO 统计而言，串行计划似乎比并行计划更有效：

并行计划 IO 统计信息

串行计划 IO 统计信息

经过的时间非常相似，但并行计划的 CPU 时间更高。

请记住，过滤索引有一些限制，因此它们可能不是您的最佳选择。

根据您的需要，答案可能是将CTE示例中的每个转换为索引视图。这将以增加存储空间和稍微慢一点的 CRUD 操作为代价，节省每个查询的聚合。基本格式类似于：

CREATE VIEW dbo.AggregatedPettyExpenses 
    WITH SCHEMABINDING AS
    SELECT 
        EventRegistrationId
        ,SUM(AmountPaid)            as AmountPaidTotal
        ,SUM(CommercialValueAmount) as CommercialValueAmountTotal
        ,SUM(CASE WHEN AmountPaid IS NOT NULL THEN 1 ELSE 0 END) as ExpenseCount -- Hack for your count logic
        ,COUNT_BIG(*) AS COUNT
    FROM PettyExpenses 
    WHERE IsDisputed = 0 AND IsUndisputed = 0
    GROUP BY EventRegistrationId

    CREATE UNIQUE CLUSTERED INDEX [IDX_dbo_AggregatedPettyExpenses_EventRegistrationId] 
        ON dbo.AggregatedPettyExpenses(EventRegistrationId);

对每个 重复上面相同的模式CTE。然后你会VIEW在你的例子中有一个 like 但你LEFT JOIN到 newINDEXED VIEW的而不是CTE的。

由于您提到您正在使用标准版，因此值得知道如果您希望索引视图的行为类似于索引视图而不是普通视图，则必须使用查询提示。WITH (NOEXPAND)顺便说一下，目前 Azure Sql Server 数据库也是如此。

有人有比这更好的选择吗？更快的东西？

您的原始查询将对所有 6 个表进行表扫描。

您可以删除distinct eer.EventRegistrationId并使用GROUP BY eer.EventRegistrationId，其余一切保持不变。

以下索引将帮助您避免TABLE SCAN并会执行以下操作INDEX SEEK：

create nonclustered index [nc_PettyExpenses] on [dbo].[PettyExpenses] (
    [IsDisputed]
    ,[IsUndisputed]
    ) include (
    EventRegistrationId
    ,AmountPaid
    ,CommercialValueAmount
    )
go

create nonclustered index [nc_HirePremisesExpenses] on [dbo].[HirePremisesExpenses] (
    [IsDisputed]
    ,[IsUndisputed]
    ) include (
    EventRegistrationId
    ,AmountPaid
    ,CommercialValueAmount
    )
go

create nonclustered index [nc_AdvertisingExpenses] on [dbo].[AdvertisingExpenses] (
    [IsDisputed]
    ,[IsUndisputed]
    ) include (
    EventRegistrationId
    ,AmountPaid
    ,CommercialValueAmount
    )
go

create nonclustered index [nc_ServiceExpenses] on [dbo].[ServiceExpenses] (
    [IsDisputed]
    ,[IsUndisputed]
    ) include (
    EventRegistrationId
    ,AmountPaid
    ,CommercialValueAmount
    )
go

create nonclustered index [nc_GoodsSuppliedExpenses] on [dbo].[GoodsSuppliedExpenses] (
    [IsDisputed]
    ,[IsUndisputed]
    ) include (
    EventRegistrationId
    ,AmountPaid
    ,CommercialValueAmount
    )
go

create nonclustered index [nc_TravelHireVehicleExpenses] on [dbo].[TravelHireVehicleExpenses] (
    [IsDisputed]
    ,[IsUndisputed]
    ) include (
    EventRegistrationId
    ,AmountPaid
    ,CommercialValueAmount
    )
go

create nonclustered index [nc_EventRegistrations] on dbo.EventRegistrations (EventRegistrationId);

WITH OPTION (MAXDOP 1)+ 以上索引

查询计划

统计IO输出

没有OPTION (MAXDOP 1)+ 以上索引

查询计划：

统计IO输出

╔═════════════════════════════════╦═════╦═════════════╗
║             Option              ║ CPU ║ ElapsedTime ║
╠═════════════════════════════════╬═════╬═════════════╣
║ Plain - As you posted your code ║  16 ║          12 ║
║ Without MAXDOP 1 + Indexes      ║  62 ║          11 ║
║ With MAXDOP 1 + Indexes         ║   0 ║           7 ║ <== Winner !!
╚═════════════════════════════════╩═════╩═════════════╝

以下是完整性代码：

/*
dbcc freeproccache
dbcc dropcleanbuffers

*/
set nocount on
set statistics io on
set statistics time on;

with pe
as (
    select EventRegistrationId
        ,sum(AmountPaid) as AmountPaidTotal
        ,sum(CommercialValueAmount) as CommercialValueAmountTotal
        ,count(1) as ExpenseCount
    from PettyExpenses
    where IsDisputed = 0
        and IsUndisputed = 0
    group by EventRegistrationId
    )
    ,hpe
as (
    select EventRegistrationId
        ,sum(AmountPaid) as AmountPaidTotal
        ,sum(CommercialValueAmount) as CommercialValueAmountTotal
        ,count(1) as ExpenseCount
    from HirePremisesExpenses
    where IsDisputed = 0
        and IsUndisputed = 0
    group by EventRegistrationId
    )
    ,ae
as (
    select EventRegistrationId
        ,sum(AmountPaid) as AmountPaidTotal
        ,sum(CommercialValueAmount) as CommercialValueAmountTotal
        ,count(1) as ExpenseCount
    from AdvertisingExpenses
    where IsDisputed = 0
        and IsUndisputed = 0
    group by EventRegistrationId
    )
    ,se
as (
    select EventRegistrationId
        ,sum(AmountPaid) as AmountPaidTotal
        ,sum(CommercialValueAmount) as CommercialValueAmountTotal
        ,count(1) as ExpenseCount
    from ServiceExpenses
    where IsDisputed = 0
        and IsUndisputed = 0
    group by EventRegistrationId
    )
    ,gse
as (
    select EventRegistrationId
        ,sum(AmountPaid) as AmountPaidTotal
        ,sum(CommercialValueAmount) as CommercialValueAmountTotal
        ,count(1) as ExpenseCount
    from GoodsSuppliedExpenses
    where IsDisputed = 0
        and IsUndisputed = 0
    group by EventRegistrationId
    )
    ,thve
as (
    select EventRegistrationId
        ,sum(AmountPaid) as AmountPaidTotal
        ,sum(CommercialValueAmount) as CommercialValueAmountTotal
        ,count(1) as ExpenseCount
    from TravelHireVehicleExpenses
    where IsDisputed = 0
        and IsUndisputed = 0
    group by EventRegistrationId
    )
select eer.EventRegistrationId
    --Petty Expense
    ,ISNULL(SUM(case e.src
                when 'pe'
                    then e.AmountPaidTotal
                end), 0) as PettyExpenseAmountPaid
    ,ISNULL(SUM(case e.src
                when 'pe'
                    then e.CommercialValueAmountTotal
                end), 0) as PettyExpenseCommercial
    ,ISNULL(SUM(case e.src
                when 'pe'
                    then e.ExpenseCount
                end), 0) as PettyExpenseCount
    --Hire On Premise Expense
    ,ISNULL(SUM(case e.src
                when 'hpe'
                    then e.AmountPaidTotal
                end), 0) as HireOnPremisesExpenseAmountPaid
    ,ISNULL(SUM(case e.src
                when 'hpe'
                    then e.CommercialValueAmountTotal
                end), 0) as HireOnPremisesExpenseCommercial
    ,ISNULL(SUM(case e.src
                when 'hpe'
                    then e.ExpenseCount
                end), 0) as HireOnPremisesExpenseCount
    --Advertising Expense
    ,ISNULL(SUM(case e.src
                when 'ae'
                    then e.AmountPaidTotal
                end), 0) as AdvertisingExpenseAmountPaid
    ,ISNULL(SUM(case e.src
                when 'ae'
                    then e.CommercialValueAmountTotal
                end), 0) as AdvertisingExpenseCommercial
    ,ISNULL(SUM(case e.src
                when 'ae'
                    then e.ExpenseCount
                end), 0) as AdvertisingExpenseExpenseCount
    --Services Expense
    ,ISNULL(SUM(case e.src
                when 'se'
                    then e.AmountPaidTotal
                end), 0) as ServiceExpenseAmountPaid
    ,ISNULL(SUM(case e.src
                when 'se'
                    then e.CommercialValueAmountTotal
                end), 0) as ServiceExpenseCommercial
    ,ISNULL(SUM(case e.src
                when 'se'
                    then e.ExpenseCount
                end), 0) as ServiceExpenseExpenseCount
    --Goods Supplied Expense
    ,ISNULL(SUM(case e.src
                when 'gse'
                    then e.AmountPaidTotal
                end), 0) as GoodsSuppliedExpenseAmountPaid
    ,ISNULL(SUM(case e.src
                when 'gse'
                    then e.CommercialValueAmountTotal
                end), 0) as GoodsSuppliedExpenseCommercial
    ,ISNULL(SUM(case e.src
                when 'gse'
                    then e.ExpenseCount
                end), 0) as GoodsSuppliedExpenseExpenseCount
    --Travel and Vehicle Expense
    ,ISNULL(SUM(case e.src
                when 'thve'
                    then e.AmountPaidTotal
                end), 0) as TravelVehicleExpenseAmountPaid
    ,ISNULL(SUM(case e.src
                when 'thve'
                    then e.CommercialValueAmountTotal
                end), 0) as TravelVehicleExpenseCommercial
    ,ISNULL(SUM(case e.src
                when 'thve'
                    then e.ExpenseCount
                end), 0) as TravelVehicleExpenseExpenseCount
    --All Expenses
    ,ISNULL(SUM(e.AmountPaidTotal), 0) as AllExpenseAmountPaidTotal
    ,ISNULL(SUM(e.CommercialValueAmountTotal), 0) as AllExpenseCommercialValueTotal
    ,ISNULL(SUM(e.ExpenseCount), 0) as AllExpenseCount
from EventRegistrations eer
left join (
    select 'pe' as src
        ,EventRegistrationId
        ,AmountPaidTotal
        ,CommercialValueAmountTotal
        ,ExpenseCount
    from pe

    union all

    select 'hpe' as src
        ,EventRegistrationId
        ,AmountPaidTotal
        ,CommercialValueAmountTotal
        ,ExpenseCount
    from hpe

    union all

    select 'ae' as src
        ,EventRegistrationId
        ,AmountPaidTotal
        ,CommercialValueAmountTotal
        ,ExpenseCount
    from ae

    union all

    select 'se' as src
        ,EventRegistrationId
        ,AmountPaidTotal
        ,CommercialValueAmountTotal
        ,ExpenseCount
    from se

    union all

    select 'gse' as src
        ,EventRegistrationId
        ,AmountPaidTotal
        ,CommercialValueAmountTotal
        ,ExpenseCount
    from gse

    union all

    select 'thve' as src
        ,EventRegistrationId
        ,AmountPaidTotal
        ,CommercialValueAmountTotal
        ,ExpenseCount
    from thve
    ) as e on eer.EventRegistrationId = e.EventRegistrationId
group by eer.EventRegistrationId --- we removed the distinct and added a group by clause 
option (maxdop 1)

set statistics io off
set statistics time off

注意：您可以伪造行数和页数 <-- 仅出于教育原因

UPDATE STATISTICS [dbo].[PettyExpenses]             WITH ROWCOUNT = 10000000, pagecount = 10000000
UPDATE STATISTICS [dbo].[HirePremisesExpenses]      WITH ROWCOUNT = 10000000, pagecount = 10000000
UPDATE STATISTICS [dbo].[AdvertisingExpenses]       WITH ROWCOUNT = 10000000, pagecount = 10000000
UPDATE STATISTICS [dbo].[ServiceExpenses]           WITH ROWCOUNT = 10000000, pagecount = 10000000
UPDATE STATISTICS [dbo].[GoodsSuppliedExpenses]     WITH ROWCOUNT = 10000000, pagecount = 10000000
UPDATE STATISTICS [dbo].[TravelHireVehicleExpenses] WITH ROWCOUNT = 10000000, pagecount = 10000000