SQL 递归公用表表达式在项目调度实践中的应用
4.44/5 (4投票s)
具有递归 SQL 公用表表达式的项目调度引擎。
引言
以下文章描述了如何在 Microsoft SQL Server 中使用专为高性能设计的递归公用表表达式来实现项目调度引擎。
该解决方案根据前置和后置依赖关系提供项目预测功能。
背景
有时，项目调度的复杂性超出了 Primavera P6 等商业软件系统的能力范围。由于这些解决方案旨在满足大多数公司的需求，因此它们存在局限性。绕过这些局限性的成本超出了构建新软件的成本，而新软件可以以相当高的性能满足所有功能需求。
在我的案例中，项目的规模和复杂性导致不同计划、不同项目和超过 7000 个不同站点中超过 300 万个里程碑。这些里程碑在计划、项目、站点之间存在交叉依赖关系，并使用了不同的工作分解结构 (WBS)。
以下解决方案展示了如何构建单个存储过程，以在几秒钟内有效预测数百万个里程碑。
使用代码
我的示例项目是建造一所房子。为了简化流程，我将有一个项目开始日期，然后是 5 个里程碑，以及一个乔迁派对作为最后一个里程碑。
本次练习的目标是，仅通过了解项目开始日期和每项活动的持续时间，来预测乔迁派对的日期。
我们设计的引擎将在任何前置里程碑的预测日期或完成日期发生变化时，更新后续里程碑的预测日期。
为了存储所有与我的项目相关的里程碑，我正在创建一个名为 tblMilestone 的表。
该表存储里程碑的名称、预测日期、完成日期以及该活动的持续时间。
CREATE TABLE tblMilestone ( MilestoneId INT IDENTITY(1, 1) , Milestone NVARCHAR(128) NOT NULL , ForecastDate DATETIME NULL , CompletedDate DATETIME NULL , DurationInDays INT NOT NULL DEFAULT ( 0 ) , PRIMARY KEY ( MilestoneId ) ) GO
在此练习中，我不会详细介绍如何使用索引和唯一键进行微调，我将尽可能简单地描述解决方案。
一开始，我唯一知道的是我将在某个日期开始工作，以及我为每项活动分配的完成时间。
我们的计划是建立一个通过计算更新预测日期的过程。
INSERT INTO dbo.tblMilestone ( Milestone, ForecastDate, DurationInDays )< VALUES ( N'Project Start', '2016 Feb 1', 0 ), ( N'Buy Land', NULL, 30 ), ( N'Build Walls', NULL, 20 ), ( N'Build Roof', NULL, 15 ), ( N'Build Garden', NULL, 30 ), ( N'Key Handover', NULL, 2 ) , ( N'Housewarming party', NULL, 5 )
如果项目里程碑是顺序的，我们会将持续天数之和加到开始日期以获得乔迁派对的日期。但有些活动可以与其他活动同时开始。
为了开始建造花园，我只需要购买土地，理论上，我可以同时建造花园和建造墙壁。尽管这不是最实际的方法，但假设只是为了举例说明。
在正常的父子关系中，我们可以将依赖项存储在 tblMilestone 中。但是，它将父里程碑的数量限制为一个。
假设，当我们买了地后，我们将同时开始建造墙壁和花园。完成墙壁后，我们就可以开始建造屋顶了。
因此，建造墙壁和屋顶所需的时间是 35 天，而花园将需要 30 天。因此，乔迁派对的最早日期是两项活动都完成后，即 35 天加上钥匙交接的 2 天。
因此，为了举办乔迁派对，我们需要完成所有活动。
在项目管理术语中，钥匙移交里程碑有两个前置任务
1. 完成屋顶建造（请注意：屋顶建造里程碑的前置任务是墙壁建造）
2. 完成花园建造
为了映射这种前置-后置关系，我们需要另一个表。
CREATE TABLE tblMilestoneDependency (
MilestoneDependencyId INT IDENTITY(1, 1) ,
PredecessorMilestoneId INT NULL --reference of the milestone to be finished before our activity can be started
,
MilestoneId INT NOT NULL --current milestone in question
,
PRIMARY KEY ( MilestoneDependencyId ) )
GO
ALTER TABLE dbo.tblMilestoneDependency
ADD CONSTRAINT FK_tblMilestoneDependency_PredecessorMilestoneId FOREIGN KEY (PredecessorMilestoneId) REFERENCES tblMilestone (MilestoneId)
GO
ALTER TABLE dbo.tblMilestoneDependency
ADD CONSTRAINT FK_tblMilestoneDependency_MilestoneId FOREIGN KEY (MilestoneId) REFERENCES tblMilestone (MilestoneId)
起始里程碑没有前置任务，插入 NULL 将被过程解释为起始活动。
INSERT INTO dbo.tblMilestoneDependency ( PredecessorMilestoneId, MilestoneId ) VALUES ( NULL, 1 ), ( 1, 2 ), -- buy the land when the project started ( 2, 3 ), -- start building the wall when the land has bought ( 2, 5 ), -- start building the garden when the land has bought ( 3, 4 ), -- start building the roof when the walls are standing ( 4, 6 ), -- start organising the key handover when the garden and the roof finished ( 5, 6 ), ( 6, 7 ) -- start organising the housewawrming party
总而言之，我们的项目具有“分支”依赖关系。它包含一个示例，即一个里程碑有多个后置任务，换句话说，当一个活动完成后，可以开始另外两个活动。
并且还包括在开始另一项活动之前需要完成多项活动的示例。所以我们可以说我们的示例涵盖了项目管理中的大多数情况。
所以让我们写一个查询来获取前置任务的预测日期，添加活动的持续时间并显示后置任务的新预测日期。
一种简单的方法是循环遍历后续里程碑并用新的预测日期更新它们。但是，该查询的性能会非常低。
但是，当公司运行 50 个计划，每个计划有 5-15 个项目，每个项目有大约 120 个里程碑，并且这些里程碑需要在 7000 个不同的地点实现时，会发生什么？里程碑有时在计划之间存在交叉依赖。例如：为了在项目 B 中开始一个里程碑，项目 A 中需要完成另一个里程碑。
由于里程碑的数量很容易超过数百万，低效的调度程序可能会导致触发下一个活动的延迟。
下面的查询显示了如何根据第一个里程碑更新第二个里程碑的 ForecastDate。但是，此脚本不会遍历链，而是始终更新下一个级联里程碑。
SELECT predm.Milestone PredecessorMilestone , m.Milestone , predm.ForecastDate PredecessorForecastDate , DATEADD(DAY, m.DurationInDays, CASE WHEN md.PredecessorMilestoneId IS NULL THEN m.ForecastDate ELSE predm.ForecastDate END) , m.DurationInDays FROM dbo.tblMilestoneDependency md INNER JOIN dbo.tblMilestone m ON m.MilestoneId = md.MilestoneId -- show all the dependencies of the milestones LEFT JOIN dbo.tblMilestone predm ON predm.MilestoneId = md.PredecessorMilestoneId -- show the name of predecessors if there is any ORDER BY md.MilestoneId
在下面的结果集中，我们可以看到“购买土地”里程碑的新预测日期，比“项目开始”预测日期晚 30 天。
引入递归公共表表达式
幸运的是，SQL 中引入了递归公用表表达式，因此我们能够用一个查询填充里程碑链。
让我们修改前面的查询，并将其用作我们递归公用表表达式中的源表。
;WITH cteCurrentSchedule AS ( SELECT predm.Milestone PredecessorMilestone , m.Milestone , predm.MilestoneId PredecessorMilestoneId , m.MilestoneId , predm.ForecastDate PredecessorForecastDate , m.ForecastDate , m.CompletedDate, m.DurationInDays FROM dbo.tblMilestoneDependency md INNER JOIN dbo.tblMilestone m ON m.MilestoneId = md.MilestoneId -- show all the dependencies of the milestones LEFT JOIN dbo.tblMilestone predm ON predm.MilestoneId = md.PredecessorMilestoneId -- show the name of predecessors if there is any ), cteNewSchedule AS ( --select the start milestones SELECT sch.Milestone , sch.MilestoneId , COALESCE(sch.CompletedDate, sch.ForecastDate) ForecastDate , sch.DurationInDays FROM cteCurrentSchedule sch WHERE sch.PredecessorMilestoneId IS NULL UNION ALL SELECT cs.Milestone , cs.MilestoneId , COALESCE(cs.CompletedDate, DATEADD(DAY, cs.DurationInDays, ns.ForecastDate)) , cs.DurationInDays FROM cteNewSchedule ns INNER JOIN cteCurrentSchedule cs ON ns.MilestoneId = cs.PredecessorMilestoneId ) SELECT u.Milestone , u.MilestoneId , m.ForecastDate , MAX(u.ForecastDate) NewForecastDate , m.CompletedDate , u.DurationInDays --take the longest parth if multiple available FROM cteNewSchedule AS u INNER JOIN dbo.tblMilestone m ON m.MilestoneId = u.MilestoneId GROUP BY u.Milestone , u.MilestoneId , m.ForecastDate , m.CompletedDate , u.DurationInDays ORDER BY MAX(u.ForecastDate)
结果集显示了所有相关里程碑的预测日期。
通过该结果，您可以使用最新的预测日期更新您的原始表 (tblMilestone)，以在用户控件上表示数据（本文未涵盖）。
总而言之，我们可以将逻辑放入存储过程中。当存储过程执行时，如果任何里程碑的前置任务发生更改，它将更新预测日期。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author, Csaba Hegyi>
-- Create date: <Create Date, 2015 Sep 21>
-- Description: <Description, This stored procedure updates milestones forecast dates if any of the predecessors' forecast date od completition date changed>
-- =============================================
ALTER PROCEDURE sp_Milestone_Reschedule
AS
BEGIN
SET NOCOUNT ON;
;WITH cteCurrentSchedule
AS ( SELECT predm.Milestone PredecessorMilestone ,
m.Milestone ,
predm.MilestoneId PredecessorMilestoneId ,
m.MilestoneId ,
predm.ForecastDate PredecessorForecastDate ,
m.ForecastDate ,
m.CompletedDate ,
m.DurationInDays
FROM dbo.tblMilestoneDependency md
INNER JOIN dbo.tblMilestone m
ON m.MilestoneId = md.MilestoneId -- show all the dependencies of the milestones
LEFT JOIN dbo.tblMilestone predm
ON predm.MilestoneId = md.PredecessorMilestoneId -- show the name of predecessors if there is any
),
cteNewSchedule
AS (
--select the start milestones
SELECT sch.Milestone ,
sch.MilestoneId ,
COALESCE(sch.CompletedDate, sch.ForecastDate) ForecastDate ,
sch.DurationInDays
FROM cteCurrentSchedule sch
WHERE sch.PredecessorMilestoneId IS NULL
UNION ALL
SELECT cs.Milestone ,
cs.MilestoneId ,
COALESCE(cs.CompletedDate,
DATEADD(DAY, cs.DurationInDays,
ns.ForecastDate)) ,
cs.DurationInDays
FROM cteNewSchedule ns
INNER JOIN cteCurrentSchedule cs
ON ns.MilestoneId = cs.PredecessorMilestoneId
),
cteLongestPathPerMilestone
as
(
SELECT ns.MilestoneId, MAX(ns.ForecastDate) ForecastDate FROM cteNewSchedule ns
GROUP BY ns.MilestoneId
)
UPDATE m
SET ForecastDate = u.ForecastDate
FROM cteLongestPathPerMilestone AS u
INNER JOIN dbo.tblMilestone m
ON m.MilestoneId = u.MilestoneId
WHERE m.CompletedDate IS NULL OR m.ForecastDate IS NULL
END
GO
存储过程仅重新调度尚未输入完成日期的里程碑。作为示例，我将更新其中两个里程碑的完成日期，以查看该过程如何计算后续里程碑的预测日期。
它将使用前置里程碑的完成日期作为事实来源，而不是其预测日期。
UPDATE dbo.tblMilestone
SET CompletedDate = DATEADD(DAY, 3, ForecastDate)
WHERE MilestoneId = 1
GO
UPDATE dbo.tblMilestone
SET CompletedDate = DATEADD(DAY, 15, ( SELECT ForecastDate
FROM dbo.tblMilestone
WHERE MilestoneId = 1
))
WHERE MilestoneId = 2
查看结果
EXEC sp_Milestone_Reschedule SELECT * FROM dbo.tblMilestone m
结果集是
希望您喜欢这篇文章，如果您有任何问题，请随时与我联系。
关注点
最具有挑战性的一部分是在设计解决方案时，当一个里程碑有多个前置任务，这意味着其中一个前置任务已经完成，但该里程碑的活动仍然必须等待最长的路径完成。
我遇到了一个解决方案，即计算所有可能路径的预测日期，并取最大日期（最长路径）来更新里程碑。这在最终的 select 语句中表示为 NewForecastDate。
实际应用比这个示例项目更复杂。该示例仅涵盖一种里程碑依赖类型，即“完成到开始”。在实施过程中，我提出了针对“开始到开始”、“开始到完成”和“完成到完成”场景的解决方案。
历史
暂无更改。