Skip to main content
added 681 characters in body
Source Link
Kin Shah
  • 62.4k
  • 6
  • 122
  • 240

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

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
Source Link
Kin Shah
  • 62.4k
  • 6
  • 122
  • 240

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

enter image description here

statistics IO output

enter image description here


WITHOUT OPTION (MAXDOP 1) + Above Indexes

Query Plan:

enter image description here

Statistics IO output

enter image description here

╔═════════════════════════════════╦═════╦═════════════╗
║             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