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

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 ServiceExp

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.

StackExchange Database Administrators Q#112856, answer score: 3