mardi 3 février 2015

Sql Server Stored procedure exicution taken long time


I have worked in a web based project, in my code sometimes i want to execute a stored procedure more than 1000 times in a single button click. The button is used for some type of approval process and when user clicks the button we approve many things for him, so as the part of process we want to run a stored procedure continuously more than 1000 times. Each time take data from a view and store as xml document then parse that document then add each document as command text then execute the stored procedure. But the problem is it takes long time. 3-4 minutes to take to complete the process . i am looking for improving the performance. Anybody has any suggestion please tell me . i have included my sp below, any help will be greatly appreciated



ALTER Procedure [dbo].[sp_SaveEntry]
@CheckpointLogId int Output,
@AssignedBy int,
@AssignedTo int,
@CheckpointId int,
@EntityId int,
@CheckListId int,
@ChecklistHeadId int,
@UnitHeadId int,
@CompliancePeriodId int,
@SiteId int,
@Status int,
@Remark ntext,
@DuedateForCheckpoint Date,
@XMLEntry ntext,
@DisplayStatus int,
@XMLDispStatus ntext

As
Begin Transaction
declare @CheckpointName varchar(4000)
print convert(varchar(100), @Remark)
Select @CheckpointLogId = isnull(Max(CheckpointLogId),0) from AssignedCheckpointLogDetails
Set @CheckpointLogId = @CheckpointLogId +1
Insert into AssignedCheckpointLogDetails (CheckpointLogId, AssignedBy, AssignedTo, AssignedDate, CheckpointId, EntityId, CheckListId, CompliancePeriodId, SiteId, Status,Remark, DisplayStatus)
Values (@CheckpointLogId, @AssignedBy, @AssignedTo, getDate(), @CheckpointId, @EntityId,@CheckListId, @CompliancePeriodId, @SiteId, @Status,@Remark, @DisplayStatus)

If @@Error <> 0

Begin

goto Error

End
--Print 'select * from CMS_AssignedCheckPoint where CheckPointID = ' + @CheckpointId + ' and EntityId = ' + @EntityId + ' and CheckListId = ' + @CheckListId + ' and CompliancePeriodId = ' + @CompliancePeriodId + ' and SiteId = '+ @SiteId

if exists(select * from AssignedCheckPoint where CheckPointID = @CheckpointId and EntityId = @EntityId and CheckListId = @CheckListId and CompliancePeriodId = @CompliancePeriodId and SiteId = @SiteId)

Begin
print '1'
update AssignedCheckPoint Set AssignedBy=@AssignedBy,AssignedTo=@AssignedTo,AssignedDate=getDate(),
CheckPointID = @CheckpointId,EntityId = @EntityId,CheckListId = @CheckListId,
CompliancePeriodId = @CompliancePeriodId, SiteId = @SiteId,Status=@Status,Remark=@Remark, DisplayStatus = @DisplayStatus,Duedate_Checkpoint=@DuedateForCheckpoint
where CheckPointID = @CheckpointId and EntityId = @EntityId and CheckListId = @CheckListId and CompliancePeriodId = @CompliancePeriodId and SiteId = @SiteId
If @@Error <> 0
Begin
goto Error
End
End
Else
Begin
print '2'
Select @CheckpointLogId = isnull(Max(AssignedCheckpointId),0) from AssignedCheckPoint
Set @CheckpointLogId = @CheckpointLogId +1
select @CheckpointName=a.checklistname from checklisttemplate a ,cms_checkpoints b where a.ChecklistTemplateId=b.ChecklistTemplateId and b.checkpointid=@CheckpointId
Insert into AssignedCheckPoint (AssignedCheckpointId, AssignedBy, AssignedTo, AssignedDate, CheckpointId, EntityId, CheckListId, CompliancePeriodId, SiteId, Status,Remark, DisplayStatus,AssignedCheckPointName,AssignedCheckPointSno,DuedateCheckbox_Status)

