Recursive CTE instead of scalar function

Question

We have data warehouse and cube which is loaded from Microsoft Dynamics AX Issue is caused by scalar function:

CREATE FUNCTION [dbo].[fn_SplitActivityByLevel] 
(
    -- Add the parameters for the function here
     @Activity varchar(20),
     @Level tinyint,
     @SplitBy char(1)
)
RETURNS varchar(20)
AS
BEGIN

DECLARE @start int,
        @count int,
        @value varchar(20),
        @valueStart int,
        @returnValue varchar(20),
        @ActivityLength int;

--SET @Activity = '100.7.3.4';
--SET @Level = 1;
SET @start = 1;
SET @count = 0;
SET @valueStart = 1;
SET @ActivityLength = 1;

-- Make sure level is a positive number
    if @Level > 0
    BEGIN
        WHILE @start < LEN(@Activity) + 1 
        BEGIN 
            SET @value = SUBSTRING(@Activity, @start, 1)
            if @value = @SplitBy
            BEGIN
                SET @count = @count + 1;
                SET @returnValue = SUBSTRING(@Activity, @valueStart, @ActivityLength - 1)

                SET @valueStart = @start + 1
                SET @ActivityLength = 0;
            END

            if @count = @Level
                break;

            SET @start = @start + 1;
            SET @ActivityLength = @ActivityLength + 1;
        END 
    END

-- Check to see if the level requested is greater then the number of levels available.
if @count < @level
    SET @returnValue = null;

-- Check for the last level.
if @count + 1 = @level
    SET @returnValue = SUBSTRING(@Activity, @valueStart, @ActivityLength - 1)

--print '@returnValue = '  + CAST(@returnValue as varchar);

    return @returnValue
END

Function is called :

