У меня есть таблица под названием 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 или более подуровней (потомков).
Мой вопрос:
Есть ли лучший способ оптимизировать (переписать) эту функцию, чтобы она могла работать быстрее.
Ниже показано, как выглядит конструкция. Пользователь может увидеть строки суббюджета, дважды щелкнув строку или нажав кнопку [Вниз].
Как выглядит пользовательский интерфейс
Излишне говорить: это мой первый пост о всемогущем stackoverflow. Извините, если я нарушил какие-либо правила сообщества. Я все еще учу их.
Rootdir.GetLevel()=(@BudgetIDrootRevel+1)
? а затем несколько раз вызывать эту функцию, чтобы получить каждый следующий уровень? Если вам нужны все уровни, почему бы просто не позволить SQLS получить их все одним нажатием? - person Caius Jard   schedule 12.11.2018SELECT * FROM BudgetLines INNER JOIN BudgetChart ON BudgetChart.BudgetChartID = BudgetLines.BudgetChartID WHERE RootDir.IsDescendantOf(@RootDir)=1 and GrantPhaseID = @GrantPhaseID
? Я думаю, вам нужно настроить это в первую очередь - person Caius Jard   schedule 12.11.2018