改善以下存储过程的执行时间 [英] improve execution time of the following stored procedure

问题描述

CREATE PROCEDURE [dbo].[SP_RPT_DefectList]         
 (        
         @WhereClause NVARCHAR(2000) = NULL        
 )        
 AS        
 BEGIN        
         DECLARE @SelectStatement NVARCHAR(2000)        
         DECLARE @FullStatement NVARCHAR(4000)             
        
         SET @SelectStatement = ''SELECT  DefectListId,
										  CONVERT(Varchar(12),DateOfEntry,101)DateOfEntry,
										  U.UserName as EnteredBy,
										  dbo.getDefectlistfromName(RefBy) RefBy,
										  dbo.GuiId(EquipmentGuid)EquipmentGuid,
										  DefectDescription,
										  SparesStoresRequiredRef,
										  ShoreAssistanceRequired,
										  DockingRequired,
										  Remarks,
										  Status,
										  CONVERT(Varchar(12),TargetDate,101)TargetDate 
										  FROM dbo.PMS_TBL_DEFECT_LIST D 
										  left outer join dbo.PMS_TBL_USERS U 
										  on U.UserId=D.EnteredBy 
										  LEFT OUTER JOIN dbo.PMS_TBL_SUB_EQUIPMENT_MASTER S 
										  ON S.UniqueId=D.EquipmentGuid
										  LEFT OUTER JOIN dbo.PMS_TBL_EQUIPMENT_MASTER E 
										  ON E.UniqueId=D.EquipmentGuid  OR E.EquipmentId=S.EquipmentId
										  LEFT OUTER JOIN dbo.PMS_TBL_SUB_FUNCTIONAL_BLOCK_MASTER SF 
										  ON SF.UniqueId=D.EquipmentGuid 
										  Or SF.SubFunctionalBlockId=E.SFBId
										  LEFT OUTER JOIN dbo.PMS_TBL_FUNCTIONAL_BLOCK_MASTER F 
										  ON F.FunctionalBlockId=SF.FBId  ''        
         SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'''')             
        
         PRINT @FullStatement        
         EXECUTE sp_executesql @FullStatement             
 END

任何人都建议我改进上述存储过程的执行时间.....

Can any one suggest me to improve the execution time of the above stored procedure.....

推荐答案

首先确保你有适当的索引在表格上 - 见上一篇文章以下是我的存储过程可以任何人建议我改进执行时间 [