Hierarchical Sum of columns in SQL Server

Hi

I have created a parent and child relationship table;

CREATE TABLE [dbo].[CostBoq](
	[CostBoqID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectID] [int] NOT NULL,
	[CostCodeSN] [int] NOT NULL,
	[CostCode] [nvarchar](20) NOT NULL,
	[ParentCostBoqID] [int] NULL,
	[SN] [int] NOT NULL,
	[CostItemDescription] [nvarchar](4000) NOT NULL,
	[Qty] [decimal](18, 3) NOT NULL,
	[BudgetUnitRate] [decimal](18, 4) NULL,
	[BudgetAmount] [decimal](18, 4) NULL,
 CONSTRAINT [PK_CostBoq] PRIMARY KEY CLUSTERED 
(
	[CostBoqID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

And I have created SQL statements for Parent, Child, and subtotal in hierarchal view.

nearly 2000 records.

---temp table start--
 
CREATE TABLE #TempTable (CostBoqID int, SN int, ParentCostBoqID int, CostCode nvarchar(20), BudgetAmount decimal(18,4), ActualCost decimal(18,4), RemainBalance decimal(18,4))
 
INSERT INTO #TempTable (CostBoqID, SN, ParentCostBoqID, CostCode, BudgetAmount, ActualCost, RemainBalance)
 
SELECT  Cb.CostBoqID            as 'CostBoqID',
        Cb.SN                   as 'SN',
        Cb.ParentCostBoqID      as 'ParentCostBoqID',
        Cb.CostCode             as 'CostCode',
        Cb.BudgetAmount         as 'BudgetAmount',
        (isnull(Sri.TotalAmount,0) + isnull(Scbi.TotalAmount,0))            as 'ActualCost',
        (Cb.BudgetAmount - isnull(Sri.TotalAmount,0) - isnull(Scbi.TotalAmount,0)) as 'RemainBalance'
FROM    CostBoq Cb
LEFT JOIN ( SELECT  CostBoqID   as 'CostBoqID',
                    SUM(Amount) as 'TotalAmount'
            FROM    [SATHUTA-INVDB].[dbo].[tbl_SR_Items]
            WHERE CostBoqID is not null
            GROUP BY CostBoqID) Sri ON Cb.CostBoqID = Sri.CostBoqID
LEFT JOIN ( SELECT  CostBoqID   as 'CostBoqID',
                    SUM(Amount) as 'TotalAmount'
            FROM    Subcontract_Bill_Items
            WHERE CostBoqID is not null
            GROUP BY CostBoqID) Scbi ON Cb.CostBoqID = Scbi.CostBoqID
WHERE   Cb.ProjectID = @ProjectID;
 
---temp table end--
 
--action--
 
WITH CTE AS
    (
        --- Root nodes:
        SELECT  CAST(0 AS tinyint)              as 'Level0',
                CAST((right('0000' + cast(A1.SN as varchar(4)), 4)) as varbinary(MAX))  as 'Level1',
                CAST((right('0000' + cast(A1.SN as varchar(4)), 4)) as VARCHAR(MAX)) + '\'  as 'Level2',
                A1.CostBoqID                    as 'CostBoqID',
                A1.ParentCostBoqID              as 'ParentCostBoqID',
                A1.CostCode                     as 'CostCode',
                A1.BudgetAmount                 as 'BudgetAmount',
                A1.ActualCost                   as 'ActualCost',
                A1.RemainBalance                as 'RemainBalance',
                CAST(STR(ROW_NUMBER() OVER (
                    PARTITION BY A1.ParentCostBoqID
                    ORDER BY A1.CostBoqID), 4, 0) AS varchar(1024)) as 'sortColumn'
        FROM    #TempTable A1
        WHERE   A1.ParentCostBoqID IS NULL
     
        UNION ALL
     
        --- Recursion:
        SELECT  CAST(C.Level0+1 AS tinyint)         as 'Level0',
                C.Level1+CAST((right('0000' + cast(A2.SN as varchar(4)), 4)) as varbinary(MAX)) as 'Level1',
                CAST(C.Level2 AS VARCHAR(MAX))  + CAST((right('0000' + cast(A2.SN as varchar(4)), 4)) AS VARCHAR(MAX)) + '\' as 'Level2',
                A2.CostBoqID                            as 'CostBoqID',
                A2.ParentCostBoqID                      as 'ParentCostBoqID',
                A2.CostCode                             as 'CostCode',
                A2.BudgetAmount                         as 'BudgetAmount',
                A2.ActualCost                           as 'ActualCost',
                A2.RemainBalance                        as 'RemainBalance',
                CAST(C.sortColumn+STR(ROW_NUMBER() OVER (
                    PARTITION BY A2.ParentCostBoqID
                    ORDER BY A2.CostBoqID), 4, 0) AS varchar(1024)) as 'sortColumn'
        FROM #TempTable A2
        INNER JOIN cte C ON A2.ParentCostBoqID = C.CostBoqID
    )
     
    --------------------
     
    SELECT  C1.sortColumn           as 'sortColumn',
            C1.Level0               as 'Level0',
            C1.Level1               as 'Level1',
            C1.Level2               as 'Level2',
            C1.CostBoqID            as 'CostBoqID',
            Cb.ProjectID            as 'ProjectID',                
            Cb.CostCode             as 'CostCode',
            Cb.ParentCostBoqID      as 'ParentCostBoqID',
            Cb.CostBoqItemNum       as 'CostBoqItemNum',
            Cb.CostItemDescription  as 'CostItemDescription',
            CONVERT(DOUBLE PRECISION,Cb.Qty)    as 'Qty',
            convert(nvarchar, cast(Cb.BudgetUnitRate as money),1)       as 'BudgetUnitRate',
            convert(nvarchar, cast(Cb.BudgetAmount as money),1)         as 'BudgetAmount',
 
            (SELECT
                convert(nvarchar, cast(SUM(BudgetAmount) as money),1)
                    FROM cte WHERE Level2 LIKE C1.Level2 + '%')         as 'BudgetTotal',
            (SELECT
                convert(nvarchar, cast(SUM(ActualCost) as money),1)
                    FROM cte WHERE Level2 LIKE C1.Level2 + '%')         as 'ActualCostTotal',
            (SELECT
                convert(nvarchar, cast(SUM(RemainBalance) as money),1)
                    FROM cte WHERE Level2 LIKE C1.Level2 + '%')         as 'RemainBalanceTotal'
    FROM cte C1
 
    JOIN CostBoq Cb ON C1.CostBoqID = Cb.CostBoqID         
 
    WHERE   Cb.ProjectID = @ProjectID AND Cb.Active = 1 AND Cb.ProjectClassID = @ProjectClassID                
 
    ORDER BY C1.Level2 ASC;

So My problem was;

Its take 5-10 minute for view whole records. Please advise me for reduced duration.   

Thanking you

SAMSMUTHU