Totals(sums/counts) across multiple child tables query optimization

Question

We have 12 types of expenses in our database, some have fairly differing data minus the Amount fields. We have multiple places in the application and reports that require single and multiple expense totals and counts per expense Type and Grand totals. In the end, we want one View for all of these calls but are open to using a stored procedure.

We looked at multiple alternatives for this and found that a CTE allows us to get all the data required without the use of temp tables. Using joins does not work as we saw records being replicated or removed no matter what we tried.

I’ve attached a subset of the expense tables and the query that includes the CTE. Does anybody have a better alternative than this? Something faster? Are we approaching this ‘flattening’ appropriately?

Please note the execution plan is the same for this query whether it is a View or a Proc and the Proc seems to take twice as long to run.

Below is the code

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
distinct eer.EventRegistrationId
--Petty Expense
,ISNULL(pe.AmountPaidTotal,0) as PettyExpenseAmountPaid
,ISNULL(pe.CommercialValueAmountTotal,0) as PettyExpenseCommercial
,ISNULL(pe.ExpenseCount,0) as PettyExpenseCount
--Hire On Premise Expense
,ISNULL(hpe.AmountPaidTotal,0) as HireOnPremisesExpenseAmountPaid
,ISNULL(hpe.CommercialValueAmountTotal,0) as HireOnPremisesExpenseCommercial
,ISNULL(hpe.ExpenseCount,0) as HireOnPremisesExpenseCount
--Advertising Expense
,ISNULL(ae.AmountPaidTotal,0) as AdvertisingExpenseAmountPaid
,ISNULL(ae.CommercialValueAmountTotal,0) as AdvertisingExpenseCommercial
,ISNULL(ae.ExpenseCount,0) as AdvertisingExpenseExpenseCount
--Services Expense
,ISNULL(se.AmountPaidTotal,0) as ServiceExpenseAmountPaid
,ISNULL(se.CommercialValueAmountTotal,0) as ServiceExpenseCommercial
,ISNULL(se.ExpenseCount,0) as ServiceExpenseExpenseCount
--Goods Supplied Expense
,ISNULL(gse.AmountPaidTotal,0) as GoodsSuppliedExpenseAmountPaid
,ISNULL(gse.CommercialValueAmountTotal,0) as GoodsSuppliedExpenseCommercial
,ISNULL(gse.ExpenseCount,0) as GoodsSuppliedExpenseExpenseCount
--Travel and Vehicle Expense
,ISNULL(thve.AmountPaidTotal,0) as TravelVehicleExpenseAmountPaid
,ISNULL(thve.CommercialValueAmountTotal,0) as TravelVehicleExpenseCommercial
,ISNULL(thve.ExpenseCount,0) as TravelVehicleExpenseExpenseCount
--All Expenses
,ISNULL(pe.AmountPaidTotal,0) 
    + ISNULL(hpe.AmountPaidTotal,0)
    + ISNULL(ae.AmountPaidTotal,0) 
    + ISNULL(se.AmountPaidTotal,0)
    + ISNULL(gse.AmountPaidTotal,0) 
    + ISNULL(thve.AmountPaidTotal,0) as AllExpenseAmountPaidTotal
,ISNULL(pe.CommercialValueAmountTotal,0) 
    + ISNULL(hpe.CommercialValueAmountTotal,0)
    + ISNULL(ae.CommercialValueAmountTotal,0) 
    + ISNULL(se.CommercialValueAmountTotal,0)
    + ISNULL(gse.CommercialValueAmountTotal,0) 
    + ISNULL(thve.CommercialValueAmountTotal,0) as AllExpenseCommercialValueTotal
,ISNULL(pe.ExpenseCount,0) 
    + ISNULL(hpe.ExpenseCount,0)
    + ISNULL(ae.ExpenseCount,0) 
    + ISNULL(se.ExpenseCount,0)
    + ISNULL(gse.ExpenseCount,0) 
    + ISNULL(thve.ExpenseCount,0) as AllExpenseCount
from EventRegistrations eer
left join pe on pe.EventRegistrationId = eer.EventRegistrationId
left join hpe on hpe.EventRegistrationId = eer.EventRegistrationId
left join ae on ae.EventRegistrationId = eer.EventRegistrationId 
left join se on se.EventRegistrationId = eer.EventRegistrationId
left join gse on gse.EventRegistrationId = eer.EventRegistrationId
left join thve on thve.EventRegistrationId = eer.EventRegistrationId

UPDATE:

Here is the db schema with inserts for those that are interested in seeing it live.

DB Schema and Inserts

Using SQL Server 2014 Standard I changed the db schema/inserts to a file (to large for here) which has more inserts as well as uploaded execution plan and results (2 images go side by side to show all returned columns)

Answer 1

Does anybody have a better alternative than this? Something faster?

Your original query will do table scans for all the 6 tables.

You can remove the distinct eer.EventRegistrationId and use GROUP BY eer.EventRegistrationId, rest everything remains the same.

Below indexes will help you avoid the TABLE SCAN and will do an 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) + Above Indexes

Query plan

statistics IO output

---

WITHOUT OPTION (MAXDOP 1) + Above Indexes

Query Plan:

Statistics IO output

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

Below is the code for completeness:

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

Note: you can fake rowcounts and pagecounts <-- ONLY FOR EDUCATIONAL REASONS

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

Answer 2

I filled the database with LOTS of data and I did some interesting findings.

Using these filtered indexes returns results in around 7 seconds in my system, compared to over 1 minute with the nonfiltered indexes suggested by 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

Plus the index recommended by Kin (which you probably already have since it seems to be the table's primary key):

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

It turns out that no clear winner exists in terms of elapsed time.

Your form of the query gets a very nice parallel plan using MERGE JOINs.

The query using UNION ALLs gets a much simpler serial plan.

As far as IO stats is concerned, it looks like the serial plan is slightly more efficient than the parallel plan:

Parallel plan IO stats

Serial plan IO stats

Elapsed times are very similar, but CPU time is higher for the parallel plan.

Remember that filtered indexes have some limitations, so they might not be the best choice for you.

Answer 3

Depending on your needs the answer might be to convert each CTE in your example to an indexed view. That would save the aggregations on every query at the expense of increased storage, and a touch slower CRUD operations. The basic format would be something like:

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

Repeat the same pattern above for each CTE. Then you would just have a VIEW like in your example but you LEFT JOIN to the new INDEXED VIEW's instead of the CTE's.

Since you mentioned that you're using Standard Edition it is worth knowing you will have to use the query hint of WITH (NOEXPAND) if you want the indexed views to behave like indexed views instead of normal views. Incidentally this is also currently true with Azure Sql Server databases.