We are facing issues in our Coding application and database.Our coding application having front end asp.net 3.5 and back end SQL Server 2008 R2.Based on project requirement we doing fields setting for coding.We facing issues of coding application & database getting more time (hang) during the coding process. Below mentioned testing observation
Test Batch Records Coding Completed QC Completed Users Fields Record Saving Time Remarks
1 Batch1 437 437 437 15-20 4 2 - 5 Sec No Issue
2 Batch2 882 882 882 15-20 4 2 - 5 Sec No Issue
3 Batch3 1426 1426 1426 15-20 5 2 - 5 Sec No Issue
4 Batch4 5761 1252 531 15-20 1 2 - 5 Sec No Issue
5 Batch5 1675 448 357 15-20 5 2 - 5 Sec No Issue
6 Batch6 247 247 247 15-20 4 2 - 5 Sec No Issue
7 Batch7 665 665 665 15-20 4 2 - 5 Sec No Issue
8 Batch8 224 224 224 15-20 2 First 2-5 Sec After 200 takes 5 - 10 Sec No Issue
9 Batch9 790 250 250 15-20 2 First 2-5 Sec After 200 takes 5 - 10 Sec Hanging start
10 Batch10 573 282 273 15-20 1 First 2-5 Sec After 200 takes 5 - 10 Sec Hanging start after 250 records
We run the trace and found sp FetchDocumentID taking more time. We execute the sp in SQL Server by selecting Executing plan and not found any missing Indexes.
Below mention Sp structure
CREATE PROCEDURE [dbo].[FetchDocumentID]
(@i_UserId int,
@i_ProjectId int)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Error INT = 0
, @ErrorLine INT
, @ErrorDesc NVARCHAR(1000)
, @Message NVARCHAR(1000)
, @ProcName NVARCHAR(128) = N'Project.dbo.FetchDocumentID.procedure.sql'
, @VALUES VARCHAR(128)
, @UserDetailId BIGINT
, @RoleName VARCHAR(150)
BEGIN TRY
CREATE TAble #Document (Id INT IDENTITY(1,1) PRIMARY KEY
,FieldId INT
, DocumentID INT
, Rejected INT )
IF NOT EXISTS(SELECT UA.AccessId
FROM dbo.UserAccess UA
WHERE UA.ProjectMasterId = @i_ProjectId
AND UA.AccessId = 4
)
BEGIN
INSERT #Document (DocumentID,Rejected )
SELECT TOP 1 [DM].[DocumentId],1
FROM [dbo].[DataMaster] DM WITH(NOLOCK)
LEFT JOIN [dbo].[DocumentMaster] DC WITH(NOLOCK)
ON [DM].[DocumentId] = [DC].[DocumentMasterId]
WHERE [DM].[IsCoded] = 1
AND [DM].[IsRejected]=1
AND [DM].CoderId = @i_UserId
AND [DC].[ProjectId] = @i_ProjectId
AND (([DC].[IsLocked] = 1 and [DC].[Status] = @i_UserId)
OR ([DC].[IsLocked] = 0 AND [DC].[IsChecked] = 1 ))
GROUP BY [DM].[DocumentId]
ORDER BY [DM].[DocumentId]
END
IF NOT EXISTS(SELECT * FROM #Document)
BEGIN
INSERT #Document (FieldId
,DocumentID)
SELECT A.FieldId
, B.DocumentMasterId
FROM dbo.UserAccess A WITH(NOLOCK)
INNER JOIN dbo.DocumentMaster B WITH(NOLOCK)
ON A.ProjectMasterId = b.ProjectId
WHERE A.[UserMasterId] =@i_UserId
AND A.ProjectMasterId=@i_ProjectId
AND (B.IsLocked = 0 OR (B.IsLocked = 1 AND B.[STATUS] = @i_UserId) )
CREATE TABLE #temp1(DocID INT )
INSERT #temp1
SELECT TOP 1 A.DocumentID
FROM #Document A
LEFT JOIN DataMaster b
ON a.FieldId = b.FieldId
AND a.DocumentID = b.DocumentId
WHERE b.DocumentId IS NULL
GROUP BY A.DocumentID
ORDER BY a.DocumentID
if EXISTS(select * from #temp1)
Begin
UPDATE [dbo].[DocumentMaster]
SET [IsLocked] = 1
,[LockedTime] = SYSDATETIME()
,[Status] = @i_UserId
WHERE [DocumentMasterId] = (select DocID from #temp1)
End
SELECT TOP 1 A.DocumentID
, 0 AS Rejected
FROM #Document A
LEFT JOIN DataMaster b
ON a.FieldId = b.FieldId
AND a.DocumentID = b.DocumentId
WHERE b.DocumentId IS NULL
GROUP BY A.DocumentID
ORDER BY a.DocumentID
END
ELSE
BEGIN
UPDATE [dbo].[DocumentMaster]
SET [IsLocked] = 1
,[LockedTime] = SYSDATETIME()
,[Status] = @i_UserId
WHERE [DocumentMasterId] = (select DocumentID from #Document)
SELECT A.DocumentID,Rejected
FROM #Document A
ORDER BY A.DocumentID
END
END TRY
BEGIN CATCH
SELECT @Error = ERROR_NUMBER()
, @ErrorLine = ERROR_LINE()
, @ErrorDesc = ERROR_MESSAGE()
SET @Message = N'Application of the procedure, %s, failed at line, %d, due to error no. %d.: %s'
RAISERROR(@Message, 11, 1, @ProcName, @ErrorLine, @Error, @ErrorDesc) WITH NOWAIT
END CATCH
END_THIS:
RETURN (@Error)
END--FetchDocumentID
Can anyone help me to resolve hang issue.
Thank you in advance