如何刪除兩個表連接以優化速度的鍵查找？

我正在嘗試優化執行不佳的儲存過程的執行速度。我減少了幾個關鍵查找，相關查詢現在執行不到 2 分鐘。但是，理想情況下，對於數百行，它應該像以前一樣在半分鐘內執行。

• 實際計劃附在此處。

如果需要查找覆蓋索引，則無法解決此鍵查找，因為其先前的 INDEX 似乎反映了查詢中兩個表之間的 JOIN 謂詞。

題

有沒有更好的方法讓這個執行更快？有什麼建議？

編輯：附加查詢。

 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

查看 Key Lookup 的屬性中引用的列，並確保所有這些列都列在提供查找的索引的 INCLUDE 部分中。然後它應該消失。

引用自：https://dba.stackexchange.com/questions/154983