Vfrdtyky

I have a table called Budgetline. It keeps track of budget lines for projects (Grants).

Schema:

BudgetId       int

Amount         decimal

LoginUser      varchar

InsertDate     datetime

GrantPhaseID   int

BudgetChartID  int

Rootdir        hierarchyid

OverHead       decimal

The Amount column is the budgeted amount for a budget line. A budget line can have a sub budgetline. A sub budgetline can have another sub budgetline. Sometimes there can be up to 5 levels.

There is another table TransactionsDetail; it keeps track of budgetline spending

Schema:

TransactionDetailID  int

TransactionID        int

Amount               numeric

ExRateAmount         numeric

TransactionDate      date

BudgetId             int

InsertDate           datetime

OverHead             decimal

Paid                 bit

PaidDate             datetime

LoginUser            varchar

Projects (Grants) have phases. There is another table GrantPhase to keep track of that.

There is another table called BudgetChart. It holds list of Budgetlines. Different projects (Grants) can have same budgetlines.

Below is the complete table-valued function to get the Sub budgetlines (descendants) of a budgetline (Parent).

ALTER FUNCTION [dbo].[getSUBS]

    (@BudgetID INT

     --,@GrantPhaseID INT

    )

RETURNS @Mytable TABLE (CID INT,

                        [COUNT] INT,

                        DESCRIPTION VARCHAR(256),

                        AMOUNT NUMERIC(18,2),

                        SPENT NUMERIC(18,2),

                        BALANCE NUMERIC(18,2),

                        OVERHEAD NUMERIC(18,2)

                        --  BUDGETLIMIT numeric(18,2)

                       )

AS

BEGIN



-- get budgetline root level

declare @BudgetIDrootRevel int

SELECT @BudgetIDrootRevel = RootDir.GetLevel() FROM budgetlines WHERE budgetid = @BudgetID



-- GET GRANTPHASEID

DECLARE @GrantPhaseID int=(

select GrantPhaseID from BudgetLines where BudgetId=@BudgetID

)



DECLARE @RootDir HIERARCHYID

SELECT @RootDir = RootDir FROM budgetlines WHERE budgetid = @BudgetID

insert into @Mytable(

    CID 

,   [COUNT]

,   DESCRIPTION 

,   AMOUNT 

,   SPENT

,   BALANCE

,   OVERHEAD 

--, BUDGETLIMIT

)



SELECT

    BudgetId 

,   ROW_NUMBER() OVER (ORDER BY BudgetID DESC)

,   [Description]

,   dbo.[getBudgetAmount](BudgetLines.BudgetId)  AMOUNT --Sums all transactions made in the TransactionDetails table

,   [dbo].[getBudgetSpent](BudgetId) as SPENT

,   ISNULL((dbo.[getBudgetAmount](BudgetLines.BudgetId)-[dbo].[getBudgetSpent](BudgetId)),0) as BALANCE

,   BudgetLines.OVERHEAD

--, BUDGETLIMIT



FROM BudgetLines INNER JOIN BudgetChart 

ON BudgetChart.BudgetChartID = BudgetLines.BudgetChartID



WHERE RootDir.IsDescendantOf(@RootDir)=1

and GrantPhaseID = @GrantPhaseID

and Rootdir.GetLevel()=(@BudgetIDrootRevel+1)

--AND isBudgetline=1



return ;



end

It works.

• Budgetline table has only 252 records


• TransactionDetails table has only 172 records

My Challenge:

It takes like 10 seconds to return sub budgetlines if that particular budgetline has 3 or more sub levels (descendants).

My Question:

Is there a better way to optimize(rewrite) this function so it can run faster.

Below is how the design looks like. User can see the sub budget lines either by double-clikcing a row or clicking the [Move Down] Button.

How the UI looks

Needless to say: This is my first post on almighty stackoverflow.
Sorry If I'v violate any of the community rules. I'm still learning
them.

It's probably worth pointing out that you're asking for/possibly assuming this "get the sub budget lines" is best done in the database as soon as the main budget line is asked for

Consider that you're building a UI that understands/displays a master-detail relationship and a lot of this can be done in the UI with greater efficiency* if the sub levels won't be desired all the time.. There's no point chasing and returning 5 levels of hierarchical data if the user really only wants to view the root level in the UI. It may thus be better to let the UI drive the demand for data - just return relevant levels as they're requested by the user

*efficient in terms of: not wasting DB's time collecting data that is unwanted/not transferring data over a network if it won't be used