SELECT 
        SMMACTIVITIES.[PARTITION]                             AS [PARTITION]
       ,SMMACTIVITIES.DATAAREAID                              AS [DATAAREAID]
       ,SMMACTIVITIES.ACTIVITYNUMBER                          AS ActivityNumber
       ,SMMACTIVITIES.PMIPACTIVITYCODE                        AS PMIPActivityCode

       ,SMMACTIVITIES.PURPOSE                                        AS ActivityPurpose
       ,SMMACTIVITYCATEGORY_ENUM.ENUMITEMName          AS ActivityCategoryName

       ,ProjTable.ProjID                                             AS ProjectID               --Outrigger - dimension tables joined to other dimension tables                     

       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 1 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4)) ELSE CAST( CAST( Level1.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level1_WBS
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 1 THEN  HierarchyTreeTable.Name ELSE Level1.Name END, '') AS Level1_TaskName
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 2 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4)) ELSE CAST( CAST( Level2.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level2_WBS
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 2 THEN  HierarchyTreeTable.Name ELSE Level2.Name END, '') AS Level2_TaskName
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 3 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4)) ELSE CAST( CAST( Level3.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level3_WBS
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 3 THEN  HierarchyTreeTable.Name ELSE Level3.Name END, '') AS Level3_TaskName
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 4 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4)) ELSE CAST( CAST( Level4.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level4_WBS
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 4 THEN  HierarchyTreeTable.Name ELSE Level4.Name END, '') AS Level4_TaskName
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 5 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4))  ELSE CAST( CAST( Level5.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level5_WBS
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 5 THEN  HierarchyTreeTable.Name ELSE Level5.Name END, '') AS Level5_TaskName
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 6 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4))  ELSE CAST( CAST( Level6.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level6_WBS
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 6 THEN  HierarchyTreeTable.Name ELSE Level6.Name END, '') AS Level6_TaskName
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 7 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4)) ELSE CAST( CAST( Level7.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level7_WBS
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 7 THEN  HierarchyTreeTable.Name ELSE Level7.Name END, '') AS Level7_TaskName
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 8 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4))  ELSE CAST( CAST( Level8.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level8_WBS
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 8 THEN  HierarchyTreeTable.Name ELSE Level8.Name END, '') AS Level8_TaskName
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 9 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4)) ELSE  CAST( CAST( Level9.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level9_WBS
       ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 9 THEN  HierarchyTreeTable.Name ELSE Level9.Name END, '') AS Level9_TaskName


FROM --SELECT * FROM
       ProjTable
INNER JOIN
       HierarchyLinkTable
ON
              ProjTable.RECID                                 = HierarchyLinkTable.RefRecID
       AND HierarchyLinkTable.REFTABLEID = 624

              --Not needed unless a field from HIERARCHY table is needed
              --INNER JOIN --SELECT * FROM
              --     Hierarchy
              --ON
              --     Hierarchy.[HierarchyID] = HierarchyLinkTable.[HierarchyID]

INNER JOIN --SELECT * FROM
       HierarchyTreeTable
ON
       HierarchyTreeTable.[HierarchyID] = HierarchyLinkTable.[HierarchyID]

INNER JOIN --SELECT * FROM
       smmActivities
ON
       HierarchyTreeTable.RefRECID = smmActivities.RECID

       INNER JOIN --SELECT * FROM SRSANALYSISENUMS WHERE ENUMName  =  'smmActivityCategory'
              dbo.SRSANALYSISENUMS smmActivityCategory_ENUM
       ON
                     smmActivityCategory_ENUM.ENUMName        =  'SMMACTIVITYCATEGORY'
              AND smmActivityCategory_ENUM.LANGUAGEID         =  'en-us'
              AND smmActivityCategory_ENUM.ENUMITEMVALUE      =  smmActivities.CATEGORY

LEFT JOIN 
       HierarchyTreeTable Level1 
ON 
              Level1.DataAreaID          =  HierarchyTreeTable.DataAreaID  
       AND Level1.[PARTITION]            =  HierarchyTreeTable.[PARTITION]
       AND Level1.ELEMENTNUMBER   =  ProjAXBI_v1_4.fn_SplitActivityByLevel(HierarchyTreeTable.path, 2, '#') 

LEFT JOIN 
       HierarchyTreeTable Level2 
ON 
              Level2.DataAreaID          =  HierarchyTreeTable.DataAreaID  
       AND Level2.[PARTITION]            =  HierarchyTreeTable.[PARTITION]
       AND    Level2.ELEMENTNUMBER =  ProjAXBI_v1_4.fn_SplitActivityByLevel(HierarchyTreeTable.path, 3, '#') 

LEFT JOIN 
       HierarchyTreeTable Level3 
ON 
              Level3.DataAreaID          =  HierarchyTreeTable.DataAreaID  
       AND Level3.[PARTITION]            =  HierarchyTreeTable.[PARTITION]
       AND    Level3.ELEMENTNUMBER =  ProjAXBI_v1_4.fn_SplitActivityByLevel(HierarchyTreeTable.path, 4, '#') 

LEFT JOIN 
       HierarchyTreeTable Level4 
ON 
              Level4.DataAreaID          =  HierarchyTreeTable.DataAreaID  
       AND Level4.[PARTITION]            =  HierarchyTreeTable.[PARTITION]
       AND    Level4.ELEMENTNUMBER =  ProjAXBI_v1_4.fn_SplitActivityByLevel(HierarchyTreeTable.path, 5, '#') 

LEFT JOIN 
       HierarchyTreeTable Level5 
ON 
              Level5.DataAreaID          =  HierarchyTreeTable.DataAreaID  
       AND Level5.[PARTITION]            =  HierarchyTreeTable.[PARTITION]
       AND    Level5.ELEMENTNUMBER =  ProjAXBI_v1_4.fn_SplitActivityByLevel(HierarchyTreeTable.path, 6, '#') 

LEFT JOIN 
       HierarchyTreeTable Level6 
ON 
              Level6.DataAreaID          =  HierarchyTreeTable.DataAreaID  
       AND Level6.[PARTITION]            =  HierarchyTreeTable.[PARTITION]
       AND    Level6.ELEMENTNUMBER =  ProjAXBI_v1_4.fn_SplitActivityByLevel(HierarchyTreeTable.path, 7, '#') 

LEFT JOIN 
       HierarchyTreeTable Level7 
ON 
              Level7.DataAreaID          =  HierarchyTreeTable.DataAreaID  
       AND Level7.[PARTITION]            =  HierarchyTreeTable.[PARTITION]
       AND    Level7.ELEMENTNUMBER =  ProjAXBI_v1_4.fn_SplitActivityByLevel(HierarchyTreeTable.path, 8, '#') 

LEFT JOIN 
       HierarchyTreeTable Level8 
ON 
              Level8.DataAreaID          =  HierarchyTreeTable.DataAreaID  
       AND Level8.[PARTITION]            =  HierarchyTreeTable.[PARTITION]
       AND    Level8.ELEMENTNUMBER =  ProjAXBI_v1_4.fn_SplitActivityByLevel(HierarchyTreeTable.path, 9, '#') 

LEFT JOIN 
       HierarchyTreeTable Level9 
ON 
              Level9.DataAreaID          =  HierarchyTreeTable.DataAreaID  
       AND Level9.[PARTITION]            =  HierarchyTreeTable.[PARTITION]
       AND    Level9.ELEMENTNUMBER =  ProjAXBI_v1_4.fn_SplitActivityByLevel(HierarchyTreeTable.path, 10, '#')

Definition of table:

CREATE TABLE [dbo].[SMMACTIVITIES](
       [ACTUALWORK] [numeric](32, 16) NOT NULL DEFAULT ((0)),
       [BILLINGINFORMATION] [nvarchar](150) NOT NULL DEFAULT (''),
       [OUTLOOKCATEGORIES] [nvarchar](255) NOT NULL DEFAULT (''),
       [PERCENTAGECOMPLETED] [numeric](32, 16) NOT NULL DEFAULT ((0)),
       [SENSITIVITY] [int] NOT NULL DEFAULT ((0)),
       [TEAMTASK] [int] NOT NULL DEFAULT ((0)),
       [TOTALWORK] [numeric](32, 16) NOT NULL DEFAULT ((0)),
       [MILEAGE] [nvarchar](199) NOT NULL DEFAULT (''),
       [TASKROLE] [nvarchar](10) NOT NULL DEFAULT (''),
       [STATUS] [int] NOT NULL DEFAULT ((0)),
       [OUTLOOKGLOBALOBJECTID] [nvarchar](255) NOT NULL DEFAULT (''),
       [ACTUALENDDATETIME] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
       [ACTUALENDDATETIMETZID] [int] NOT NULL DEFAULT ((37001)),
       [DISPATCHED] [int] NOT NULL DEFAULT ((0)),
       [REMINDERDATETIME] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
       [REMINDERDATETIMETZID] [int] NOT NULL DEFAULT ((37001)),
       [LASTEDITAXDATETIME] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
       [LASTEDITAXDATETIMETZID] [int] NOT NULL DEFAULT ((37001)),
       [RESPONSEREQUESTED] [int] NOT NULL DEFAULT ((0)),
       [OUTLOOKRESOURCES] [nvarchar](10) NOT NULL DEFAULT (''),
       [ENDDATETIME] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
       [ENDDATETIMETZID] [int] NOT NULL DEFAULT ((37001)),
       [RESPONSIBILITYID] [nvarchar](20) NOT NULL DEFAULT (''),
       [STARTDATETIME] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
       [STARTDATETIMETZID] [int] NOT NULL DEFAULT ((37001)),
       [ORIGINALAPPOINTMENTSTARTDATETIME] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
       [ORIGINALAPPOINTMENTSTARTDATETIMETZID] [int] NOT NULL DEFAULT ((37001)),
       [RECURRENCESTATE] [int] NOT NULL DEFAULT ((0)),
       [MODIFIED] [int] NOT NULL DEFAULT ((0)),
       [ISMASTERAPPOINTMENT] [int] NOT NULL DEFAULT ((0)),
       [RESPONSIBLEWORKER] [bigint] NOT NULL DEFAULT ((0)),
       [DONEBYWORKER] [bigint] NOT NULL DEFAULT ((0)),
       [ISTEMPLATE] [int] NOT NULL DEFAULT ((0)),
       [CLOSED] [int] NOT NULL DEFAULT ((0)),
       [PURPOSE] [nvarchar](255) NOT NULL DEFAULT (''),
       [ACTIVITYNUMBER] [nvarchar](50) NOT NULL DEFAULT (''),
       [TYPEID] [nvarchar](20) NOT NULL DEFAULT (''),
       [PHASEID] [nvarchar](20) NOT NULL DEFAULT (''),
       [CATEGORY] [int] NOT NULL DEFAULT ((0)),
       [LOCATION] [nvarchar](255) NOT NULL DEFAULT (''),
       [PLANID] [nvarchar](20) NOT NULL DEFAULT (''),
       [KEEPSYNCHRONIZED] [int] NOT NULL DEFAULT ((0)),
       [REMINDERACTIVE] [int] NOT NULL DEFAULT ((0)),
       [REMINDERMINUTES] [int] NOT NULL DEFAULT ((0)),
       [OUTLOOKENTRYID] [nvarchar](255) NOT NULL DEFAULT (''),
       [SOURCE] [nvarchar](255) NOT NULL DEFAULT (''),
       [TASKPRIORITY] [int] NOT NULL DEFAULT ((0)),
       [USERMEMO] [nvarchar](max) NULL DEFAULT (NULL),
       [EXTERNALMEMO] [nvarchar](max) NULL DEFAULT (NULL),
       [ALLDAY] [int] NOT NULL DEFAULT ((0)),
       [ACTIVITYTIMETYPE] [int] NOT NULL DEFAULT ((0)),
       [ACTIVITYTASKTIMETYPE] [int] NOT NULL DEFAULT ((0)),
       [PSAMILESTONE] [int] NOT NULL DEFAULT ((0)),
       [PSAMANDATORY] [int] NOT NULL DEFAULT ((0)),
       [PSADESCRIPTION] [nvarchar](60) NOT NULL DEFAULT (''),
       [PSAACTIVITY] [nvarchar](20) NOT NULL DEFAULT (''),
       [PSACATEGORYDEFAULT] [nvarchar](30) NOT NULL DEFAULT (''),
       [PSAQUOTATIONID] [nvarchar](20) NOT NULL DEFAULT (''),
       [PURCHAGREEMENTHEADER] [bigint] NOT NULL DEFAULT ((0)),
       [MODIFIEDDATETIME] [datetime] NOT NULL DEFAULT (dateadd(millisecond, -datepart(millisecond,getutcdate()),getutcdate())),
       [MODIFIEDBY] [nvarchar](8) NOT NULL DEFAULT ('?'),
       [CREATEDDATETIME] [datetime] NOT NULL DEFAULT (dateadd(millisecond, -datepart(millisecond,getutcdate()),getutcdate())),
       [CREATEDBY] [nvarchar](8) NOT NULL DEFAULT ('?'),
       [DATAAREAID] [nvarchar](4) NOT NULL DEFAULT ('dat'),
       [RECVERSION] [int] NOT NULL DEFAULT ((1)),
       [PARTITION] [bigint] NOT NULL DEFAULT ((5637144576.)),
       [RECID] [bigint] NOT NULL,
       [PMIPPARENTACTIVITYCODE_DEL] [nvarchar](27) NOT NULL DEFAULT (''),
       [PMIPACTIVITYCODE] [nvarchar](27) NOT NULL DEFAULT (''),
       [PMIPCHILDWBS] [nvarchar](60) NOT NULL DEFAULT (''),
       [PMIPPARENTWBS] [nvarchar](60) NOT NULL DEFAULT (''),
CONSTRAINT [I_8142ACTIVITYNUMBERIDX] PRIMARY KEY CLUSTERED 
(
       [PARTITION] ASC,
       [DATAAREAID] ASC,
       [ACTIVITYNUMBER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[SMMACTIVITIES]  WITH CHECK ADD CHECK  (([RECID]<>(0)))
GO

Entire loading mechanism is done in 3 minutes except, when it comes till this part. From this part it takes more than 30 minutes. I need to change this function with some recursive CTE . Any suggestion?

Answer 1

Scalar functions are performance killers in MSSQL, especially if you are going to use them plentiful in JOIN operations =(

I'd try to make a lookup table that already has the conversion from Path+level to Activity prepared and do lookups on that.

Something along the lines of:

;WITH Numbers -- we support up to 9 levels
  AS (SELECT Nbr = 1 UNION ALL 
      SELECT Nbr = 2 UNION ALL 
      SELECT Nbr = 3 UNION ALL 
      SELECT Nbr = 4 UNION ALL 
      SELECT Nbr = 5 UNION ALL 
      SELECT Nbr = 6 UNION ALL 
      SELECT Nbr = 7 UNION ALL 
      SELECT Nbr = 8 UNION ALL 
      SELECT Nbr = 9 ),
     Paths
  AS (SELECT DISTINCT Path
       FROM HierarchyTreeTable -- I'm guessing this may be a lot of data, trying to limit it by below, adapt as needed
      WHERE EXISTS ( SELECT * 
                       FROM HierarchyLinkTable
                      WHERE REFTABLEID = 624
                        AND HierarchyLinkTable.[HierarchyID] = HierarchyTreeTable.[HierarchyID] )

SELECT Level = Nbr,
       Path,
       Activity = Path + '#' -- Important! Add trailing splitter 
  INTO #HierarchyTree
  FROM Numbers,
       Paths

-- add index for quick processing 
CREATE CLUSTERED INDEX idxHierarchyTree ON #HierarchyTree (Level) WITH (FILLFACTOR = 100)

DECLARE @level int = 0,
        @x_pos int

WHILE @level < 10
    BEGIN
        SELECT @level = @level + 1

        UPDATE #HierarchyTree
           SET @x_pos = CharIndex('#', Activity),
               Activity = (CASE WHEN Level = @level AND @x_pos > 0 THEN Left(Activity, @x_pos - 1)
                                WHEN Level = @level                THEN Activity
                                                                   ELSE SubString(Activity, @x_pos + 1, Len(Activity)) END)
         WHERE Level >= @level

        -- select _@level = @level , * FROM #HierarchyTree WHERE [HierarchyID] = 1

    END

-- rebuild index for quick joining
CREATE UNIQUE CLUSTERED INDEX idxHierarchyTree ON #HierarchyTree (Path, Level) WITH (DROP_EXISTING = ON, FILLFACTOR = 100)


-- SELECT * FROM #HierarchyTree

-- actual fetch, now with lookups instead of using scalar function
SELECT 
    SMMACTIVITIES.[PARTITION]                             AS [PARTITION]
   ,SMMACTIVITIES.DATAAREAID                              AS [DATAAREAID]
   ,SMMACTIVITIES.ACTIVITYNUMBER                          AS ActivityNumber
   ,SMMACTIVITIES.PMIPACTIVITYCODE                        AS PMIPActivityCode

   ,SMMACTIVITIES.PURPOSE                                        AS ActivityPurpose
   ,SMMACTIVITYCATEGORY_ENUM.ENUMITEMName          AS ActivityCategoryName

   ,ProjTable.ProjID                                             AS ProjectID               --Outrigger - dimension tables joined to other dimension tables                     

   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 1 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4)) ELSE CAST( CAST( Level1.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level1_WBS
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 1 THEN  HierarchyTreeTable.Name ELSE Level1.Name END, '') AS Level1_TaskName
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 2 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4)) ELSE CAST( CAST( Level2.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level2_WBS
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 2 THEN  HierarchyTreeTable.Name ELSE Level2.Name END, '') AS Level2_TaskName
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 3 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4)) ELSE CAST( CAST( Level3.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level3_WBS
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 3 THEN  HierarchyTreeTable.Name ELSE Level3.Name END, '') AS Level3_TaskName
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 4 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4)) ELSE CAST( CAST( Level4.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level4_WBS
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 4 THEN  HierarchyTreeTable.Name ELSE Level4.Name END, '') AS Level4_TaskName
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 5 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4))  ELSE CAST( CAST( Level5.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level5_WBS
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 5 THEN  HierarchyTreeTable.Name ELSE Level5.Name END, '') AS Level5_TaskName
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 6 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4))  ELSE CAST( CAST( Level6.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level6_WBS
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 6 THEN  HierarchyTreeTable.Name ELSE Level6.Name END, '') AS Level6_TaskName
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 7 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4)) ELSE CAST( CAST( Level7.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level7_WBS
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 7 THEN  HierarchyTreeTable.Name ELSE Level7.Name END, '') AS Level7_TaskName
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 8 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4))  ELSE CAST( CAST( Level8.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level8_WBS
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 8 THEN  HierarchyTreeTable.Name ELSE Level8.Name END, '') AS Level8_TaskName
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 9 THEN  CAST( CAST(HierarchyTreeTable.SiblingNumber AS INT) AS NVARCHAR(4)) ELSE  CAST( CAST( Level9.SiblingNumber AS INT) AS NVARCHAR(4)) END, '') AS Level9_WBS
   ,ISNULL(case when HierarchyTreeTable.LEVEL_ = 9 THEN  HierarchyTreeTable.Name ELSE Level9.Name END, '') AS Level9_TaskName


