什么是更有效的T-SQL方法来查询具有层次数据类型的表

Question

我有一个名为Budgetline的表。它跟踪项目(补助金(的预算线。

架构：

BudgetId       int
Amount         decimal
LoginUser      varchar
InsertDate     datetime
GrantPhaseID   int
BudgetChartID  int
Rootdir        hierarchyid
OverHead       decimal

Amount列是预算行的预算金额。预算行可以有一个子预算行。子预算线可以有另一个子预算线。有时可以有多达5个级别。

还有另一个表TransactionsDetail；它跟踪预算线支出

架构：

TransactionDetailID  int
TransactionID        int
Amount               numeric
ExRateAmount         numeric
TransactionDate      date
BudgetId             int
InsertDate           datetime
OverHead             decimal
Paid                 bit
PaidDate             datetime
LoginUser            varchar

项目(补助金(分阶段进行。还有另一个表GrantPhase来跟踪这一点。

还有一个表叫做BudgetChart。它包含预算线列表。不同的项目(拨款(可以有相同的预算线。

下面是完整的表值函数，用于获取预算线(父级(的子预算线(子级(。

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

它有效。

• 预算行表只有252条记录
• TransactionDetails表只有172条记录

我的挑战：

如果特定预算线具有3个或更多子级别(子级别(，则返回子预算线大约需要10秒。

我的问题：

有没有更好的方法来优化(重写(这个函数，这样它可以更快地运行。

下面是设计的样子。用户可以通过双击一行或单击[下移]按钮来查看子预算行。

UI的外观

不用说：这是我在全能的stackoverflow上的第一篇帖子。对不起，如果我违反了任何社区规则。我还在学习他们

Answer 1

可能值得指出的是，您要求/可能假设一旦主预算行被要求，就最好在数据库中完成"获取子预算行">

考虑一下，您正在构建一个理解/显示主-细节关系的UI，如果不总是需要子级别，那么可以在UI中以更高的效率完成很多工作。。如果用户真的只想在UI中查看根级别，那么追逐并返回5个级别的分层数据是没有意义的。因此，让UI驱动数据需求可能会更好——只需根据用户的请求返回相关级别

*效率方面：不浪费DB的时间收集不需要的数据/如果不使用，则不通过网络传输数据