2

I am trying to optimze the execution speed of a poor running stored proc. I have reduced a couple of key lookups and the relevant query now runs under 2 minutes. But, ideally, for some hundred rows, it should be executed under half a minute as it used to be.

There is this key lookup which I am unable to resolve if I need to look for a covering INDEX, as its prior INDEX seems to reflect predicates for JOIN among two tables in the query.

Question

Is there is a better way to make this run faster? Any suggestions?

Edit: Query attached.

  select  distinct 
       ISNULL(emp.EMAILID,'') as EmployeeEmailId
       ,ast.category as [Employee Status]
       ,'' as BillingCycle
       ,0 as Id
       ,ISNULL(ast.Id,0) as WorkFlowId  
       ,isnull(emp.FULLNAME,bce.UserName) as FullName
       ,ast.AssetId
       ,ast.employeeid    
       ,avd.IsValidatedByEmployee
       ,'' as IsValidatedString
       ,avd.ValidatedByEmployeeLoginTokenId
       ,'' as ValidatedWhenByEmployee
       ,'' as EmployeeComments
       ,isnull(ast.FootPrintTicketNumber,'0') as FootPrintTicketNumber
       ,isnull(ast.CSCTicketNumber,'0') as CSCTicketNumber
       ,isnull(ast.ARSTicketNumber,'0') as ARSTicketNumber 
       ,0 as PingStatus
       ,isnull(ast.status,1) as WorkFlowStatus
       ,isnull(wfs.WorkFlowStatus,'Open') as WorkFlowStatusDesc
       ,ast.deptid
       ,isnull(ast.StatusComments,'') as StatusComments
       ,ast.statusowner
       ,ISNULL(ast.Closingstatus,0) as ClosingStatus
       ,ISNULL(cs.closingstatus,'') as ClosingstatusDesc
       ,(select (case when a.IsGoByReqdOnUI=1 then a.Goby else a.COMPANY end)   
           from tblwalioperatingunit a   
               join tblwaliRegion b on a.RegionId=b.RegionId    
               join tblwalibusinessunit c on a.BUId=c.BUId  
           where a.company=bce.[Org-tier2] and b.RegionDescription=bce.[Org-tier1]   
               and a.operatingunitdescription=bce.operatingunit and c.BUDescription=bce.BusinessUnit
        ) as CompanyName
        ,bce.operatingunit as DeptName
        ,(select top 1 cast(a.MonthlyTotal as decimal(10,2)) from tblbillingcscequipments a                                           
           where a.AssetID=bce.AssetID     
               and a.useremployeeid=bce.useremployeeid                                         
               and (a.departmentid=bce.departmentid  or a.OperatingUnit=bce.OperatingUnit)                                            
           order by a.Period desc) as  PerAssetCost
       ,(select cast((select cast((select top 1 a.MonthlyTotal from tblbillingcscequipments a    with(nolock)                                                 
                      where a.AssetID=bce.AssetID      
                           and a.useremployeeid=bce.useremployeeid                                                 
                           and (a.departmentid=bce.departmentid or a.operatingunit=bce.operatingunit)                                                                                                                  
                       order by a.Period desc)  as decimal(10,2))  / 
                   isnull((select top 1 cast(USDConversionRate as decimal(10,2))  
                           from tblCurrencyConversion where cycleid=bce.CycleID 
                               and childcycleid=bce.ChildCycleID),1.00))  as decimal(10,2)
                       )
           ) as LocalCurrencyValuePerAssetCost 
       ,(select isnull(sum(monthlytotal),0.00) from tblbillingcscequipments tbc    with(nolock)                                               
           where tbc.assetid=bce.assetid                                                 
             and tbc.useremployeeid=ast.employeeid
             and (tbc.departmentid=bce.departmentid or tbc.operatingunit=ast.deptid)
             and Period>= (Case when ast.category='Terminated' 
                               then(case when (asth.InsertedWhen < last_date_worked OR last_date_worked < '9/1/2015') then convert(date,asth.InsertedWhen)
                                   else convert(date,last_date_worked) end) 
                               else convert(date,asth.InsertedWhen) end 
                           )
           ) as TotalCost 
       ,(select cast((select cast((select isnull(sum(monthlytotal),0.00) from tblbillingcscequipments tbc    with(nolock)                                               
                       where tbc.assetid=bce.assetid                                                 
                         and tbc.useremployeeid=ast.employeeid
                         and (tbc.departmentid=bce.departmentid or tbc.operatingunit=ast.deptid)
                         and Period>= (Case when ast.category='Terminated' 
                                           then(case when (asth.InsertedWhen < last_date_worked OR last_date_worked < '9/1/2015') then convert(date,asth.InsertedWhen)
                                               else convert(date,last_date_worked) end) 
                                           else convert(date,asth.InsertedWhen) end 
                                       )
                       )as decimal(10,2))  / 
                       isnull((select top 1 cast(USDConversionRate as decimal(10,2))  
                               from tblCurrencyConversion   
                               where cycleid=bce.CycleID and childcycleid=bce.ChildCycleID),1.00))  as decimal(10,2)
                   )
           ) as LocalCurrencyValueTotalCost                        
       ,(Case when ast.category='Terminated' 
           then(case when (asth.InsertedWhen < last_date_worked OR last_date_worked < '9/1/2015') then convert(date,asth.InsertedWhen)
               else convert(date,last_date_worked) end) 
           else convert(date,asth.InsertedWhen) end 
       ) as EffectiveDate 
       ,convert(date,(select top 1 InsertedWhen from [tblAssetsStatus] a  with(nolock) where a.Id=ast.Id and status=3) )as CompletedDate
       ,convert(date,(select top 1 InsertedWhen from [tblAssetsStatus] a where a.Id=ast.Id)) as LastUpdatedDate
       ,convert(date,(select top 1 period from tblbillingcscequipments tbc    with(nolock)   
                       where tbc.assetid=bce.assetid                                                     
                            and tbc.useremployeeid=bce.useremployeeid                                                     
                            and tbc.operatingunit=bce.operatingunit     
                       order by period desc)
                   ) as LastAppearedDate
       ,u.Name as StatusChangedBy
       ,(select top 1 State from tblbillingcscequipments a  with(nolock)  where bce.AssetID=a.AssetID and bce.UserEmployeeId=a.UserEmployeeId 
                   and bce.OperatingUnit=a.OperatingUnit order by CycleID desc) as State  
       ,(select top 1 City from tblbillingcscequipments a  with(nolock)  where bce.AssetID=a.AssetID and bce.UserEmployeeId=a.UserEmployeeId 
                   and bce.OperatingUnit=a.OperatingUnit order by CycleID desc)  as City 
   from [tblAssetsStatus] ast    
       left join tblassetvalidationdataforemployee avd on ast.AssetId=avd.AssetID 
           and ast.employeeid=avd.EmployeeID 
           and ast.deptid=avd.DeptID 
           and (ast.category =(case when avd.IsValidatedByEmployee=0 OR avd.IsValidatedByManager=0 then 'no longer required'  
                               when avd.IsValidatedByEmployee=2 then 'Discovery' end) 
               or (ast.category='Reassign' and exists (select * from tblAssetsvalidationReassignment where assetvalidationId=avd.ID)))
           and avd.Submit=1
       left join tblBillingCycles bc on avd.CycleID=bc.CycleID
       left join tblAssetsvalidationReassignment avdr on avd.ID=avdr.assetvalidationId
       inner join tblbillingcscequipments  as bce on ast.AssetId=bce.AssetID 
           and         
               ((ast.employeeid=bce.UserEmployeeId and ast.category <> ('Reassign') and (avdr.OldEmployeeId is null or avdr.OldEmployeeId=bce.UserEmployeeId))        

           or            ((ast.employeeid=bce.UserEmployeeId and ast.category in ('Terminated','Surplus')))    

                   or (bce.UserEmployeeId=avdr.OldEmployeeId and ast.category in ('Reassign') )

                   or (bce.UserEmployeeId=avdr.OldEmployeeId and ast.employeeid=avdr.NewEmployeeId and ast.category in ('No longer Required','Discovery') )

                   or 
                       (ast.employeeid =(select top 1 newemployeeid 
                                           from tblemployeereassign 
                                           where filerowid=bce.filerowid)
                                               --and category in ('Reassign','no longer required', 'discovery')
                                               )
            )
           and (ast.deptid=bce.OperatingUnit or  ast.deptid=bce.DepartmentID or ast.deptid is null)
       inner join tblusagedescriptionmapping udm on bce.UsageDescription=udm.UsageDescription 
           and udm.CustomType in ('Desktop','Laptop','Tabelet','Tablet','Workstation','Laptop/Tablet/Handheld')    
       inner join tblWaliAssetTypes at on at.AssetType=bce.AssetTypeDescription
       left join tblemployees as emp on ast.employeeid = emp.employeeId                                                        
       inner join tblworkflowstatus as wfs on WorkFlowStatusId=ast.status                            
       inner  join tblclosingstatus as cs on closingstatusId  =ast.Closingstatus                          
       left join tblassetsstatushistory asth on ast.Id=asth.Id 
           and asth.Status=1
           and asth.insertedwhen=(select min(insertedwhen) from tblAssetsStatusHistory where id=ast.id and status=1)
       left outer join tblusers u  with(nolock) on u.logintokenid=ast.insertedby    
   where  bce.Period > (case when bce.operatingunit in (select operatingunit from tbloperatingUnitperiod)   
                               and bce.[org-tier2] in (select company from tbloperatingUnitperiod)  
                           then (select top 1 case when ast.category='No Longer Required' then Nolongerrequiredperiod else period end from tbloperatingUnitperiod   
                                           where tbloperatingUnitperiod.operatingunit=bce.operatingunit    
                                               and tbloperatingUnitperiod.company=bce.[Org-Tier2] )   
                           else '8/31/2015' end)  
       and ((isnull(emp.LAST_DATE_WORKED,bce.Period) <= bce.Period and ast.category='Terminated') or ast.category<>'Terminated' )
       and bce.operatingunit in (select item from [SplitString](@OperatingUnit,'~'))                                                 
       and bce.Businessunit=@BU                                      
       and bce.[Org-tier1] in (select item from SplitString(@Region,'~'))                                   
       and (bce.[Org-tier2]=@Company                                   
           or @Company=(select top 1 goby from tblwalioperatingunit where company=bce.[Org-tier2] 
                           and operatingunitdescription=bce.operatingunit) or @company='All')                                      
       and (ast.Status in (select * from dbo.[SplitStringwithIntOutput](@status,','))  or @status='')
       and (ast.statusowner=@statusowner or @statusowner='0')        

   order by TotalCost desc, fullname                  

