Totais (somas/contagens) em várias tabelas filho otimização de consulta

Preenchi o banco de dados com MUITOS dados e fiz algumas descobertas interessantes.

O uso desses índices filtrados retorna resultados em cerca de 7 segundos em meu sistema, em comparação com mais de 1 minuto com os índices não filtrados sugeridos por Kin.

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

Mais o índice recomendado por Kin (que você provavelmente já possui, pois parece ser a chave primária da tabela):

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

Acontece que não existe um vencedor claro em termos de tempo decorrido.

Sua forma de consulta obtém um plano paralelo muito bom usando MERGE JOINs.

A consulta usando UNION ALLs obtém um plano serial muito mais simples.

No que diz respeito às estatísticas de IO, parece que o plano serial é um pouco mais eficiente que o plano paralelo:

Estatísticas IO do plano paralelo

Estatísticas de E/S do plano serial

Os tempos decorridos são muito semelhantes, mas o tempo de CPU é maior para o plano paralelo.

Lembre-se de que os índices filtrados têm algumas limitações , portanto, podem não ser a melhor escolha para você.

Dependendo de suas necessidades, a resposta pode ser converter cada um CTEem seu exemplo em uma exibição indexada . Isso economizaria as agregações em cada consulta às custas de maior armazenamento e operações CRUD um pouco mais lentas. O formato básico seria algo como:

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);

Repita o mesmo padrão acima para cada um CTE. Então você teria apenas um VIEWgosto em seu exemplo, mas você LEFT JOINpara o novo INDEXED VIEW's em vez do CTE's.

Como você mencionou que está usando a Standard Edition, vale a pena saber que você terá que usar a dica de consultaWITH (NOEXPAND) se quiser que as exibições indexadas se comportem como exibições indexadas em vez de exibições normais. Aliás, isso também é verdade atualmente com os bancos de dados Azure Sql Server.

Alguém tem uma alternativa melhor que essa? Algo mais rápido?

Sua consulta original fará varreduras de tabela para todas as 6 tabelas.

Você pode remover o distinct eer.EventRegistrationIde usar GROUP BY eer.EventRegistrationId, resto tudo permanece o mesmo.

Os índices abaixo ajudarão você a evitar TABLE SCANe farão um 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);

COM OPTION (MAXDOP 1)+ Índices Acima

plano de consulta

saída de E/S de estatísticas

SEM OPTION (MAXDOP 1)+ Índices Acima

Plano de consulta :

Estatísticas de saída 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 !!
╚═════════════════════════════════╩═════╩═════════════╝

Abaixo está o código para completar:

/*
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

Observação: você pode falsificar contagens de linhas e contagens de páginas <-- APENAS POR MOTIVOS EDUCACIONAIS

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