Что является более эффективным способом 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, Он содержит список Budgetlines. Разные проекты (гранты) могут иметь одинаковые бюджетные линии.

Ниже приведена полная таблично-значная функция для получения дополнительных бюджетных строк (потомков) бюджетной линии (родительской).

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

Оно работает.

• Таблица Budgetline имеет только 252 записи
• Таблица TransactionDetails содержит только 172 записи

Мой вызов:

Возврат суббюджетных линий занимает около 10 секунд, если этот конкретный бюджет имеет 3 или более подуровней (потомков).

Мой вопрос:

Есть ли лучший способ оптимизировать (переписать) эту функцию, чтобы она могла работать быстрее.

Ниже показано, как выглядит дизайн. Пользователь может просмотреть строки суббюджета, дважды щелкнув строку или нажав кнопку [Переместить вниз].

Как выглядит интерфейс

Само собой разумеется: это мой первый пост о всемогущем стеке потока. Извините, если я нарушил какое-либо из правил сообщества. Я все еще учу их.

Answer 1

Вероятно, стоит указать, что вы просите / возможно, если предположить, что "получить строки суббюджета" лучше всего сделать в базе данных, как только будет запрошена основная строка бюджета.

Учтите, что вы создаете пользовательский интерфейс, который понимает / отображает отношение "мастер-детализация", и многое из этого можно сделать в пользовательском интерфейсе с большей эффективностью *, если подуровни не будут желательны все время. и возвращение 5 уровней иерархических данных, если пользователь действительно хочет только просматривать корневой уровень в пользовательском интерфейсе. Поэтому может быть лучше позволить пользовательскому интерфейсу управлять спросом на данные - просто возвращать соответствующие уровни, когда они запрашиваются пользователем

* эффективен с точки зрения: не тратить время БД на сбор ненужных данных / не передавать данные по сети, если они не будут использоваться