FROM --SELECT * FROM
       ProjTable
INNER JOIN
       HierarchyLinkTable
ON
              ProjTable.RECID                                 = HierarchyLinkTable.RefRecID
       AND HierarchyLinkTable.REFTABLEID = 624

              --Not needed unless a field from HIERARCHY table is needed
              --INNER JOIN --SELECT * FROM
              --     Hierarchy
              --ON
              --     Hierarchy.[HierarchyID] = HierarchyLinkTable.[HierarchyID]

INNER JOIN --SELECT * FROM
       HierarchyTreeTable
ON
       HierarchyTreeTable.[HierarchyID] = HierarchyLinkTable.[HierarchyID]

INNER JOIN --SELECT * FROM
       smmActivities
ON
       HierarchyTreeTable.RefRECID = smmActivities.RECID

       INNER JOIN --SELECT * FROM SRSANALYSISENUMS WHERE ENUMName  =  'smmActivityCategory'
              dbo.SRSANALYSISENUMS smmActivityCategory_ENUM
       ON
                     smmActivityCategory_ENUM.ENUMName        =  'SMMACTIVITYCATEGORY'
              AND smmActivityCategory_ENUM.LANGUAGEID         =  'en-us'
              AND smmActivityCategory_ENUM.ENUMITEMVALUE      =  smmActivities.CATEGORY

