如何优化这个sp [英] how to optimize this sp

问题描述

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_DeleteRoadMapRelations]
       -- Add the parameters for the stored procedure here
       @RoadMapGuid uniqueidentifier,
       @StatusMessage varchar(50) OUTPUT  
AS
BEGIN
     
   -- DELETE RoadMap Document Mappings
   declare @IsTemplate bit
   DECLARE @DocumentGuid uniqueidentifier
	DECLARE @getDocument CURSOR
	SET @getDocument = CURSOR FOR
	SELECT DocumentGuid
	FROM RoadmapDocumentMapping where RoadmapGuid = @RoadMapGuid
   select @IsTemplate = IsTemplate from Roadmap where RoadmapGuid = @RoadMapGuid
   if @IsTemplate = 0
   begin
   		OPEN @getDocument
		FETCH NEXT
		FROM @getDocument INTO @DocumentGuid
		WHILE @@FETCH_STATUS = 0
		BEGIN		
			if exists(select * from Document where DocumentGuid = @DocumentGuid and IsFromTemplate = 0)
			begin
				delete from RoadmapDocumentMapping where DocumentGuid = @DocumentGuid
				delete from DocumentVersion where DocumentGuid = @DocumentGuid
				delete from Document where DocumentGuid = @DocumentGuid
			end
			else
			begin
				delete from RoadmapDocumentMapping where DocumentGuid = @DocumentGuid and RoadmapGuid = @RoadMapGuid
			end
		
			FETCH NEXT
			FROM @getDocument INTO @DocumentGuid
		END
		CLOSE @getDocument
		DEALLOCATE @getDocument
   end
   else
   begin
		OPEN @getDocument
		FETCH NEXT
		FROM @getDocument INTO @DocumentGuid
		WHILE @@FETCH_STATUS = 0
		BEGIN		
			delete from RoadmapDocumentMapping where DocumentGuid = @DocumentGuid
			delete from DocumentVersion where DocumentGuid = @DocumentGuid
			delete from Document where DocumentGuid = @DocumentGuid
					
			FETCH NEXT
			FROM @getDocument INTO @DocumentGuid
		END
		CLOSE @getDocument
		DEALLOCATE @getDocument
   end
	
	IF @@ERROR <> 0 
	BEGIN 
		SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapDocumentMapping'
		RETURN
	END 
	
	-- DELETE RoadMap History
	DELETE FROM RoadmapHistory WHERE RoadmapGuid = @RoadMapGuid
	IF @@ERROR <> 0
	BEGIN 
		SELECT @StatusMessage = 'Error while deleting FROM RoadmapHistory RoadmapGuid Column'
		RETURN
	END
	
	--ProcessProcedureMapping
	DELETE FROM ProcessProcedureMapping WHERE  ProcedureGuid = @RoadMapGuid
	IF @@ERROR <> 0
	BEGIN 
		SELECT @StatusMessage = 'Error while deleting FROM ProcessProcedureMapping ProcedureGuid Column'
		RETURN
	END
	
	--RoadmapKBArticleMapping
	DELETE FROM RoadmapKBArticleMapping where RoadmapGuid = @RoadMapGuid
	IF @@ERROR <> 0 
	BEGIN 
		SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapKBArticleMapping'
		RETURN
	END
	--RoadmapNotesMapping
	DELETE FROM RoadmapNotesMapping where RoadmapGuid = @RoadMapGuid
	IF @@ERROR <> 0 
	BEGIN 
		SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapNotesMapping'
		RETURN
	END    
	--WorkItemRoadmapMapping
	DELETE FROM WorkItemRoadmapMapping where RoadmapGuid = @RoadMapGuid
	IF @@ERROR <> 0 
	BEGIN 
		SELECT @StatusMessage = 'Error while DELETE FROM table WorkItemRoadmapMapping'
		RETURN
	END
	--CustomEntityRoadmapMapping
	DELETE FROM CustomEntityRoadmapMapping where RoadmapGuid = @RoadMapGuid
	IF @@ERROR <> 0 
	BEGIN 
		SELECT @StatusMessage = 'Error while DELETE FROM table CustomEntityRoadmapMapping'
		RETURN
	END
	--RoadmapCustomFormTemplateMapping
	DELETE FROM RoadmapCustomFormTemplateMapping where RoadmapGuid = @RoadMapGuid
	IF @@ERROR <> 0 
	BEGIN 
		SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapCustomFormTemplateMapping'
		RETURN
	END
	-- RoadmapCustomFormMapping
	DELETE FROM RoadmapCustomFormMapping where RoadmapGuid = @RoadMapGuid
	IF @@ERROR <> 0 
	BEGIN 
		SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapCustomFormMapping'
		RETURN
	END 
	
	
	update RoadmapTaskMapping set ParentPTMappingGuid = null where ParentPTMappingGuid in
	(select RoadmapTaskMappingGuid from  RoadmapTaskMapping where ProcedureGuid = @RoadMapGuid)
	IF @@ERROR <> 0 
	BEGIN
		SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapTaskMapping'
		RETURN	
	END 
	
	DELETE FROM RoadmapTaskPredecessorMapping where SuccessorPTMappingGuid in
	(select RoadmapTaskMappingGuid from  RoadmapTaskMapping where ProcedureGuid= @RoadMapGuid)
	IF @@ERROR <> 0 
	BEGIN
		SELECT @StatusMessage = 'Error while DELETE FROM table Successor-RoadmapTaskMapping'
		RETURN
	END
	
	DELETE FROM RoadmapTaskPredecessorMapping where PredecessorPTMappingGuid in
	(select RoadmapTaskMappingGuid from  RoadmapTaskMapping where ProcedureGuid = @RoadMapGuid)
	IF @@ERROR <> 0 
	BEGIN
		SELECT @StatusMessage = 'Error while DELETE FROM table Predecessor-RoadmapTaskMapping'
		RETURN
	END
	
	DELETE FROM RoadmapTaskMapping where ProcedureGuid = @RoadMapGuid
	IF @@ERROR <> 0 
	BEGIN
		SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapTaskMapping'
		RETURN
	END 
	
	--WPMCustomEntityRoadmapMapping
	DELETE FROM WPMCustomEntityRoadmapMapping where RoadMapGuid = @RoadMapGuid
	IF @@ERROR <> 0 
	BEGIN
		SELECT @StatusMessage = 'Error while DELETE FROM table WPMCustomEntityRoadmapMapping'
		RETURN
	END
	
	--BERoadmapMapping
	DELETE FROM BusinessEntityRoadmapMapping where RoadMapGuid = @RoadMapGuid
	IF @@ERROR <> 0 
	BEGIN
		SELECT @StatusMessage = 'Error while DELETE FROM table BusinessEntityRoadmapMapping'
		RETURN
	END	
SELECT @StatusMessage = 'Success'
 END

推荐答案

同意NeverJustHere。



0 ）避免使用游标。

光标备选方案 [ ^ ]

1 ）并改进异常处理。

SQL Server 2005中的错误处理概述 [ ^ ]

仔细研究RAISERROR - SQLServer 2005 [ ^ ]

2 ）对于，使用 EXISTS 而不是 IN 删除查询。

N ）检查这个答案，我已经解释了许多主题的内容以改进。

如何提高网站的效果？ [ ^ ] br />
检查数据库优化是否存在问题。

Agree with NeverJustHere.

0) Avoid Cursors.
Cursor alternatives[^]
1) And improve the exception handling.
Overview of Error Handling in SQL Server 2005[^]
A Closer Look Inside RAISERROR - SQLServer 2005 [^]
2) Use EXISTS instead of IN for DELETE queries.
N) Check this answer, I have explained things on many topic to improve things.
How to improve performance of a website?[^]
Check Database optimization for your issue.

这篇关于如何优化这个sp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！