title: How to Improve performance of sql recursive scalar function?
body: <p>I've created a SQL recursive scalar function that generate a JSON it runs for 1600 records and generates a JSON in 30 seconds i want to reduce this time how can i do this ? please the code below.</p>
  
  <pre><code>ALTER  FUNCTION [dbo].[function_Json_by_shumail](@POSITIONID INT, @IsRoot INT ) 
  RETURNS VARCHAR(MAX)
  
  BEGIN 
      DECLARE @Json VARCHAR(MAX) = '{}', @Name NVARCHAR(MAX), @Title NVARCHAR(MAX), @Phone NVARCHAR(MAX), @Company NVARCHAR(MAX), @Email NVARCHAR(MAX) , @Children    NVARCHAR(MAX)
  
      SET @Json =  
      (SELECT P.Name as name , ISNULL(P.POSTIONTITTLE,'') as title, ISNULL(P.primarycontactemail,'') as email , ISNULL(P.PRIMARYCONTACTPHONE,'') as phone , ISNULL(P.[COMPANY NAME],'') as company ,JSON_QUERY(dbo.function_Json_by_shumail(P.POSITIONID, 2) ) AS children 
      FROM [dbo].[VW_WorkerHierarchyUpdated] AS P  
      WHERE P.PARENTPOSITIONID = @POSITIONID
      FOR JSON AUTO);
  
      IF(@IsRoot = 0) 
      BEGIN
         SELECT @Name = P.Name FROM [dbo].[VW_WorkerHierarchyUpdated] AS P WHERE P.PARENTPOSITIONID is Null
         SELECT @Title = ISNULL(P.POSTIONTITTLE,'') FROM [dbo].[VW_WorkerHierarchyUpdated] AS P WHERE P.PARENTPOSITIONID is Null
         SELECT @Phone = ISNULL(P.PRIMARYCONTACTPHONE,'') FROM [dbo].[VW_WorkerHierarchyUpdated] AS P WHERE P.PARENTPOSITIONID is Null
         SELECT @Company = ISNULL(P.[COMPANY NAME],'') FROM [dbo].[VW_WorkerHierarchyUpdated] AS P WHERE P.PARENTPOSITIONID is Null
         SELECT @Email = ISNULL(P.primarycontactemail,'') FROM [dbo].[VW_WorkerHierarchyUpdated] AS P WHERE P.PARENTPOSITIONID is Null
         SET @Json =   '{"name":"' + @Name + '","title":"' + @Title + '","email":"' + @Email + '","phone":"' + @Phone + '","company":"' + @Company + '","children":' + CAST(@Json AS VARCHAR(MAX)) + '}'
         SET @IsRoot = 1
      END
      IF(@IsRoot = @POSITIONID) 
      BEGIN
         SELECT @Name = P.Name FROM [dbo].[VW_WorkerHierarchyUpdated] AS P WHERE P.POSITIONID = @POSITIONID
         SELECT @Title = ISNULL(P.POSTIONTITTLE,'') FROM [dbo].[VW_WorkerHierarchyUpdated] AS P WHERE P.POSITIONID = @POSITIONID
         SELECT @Phone = ISNULL(P.PRIMARYCONTACTPHONE,'') FROM [dbo].[VW_WorkerHierarchyUpdated] AS P WHERE P.POSITIONID = @POSITIONID
         SELECT @Company = ISNULL(P.[COMPANY NAME],'') FROM [dbo].[VW_WorkerHierarchyUpdated] AS P WHERE P.POSITIONID = @POSITIONID
         SELECT @Email = ISNULL(P.primarycontactemail,'') FROM [dbo].[VW_WorkerHierarchyUpdated] AS P WHERE P.POSITIONID = @POSITIONID
         SET @Json =   '{"name":"' + @Name + '","title":"' + @Title + '","email":"' + @Email + '","phone":"' + @Phone + '","company":"' + @Company + '","children":' + CAST(@Json AS VARCHAR(MAX)) + '}'
         SET @IsRoot = 1
      END
  
  
      RETURN @Json 
  
  END
  </code></pre>