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