Click here to Skip to main content
15,888,090 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
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.....
Posted
Updated 8-Oct-15 1:38am
v2
Comments
Maciej Los 8-Oct-15 7:38am    
What's an issue?
[no name] 8-Oct-15 7:39am    
need to improve query execution time ,because its getting too much time ...
CHill60 8-Oct-15 7:55am    
Have you applied all of the things that were suggested on your previous post - the following is my stored procedure can any one suggest me the improvement of execution time[^]?
F-ES Sitecore 8-Oct-15 8:52am    
Make a copy of your SP that doesn't call the functions and time it against the one that does. If there is a large difference then the functions are probably to blame for your poor performance, especially if you are retrieving many rows.

1 solution

Firstly make sure you have appropriate indexes on the tables - see your previous post the following is my stored procedure can any one suggest me the improvement of execution time[^]

Next - why are you doing the conversions on dates
CONVERT(Varchar(12),DateOfEntry,101)DateOfEntry
...
CONVERT(Varchar(12),TargetDate,101)TargetDate
Don't do the conversion in the data retrieval - only do that in the presentation layer.

You're joining across a lot of tables and using OR in the ON clauses - is there any way you can reduce the number of records that are being fed into these joins - e.g. use a CTE to get the key records with the WHERE clause then join to get the additional information.

You're also calling two functions
VB
dbo.getDefectlistfromName(RefBy) RefBy,
dbo.GuiId(EquipmentGuid)EquipmentGuid,
Are they efficient (you haven't shared that code)? Do they actually need to be there or can they be used immediately prior to presentation of the results
 
Share this answer
 
Comments
Maciej Los 8-Oct-15 9:19am    
Holy words!
+5!
CHill60 8-Oct-15 9:34am    
:-)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900