End  
GO
  • 1
    Can you edit the question and add the query (and the table definitions)? – ypercubeᵀᴹ Nov 11 '16 at 18:03
  • @ypercubeᵀᴹ, I have attached the complete Query. Let me know if that helps, thank you – BeginnerDBA Nov 11 '16 at 18:26
  • 1
    So which 2 tables are the problem you are referring to in the question? I see 20, not 2 tables joined. – ypercubeᵀᴹ Nov 11 '16 at 18:40
  • @ypercubeᵀᴹ, yes that's correct. I was trying to analyze the plan and saw a key look up on TblAssetValidationDataForEmployee].[tblAssetstatus_ID_Clustered].(Clustered) with cost over 30%. I know that number is not the actual one but was of view on how to resolve this Key lookup. I am using sql sentry plan explorer to view the plan. – BeginnerDBA Nov 11 '16 at 18:47
  • @BeginnerDBA, why usage of TOP without an ORDER BY – Biju jose Nov 12 '16 at 10:34
3

Look at the columns being referred to in the Key Lookup's properties, and make sure all those columns are listed in the INCLUDE section of the index that is feeding the lookup. Then it should disappear.

  • thanks that's what I was doing but somehow did not clicked. However , when I made few columns in included column section of indexes moved to index key columns, plan changed and query started executing within 20 some seconds. I am still wandering how that made this change – BeginnerDBA Nov 14 '16 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.