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.
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.