Values (@CheckpointLogId, @AssignedBy, @AssignedTo, getDate(), @CheckpointId, @EntityId,@CheckListId, @CompliancePeriodId, @SiteId, @Status,@Remark, @DisplayStatus,convert(varchar(4000),@CheckpointName),(select Sno from CMS_Checkpoints where CheckPointID=@CheckpointId ),(select Checkbox_Status from Checkpoints where CheckPointID=@CheckpointId) )
If @@Error <> 0
Begin
goto Error
End
End
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XMLEntry
Delete from BusinessLogicEntryDetails where CompliancePeriodId = @CompliancePeriodId and EntityId=@EntityId and CheckpointId = @CheckpointId and SiteId = @SiteID

If @@Error <> 0
Begin
goto Error
End
Insert BusinessLogicEntryDetails
(CompliancePeriodId, EntityId, SiteId, CheckpointId, InputParamId, InputParamValue, OperatorId, OprParamId,
OprParamValue,ConditionId,NonConformance_Status,Mode,Type,OrderNo)
SELECT
@CompliancePeriodId, @EntityId,@SiteId, @CheckpointId, xmlEntity.InputParamId,
xmlEntity.InputParamValue,xmlEntity.OperatorId,xmlEntity.OprParamId, xmlEntity.OprParamValue,
xmlEntity.ConditionId, xmlEntity.NCStatus, xmlEntity.Mode, xmlEntity.Type,xmlEntity.OrderNo
FROM
OPENXML(@hDoc, 'Root/Data', 2) WITH(InputParamId int, InputParamValue nText, OperatorId int, OprParamId int,OprParamValue ntext,Mode int,
NCStatus int,ConditionId int, Type int,OrderNo int) as xmlEntity--, CMS_CheckpointEntryDetails
exec sp_xml_removedocument @hdoc
If @@Error <> 0
Begin
goto Error
End
Exec SaveCheckPointNCStatus @CheckpointId, @CompliancePeriodId, @EntityId, @SiteId, @CheckListId
If @@Error <> 0
Begin
goto Error
End
if Exists(Select * from checkpointdelegationstatus where CompliancePeriodId = @CompliancePeriodId and ChecklistId = @CheckListId and CheckpointId=@CheckpointId and EntityId=@EntityId and SiteId=@SiteId)

Begin
print '3'
update checkpointdelegationstatus set ChecklistHeadId=@ChecklistHeadId , CheckpointUserId=@AssignedTo, CheckpointStatus=@Status, UnitHeadId=@UnitHeadId
where CompliancePeriodId = @CompliancePeriodId and ChecklistId = @CheckListId and CheckpointId=@CheckpointId and EntityId=@EntityId and SiteId=@SiteId
If @@Error <> 0
Begin
goto Error
End
End
Else
Begin
print '4'
Insert checkpointdelegationstatus

(CompliancePeriodId, ChecklistId, ChecklistHeadId, CheckpointId,CheckpointUserId,CheckpointStatus,EntityId,SiteId,UnitHeadId)

Select @CompliancePeriodId,@CheckListId, @ChecklistHeadId, @CheckpointId, @AssignedTo, @Status, @EntityId,@SiteId,@UnitHeadId

If @@Error <> 0
Begin
goto Error
End
End
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XMLDispStatus
update AssignedCheckPoint Set DisplayStatus = 0
From
OPENXML(@hDoc, 'Root/Data', 2) WITH(CheckpointId int) as xmlEntity
where AssignedCheckPoint.CheckPointID = xmlEntity.CheckPointID and EntityId = @EntityId and CheckListId = @CheckListId and CompliancePeriodId = @CompliancePeriodId and SiteId = @SiteId
exec sp_xml_removedocument @hdoc
exec Sp_insertassignedentries @CheckpointId,@CompliancePeriodId,@EntityId
If @@Error <> 0
Begin
goto Error
End
Commit Transaction
goto End_Proc
Error:
Set @CheckpointLogId = 0
Rollback Transaction
End_Proc:


i had already go through this link but still the execution lags





Aucun commentaire:

Enregistrer un commentaire