Slow Complex Query

Question

i am new in SQL and this is first project i am trying to do, so

i have some issue with query, it was run in 8 min and i make a unique clustered then it helps to run in 2 to 3 min, while i was searching i found execution plan as i upload it takes too much time, how can i reduce it please

select 
    tblStockmb5b.[Material] ,
    [Plnt]  ,
    sum(distinct [opening stock]) as [opening stockM],
    sum(distinct [closing stock]) as [closing stockM],
    sum(distinct tblCOGSPrice.unitvalue* [Opening Stock]) as TotalOpeningStockCOGS ,
    sum(distinct tblCOGSPrice.unitvalue * [Closing Stock]) as TotalClosingStockCOGS ,
    (sum(distinct tblCOGSPrice.unitvalue* [Opening Stock]) + sum(distinct tblCOGSPrice.unitvalue * [Closing Stock]))/2 as AverageStockPerMonth,
    sum(tblConsumtion261.[Quantity]) as TotalMonthDemand,
    sum(tblConsumtion261.[Quantity] * tblCOGSPrice.unitvalue) as TotalannualDemand,
    ( (sum(distinct tblCOGSPrice.unitvalue* [Opening Stock]) + sum(distinct tblCOGSPrice.unitvalue * [Closing Stock]))/2 )/ count(distinct [EomonthMB5b]) as AverageInventoryValue,
    iif(sum(tblConsumtion261.[Quantity]* tblCOGSPrice.unitvalue) = 0 , Null,sum(tblConsumtion261.[Quantity]* tblCOGSPrice.unitvalue) ) /  datediff(day,'2020-01-01',GETDATE()) as AverageDailyCOGS ,
    iif( sum(tblConsumtion261.[Quantity] * tblCOGSPrice.unitvalue) = 0 , Null , sum(tblConsumtion261.[Quantity] * tblCOGSPrice.unitvalue) ) / 
    (iif(( (sum(distinct tblCOGSPrice.unitvalue* [Opening Stock]) + sum(distinct tblCOGSPrice.unitvalue * [Closing Stock]))/2 )= 0 , Null ,
    ( (sum(distinct tblCOGSPrice.unitvalue* [Opening Stock]) + sum(distinct tblCOGSPrice.unitvalue * [Closing Stock]))/2 ) )/ count(distinct [EomonthMB5b]) )as [Inv.Turnover Ratio] ,
    (( (sum(distinct tblCOGSPrice.unitvalue* [Opening Stock]) + sum(distinct tblCOGSPrice.unitvalue * [Closing Stock]))/2 )/ count(distinct [EomonthMB5b]) )/ 
    (iif(sum(tblConsumtion261.[Quantity]* tblCOGSPrice.unitvalue) = 0 , Null,sum(tblConsumtion261.[Quantity]* tblCOGSPrice.unitvalue) ) /  datediff(day,'2020-01-01',GETDATE())) as IDS
    from tblStockmb5b
    left join tblCOGSPrice
        on tblStockmb5b.[Material] = tblCOGSPrice.[Material] and tblStockmb5b.[plnt] = tblCOGSPrice.[Plant] and tblStockmb5b.[EomonthMB5b] = tblCOGSPrice.[EomonthC9]
    left join tblConsumtion261
        on tblStockmb5b.[Material] = tblConsumtion261.[Material] and tblStockmb5b.[plnt] = tblConsumtion261.[Plant] and tblStockmb5b.[EomonthMB5b] = tblConsumtion261.[Eomonth261] 

    group by  tblStockmb5b.[Material] ,  [Plnt] 
    order by tblStockmb5b.[Material] , [Plnt] 

Answer 1

thank you i figer out the solution based on what you said about distinct and re-query that to CTE's now it Runs in 4 sec

