Currently I'm facing an issue in a SQL Server retrieval procedure.
Stored procedure returns unexpected result when executed by many user (No. of users > 40) concurrently for the same parameter. For No of users <= 20 it shows expected result. Error rate is 0.07-0.10 % measured using jMeter.
The following SQL statement is expected to return one row. But under heavy load it sometimes returns no rows.
exec RetrievingNode @Keys='one,two,three'
Here is the procedure:
CREATE PROCEDURE [dbo].[RetrievingNode]
@Keys nvarchar(max)
as
Begin
SET NOCOUNT ON;
--***************************************
--***************************************
-- Turn On or Off updation of LastAccessDateTime
-- To Turn on set @TurnOnUpdation=1
-- To Turn Off set @TurnOnUpdation=0
declare @TurnOnUpdation as bit
set @TurnOnUpdation=1
--***************************************
--***************************************
declare @Variable as table(Value nvarchar(max),KeyedNodes_Id int,KeyNodeValue nvarchar(max),IsParent bit, IsReadOnly bit )-- this table will hold ids against which LastAccessDateTime can be updated.
declare @tbl_Keys table(id int identity(1,1),Value nvarchar(max))
--following will take all the keys into @tbl_Keys
insert into @tbl_Keys(Value)select Data from Split(@Keys,',')
DECLARE @count as int
DECLARE @counter as int
SET @counter=1
SELECT @count=COUNT(*) FROM @tbl_Keys
--Declare @ChildId as int
declare @ChildNodes table(id int,Value nvarchar(max),ParentNode_id int)
declare @ParentId as int
WHILE(@counter<=@count)
BEGIN
DECLARE @Key as nvarchar(max)
SELECT @Key=Value FROM @tbl_Keys WHERE id=@counter
if(@counter=1)
begin
if not exists(select * from keyednodes(nolock) KN where KN.Value=@Key and KN.ParentNode_Id is null)
BEGIN
---node does not exists
Break;
END
declare @ValueExistsForFirstKey as bit
set @ValueExistsForFirstKey=0
insert into @ChildNodes
select k2.Id,k2.Value,k2.ParentNode_Id from keyednodes(nolock) k1 inner join keyednodes(nolock) k2 on k1.id=k2.ParentNode_id where K1.value=@Key and k1.ParentNode_Id is null
if(@count=1)
BEGIN
IF EXISTS(
select GV.Value,GV.KeyedNodes_Id,KN.Value from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV
on KN.Id=GV.KeyedNodes_Id and KN.Value=@Key and KN.ParentNode_Id is null
)
BEGIN -- If value of node exists
SET @ValueExistsForFirstKey=1
insert into @Variable
select GV.Value,GV.KeyedNodes_Id,KN.Value,1,GV.ReadOnly from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV
on KN.Id=GV.KeyedNodes_Id and KN.Value=@Key and KN.ParentNode_Id is null
END
ELSE IF NOT EXISTS(select * from @ChildNodes)
BEGIN
insert into @Variable select -1,-1,-1,0,0 --Node exists but No value or children exists
GOTO ExitFromProcedure
END
IF(@counter=@count)
BEGIN
if exists(select Id,Value,ParentNode_Id from keyednodes(nolock) KN where KN.Value=@Key and KN.ParentNode_Id is null)
BEGIN
if(@ValueExistsForFirstKey=0)and (not exists(select * from @ChildNodes))
BEGIN
insert into @Variable select -1,-1,-1,0,0
GOTO ExitFromProcedure
END
END
END
END
end
else
begin
declare @KeyIsChild as int
set @KeyIsChild=0
if exists(select * from @ChildNodes where Value=@Key)
begin
set @KeyIsChild=1
End
ELSE
BEGIN
--Key path does not exist
--print 'key path does not exist'
GOTO ExitFromProcedure
ENd
if not exists(select * from @ChildNodes where Value=@Key)
BEGIN
set @ParentId=0
END
ELSE
BEGIN
select @ParentId=Id from @ChildNodes where Value=@Key
ENd
delete @ChildNodes
if(@KeyIsChild=1)
begin
insert into @ChildNodes
select k2.Id,k2.Value,k2.ParentNode_Id from keyednodes(nolock) k1 inner join keyednodes(nolock) k2 on k1.id=k2.ParentNode_id where k2.ParentNode_Id=@ParentId
end
end
SET @counter=@counter+1
END
if exists(select * from @ChildNodes)
begin
IF EXISTS
(
select GV.Value,CN.id,CN.Value from @ChildNodes CN left outer join GlobalVariables(nolock) GV on CN.Id=GV.KeyedNodes_Id --children
union all
select GV.Value,GV.KeyedNodes_Id,KN.Value from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV
on KN.Id=GV.KeyedNodes_Id and KN.Id=@ParentId--children
)
BEGIN
insert into @Variable
select GV.Value,CN.id,CN.Value,0,GV.ReadOnly from @ChildNodes CN left outer join GlobalVariables(nolock) GV on CN.Id=GV.KeyedNodes_Id --children
union all
select GV.Value,GV.KeyedNodes_Id,KN.Value,1,GV.ReadOnly from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV
on KN.Id=GV.KeyedNodes_Id and KN.Id=@ParentId--children
END
ELSE
BEGIN
if(@count <>1)
insert into @Variable select -1,-1,-1,0,0
if(@count=1) and not exists(select * from @Variable)
insert into @Variable select -1,-1,-1,0,0
END
end
else
Begin
if (@KeyIsChild=1)
BEGIN
IF EXISTS
(
select GV.Value,GV.KeyedNodes_Id,KN.Value from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV
on KN.Id=GV.KeyedNodes_Id and KN.Id=@ParentId
)
BEGIN
---IF value of the node exists
insert into @Variable
select GV.Value,GV.KeyedNodes_Id,KN.Value,1,GV.ReadOnly from keyednodes(nolock) KN inner join GlobalVariables(nolock) GV
on KN.Id=GV.KeyedNodes_Id and KN.Id=@ParentId --node
END
ELSE
BEGIN
insert into @Variable select -1,-1,-1,0,0 --Node exists but No value or children exists
END
END
End
----
ExitFromProcedure:
select KeyedNodes_Id,KeyNodeValue,Value,IsParent,Isnull(IsReadOnly,0)as IsReadOnly from @Variable order by IsParent desc, KeyNodeValue asc
--update LastAccessDateTime
IF(@TurnOnUpdation=1)
BEGIN
IF EXISTS(select * from @Variable)
BEGIN
UPDATE GlobalVariables SET LastAccessDateTime=getdate()
WHERE KeyedNodes_Id IN (SELECT KeyedNodes_Id FROM @Variable)
END
END
End
Go
CREATE FUNCTION [dbo].[Split]
(
@RowData nvarchar(max),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(max)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
Go