LEFT JOIN 
       HierarchyTreeTable Level1 
ON 
           Level1.DataAreaID          =  HierarchyTreeTable.DataAreaID  
       AND Level1.[PARTITION]         =  HierarchyTreeTable.[PARTITION]
       AND Level1.ELEMENTNUMBER       =  (SELECT Activity FROM #HierarchyTree LookUp1 WHERE LookUp1.Path = HierarchyTreeTable.path AND LookUp1.Level = 2)

LEFT JOIN 
       HierarchyTreeTable Level2 
ON 
           Level2.DataAreaID          =  HierarchyTreeTable.DataAreaID  
       AND Level2.[PARTITION]         =  HierarchyTreeTable.[PARTITION]
       AND Level2.ELEMENTNUMBER       =  (SELECT Activity FROM #HierarchyTree LookUp2 WHERE LookUp2.Path = HierarchyTreeTable.path AND LookUp2.Level = 2)

etc...

Hope this gets you going...

Answer 2

1. Create another function [dbo].[fn_SplitActivityByLevel_new] which return table with two column Activity and level and accept only @Activity varchar(20),@SplitBy char(1)

;

WITH CTE
AS (
  SELECT ht.*
      ,fn.Activity
      ,fn.[Level]
  FROM dbo.HierarchyTreeTable ht
  CROSS APPLY (
      SELECT *
      FROM [dbo].[fn_SplitActivityByLevel_new](ht.path, '#')
      ) fn
  )
SELECT *
FROM CTE

see this what it return and analyse it and fix bug if any.

Now join CTE in you main query instead of HierarchyTreeTable and do the necessary change.

and kindly let me know your progress.

If till here everything is ok including performance then it still has potential for further improvement.

you don't need recursive.you need inline table value function.