with cteTblConsumtion261([Material],[Plant],[TotalMonthDemand],[TotalannualDemand],[AverageDailyCOGS])
         as (
            
            select TblConsumtion261.[Material],
                   TblConsumtion261.[Plant],
                   sum(tblConsumtion261.[Quantity]) as TotalMonthDemand,
                   sum(tblConsumtion261.[Quantity] * tblCOGSPrice.unitvalue) as TotalannualDemand,
                   iif(sum(tblConsumtion261.[Quantity]* tblCOGSPrice.unitvalue) = 0 , Null,sum(tblConsumtion261.[Quantity]* tblCOGSPrice.unitvalue) ) / 
                    datediff(day,'2020-01-01',GETDATE()) as AverageDailyCOGS
                
            from TblConsumtion261
            left join tblCOGSPrice
            on TblConsumtion261.[Material] = tblCOGSPrice.[Material] and TblConsumtion261.[plant] = tblCOGSPrice.[Plant] and TblConsumtion261.[Eomonth261] = tblCOGSPrice.[EomonthC9]
            group by  TblConsumtion261.[Material] ,  TblConsumtion261.[Plant] 
            ), ctetblStockMb5b([Material], [Plnt],[opening stockM],[closing stockM],[TotalOpeningStockCOGS],[TotalClosingStockCOGS],[AverageStockPerMonth],[AverageInventoryValue])
              as (
            
            select  tblStockmb5b.[Material] ,
                    [Plnt]  ,
                    sum( [opening stock]) as [opening stockM],
                    sum( [closing stock]) as [closing stockM],
                    sum( tblCOGSPrice.unitvalue* [Opening Stock]) as TotalOpeningStockCOGS ,
                    sum( tblCOGSPrice.unitvalue * [Closing Stock]) as TotalClosingStockCOGS ,
                    iif((sum( tblCOGSPrice.unitvalue* [Opening Stock]) + sum( tblCOGSPrice.unitvalue * [Closing Stock])) = 0 , Null , 
                    (sum( tblCOGSPrice.unitvalue* [Opening Stock]) + sum( tblCOGSPrice.unitvalue * [Closing Stock])) ) /2 as AverageStockPerMonth,
                    (iif(( (sum( tblCOGSPrice.unitvalue* [Opening Stock]) + sum( tblCOGSPrice.unitvalue * [Closing Stock]))/2 ) = 0 , Null , 
                    ( (sum( tblCOGSPrice.unitvalue* [Opening Stock]) + sum( tblCOGSPrice.unitvalue * [Closing Stock]))/2 ) )/ 20) as AverageInventoryValue
    
                from tblStockmb5b
            left join tblCOGSPrice
            on tblStockmb5b.[Material] = tblCOGSPrice.[Material] and tblStockmb5b.[plnt] = tblCOGSPrice.[Plant] and tblStockmb5b.[EomonthMB5b] = tblCOGSPrice.[EomonthC9]
            group by  tblStockmb5b.[Material] ,  [Plnt] 

            )

            select ctetblStockMb5b.[Material] ,
                   ctetblStockMb5b.[Plnt] , 
                   ctetblStockMb5b.[opening stockM], 
                   ctetblStockMb5b.[closing stockM],
                   ctetblStockMb5b.TotalOpeningStockCOGS,
                   ctetblStockMb5b.TotalClosingStockCOGS,
                   ctetblStockMb5b.AverageStockPerMonth,
                   cteTblConsumtion261.TotalMonthDemand,
                   cteTblConsumtion261.TotalannualDemand,
                   ctetblStockMb5b.AverageInventoryValue,
                   cteTblConsumtion261.AverageDailyCOGS, 
                   TotalannualDemand / (AverageStockPerMonth /20) as [Inv.Turnover Ratio] , 
                   (AverageStockPerMonth /20) / AverageDailyCOGS  as IDS 

            from  ctetblStockMb5b 
            left join cteTblConsumtion261 
            on ctetblStockMb5b.[Material] = cteTblConsumtion261.Material and ctetblStockMb5b.Plnt = cteTblConsumtion261.Plant
                    group by  ctetblStockMb5b.[Material] ,
                   ctetblStockMb5b.[Plnt] , 
                   ctetblStockMb5b.[opening stockM], 
                   ctetblStockMb5b.[closing stockM],
                   ctetblStockMb5b.TotalOpeningStockCOGS,
                   ctetblStockMb5b.TotalClosingStockCOGS,
                   ctetblStockMb5b.AverageStockPerMonth,
                   ctetblStockMb5b.AverageStockPerMonth,
                   cteTblConsumtion261.TotalMonthDemand,
                   cteTblConsumtion261.TotalannualDemand ,
                   cteTblConsumtion261.AverageDailyCOGS, 
                    ctetblStockMb5b.AverageInventoryValue,
                   TotalannualDemand / (AverageStockPerMonth /20)  , 
                  (AverageStockPerMonth /20) / AverageDailyCOGS