在同一连接中跨函数调用访问数据

Question

我需要帮助解决与SQL Server中递归函数相关的性能问题。我有一个项目任务表，每个项目都有交付周期。我的函数递归地调用自己，根据前面任务的总和来计算每个任务的截止日期(简单地说…(。我认为，该函数在大范围内执行缓慢，主要是因为必须为每个祖先和每个后续任务重新计算截止日期。

所以我想知道，有没有一种方法可以存储一个计算值，该值可以在函数调用到函数调用之间持续存在，并且只持续连接的生命周期？然后，如果我的函数找到了一个预先计算的值，它可能会"短路"，并避免对每个到期日请求进行重新评估。基本模式如下，对所讨论的函数进行了粗略表示(该函数也可以用cte完成，但计算仍在重复相同的计算(：

Create Table Projects(id int, DueDate DateTime)
Create Table Items(id int, Parent int, Project int, Offset int)
Create Table Tasks (id int, Parent int, Leadtime Int, Sequence int)
insert into Projects Values
(100,'1/1/2021')
Insert into Items Values
(0,null, 100, 0)
,(1,12, null, 0)
,(2,15, null, 1)
Insert into Tasks Values
(10,0,1,1)
,(11,0,1,2)
,(12,0,2,3)
,(13,0,1,4)
,(14,1,1,1)
,(15,1,1,2)
,(16,2,2,1)
,(17,2,1,2);
CREATE FUNCTION GetDueDate(@TaskID int)
Returns DATETIME
AS BEGIN
Declare @retval DateTime = null
Declare @parent int = (Select Parent from Tasks where ID = @TaskID)
Declare @parentConsumingOp int = (select Parent from Items where ID = @parent)
Declare @parentOffset int = (select Offset from Items where ID = @parent)
Declare @seq int = (Select Sequence from Tasks where ID = @TaskID)
Declare @NextTaskID int = (select ID from Tasks where Parent = @parent and Sequence = @seq-1)
Declare @Due DateTime = (select DueDate from Projects where ID = (Select Project from Items where ID = (Select Parent from Tasks where ID = @TaskID)))
Declare @leadTime int = (Select LeadTime from Tasks where ID = @TaskID)
if @NextTaskID is not null
BEGIN
SET @retval = DateAdd(Day,@leadTime * -1,dbo.GetDueDate(@NextTaskID))
END ELSE IF @parentConsumingOp Is Not Null
BEGIN
SET @retval = DateAdd(Day,(@leadTime + @parentOffset)*-1,dbo.GetDueDate(@parentConsumingOp))
END ELSE SET @retval = DateAdd(Day,@parentOffset*-1,@Due)
Return @retval
END

编辑：Sql Fiddle Here

Answer 1

注意事项：以下内容基于您提供的示例数据，而不是试图通过函数中的逻辑(即您试图实现什么，而不是如何实现它(。。。

该功能的结果似乎是：

对于"；这个任务"；

project.due_date-(sum(tasks.leadtime(+1(其中tasks.sequence<=此任务和任务的顺序。parent=此任务的父任务

如果是这种情况，那么这个函数会给出与您相同的结果，但要简单得多：

CREATE FUNCTION GetDueDate1(@TaskID int)
Returns DATETIME
AS BEGIN
Declare @retval DateTime = null
Declare @parent int = (Select Parent from Tasks where ID = @TaskID)
Declare @seq int = (Select sequence from Tasks where ID = @TaskID)
Declare @totlead int = (select Sum(Leadtime) - 1 from Tasks where parent = @parent and sequence <= @Seq)
Declare @duedate DateTime = (select p.DueDate from tasks t inner join items i on t.parent = i.id inner join projects p on i.Project = p.id where t.id = 13)
SET @retval = DateAdd(Day,@totlead * -1,@duedate)
Return @retval
END;

如果我对你的数据运行两个函数：

select id
,leadtime
, sequence
, [dbo].[GetDueDate](id) "YourFunction"
, [dbo].[GetDueDate1](id) "MyFunction"
from tasks
where parent = 0;

我得到了相同的结果：

id  leadtime    sequence    YourFunction            MyFunction
10  1           1           2021-01-01 00:00:00.000 2021-01-01 00:00:00.000
11  1           2           2020-12-31 00:00:00.000 2020-12-31 00:00:00.000
12  2           3           2020-12-29 00:00:00.000 2020-12-29 00:00:00.000
13  1           4           2020-12-28 00:00:00.000 2020-12-28 00:00:00.000

希望这有帮助？如果没有，请提供一些样本数据，其中我的函数不能产生与你的相同的结果

更新以下注释

好的一点是，上面的代码并不适用于您的所有数据。我一直在思考这个问题，并提出了以下建议-如果我误解了什么，请随时指出：

1. 很明显，您的函数只会返回作为参数传入的任务的截止日期。在此过程中，它还将只计算前面每个任务的截止日期一次
2. 因此，"；节省"；为其他任务计算的截止日期，因为它们只会在计算初始任务id时使用一次(因此，保持这些值不会带来性能增益，因为它们不会被重复使用(，如果您再次调用函数，它们也不会被使用-因为函数不是这样工作的：它不能"；知道"；您以前可能已经调用过该函数，并且作为中间步骤的一部分，已经计算出该任务id的截止日期

重新阅读最初的解释，您似乎真的想计算多个任务(或全部任务？(的截止日期，而不仅仅是一个任务。如果是这种情况，那么我不会(只是(使用一个函数(它本质上只限于一个任务(，相反，我会编写一个存储过程，它将遍历您的所有任务，计算它们的截止日期，并将其保存到一个表中(新表或更新您的任务表(。

1. 您需要确保以适当的顺序处理任务，以便首先计算后续任务的计算中使用的任务
2. 您可以重复使用函数中的逻辑(甚至可以从SP中调用函数(，但添加一些步骤来检查是否已经计算了截止日期(即，尝试从表中选择它(，如果有则使用它，如果没有则计算它(并将其保存到表中(
3. 每当计算中使用的表中的相关数据被修改时，您都需要运行此SP