优化存储过程

Question

>我有一个存储过程，它被多次调用以获取显示页面所需的所有元素。

示例数据：

CREATE TABLE #temp 
(plan_id INT, parentid INT, label VARCHAR(20),  color VARCHAR(10),  comp_id INT,    start_date DATETIME) 
INSERT INTO #temp 
VALUES
(607, NULL,'abc',   'CDC',  432,    '2018-05-22 00:00:00'),
(607,NULL,'abc',    'CDC',  432,    '2018-05-22 00:00:00'),
(607,NULL,'abc',    'CDC',  433,    '2018-05-22 00:00:00'),  
(NULL,432,'def',    'CDC',  434,    '2018-05-22 00:00:00'),
(NULL,432,'def',    'CDC',  434,    '2018-05-22 00:00:00'),
(NULL,433,'def',    'CDC',  435,    NULL),
(NULL,433,'def',    'CDC',  435,    NULL), 
(NULL,434,'obj',    'CDC',  436,    '2018-05-22 00:00:00'),
(NULL,434,'obj',    'CDC',  436,    '2018-05-22 00:00:00'),
(NULL,435,'obj',    'CDC',  437,    NULL), 
(NULL,436,'ion',    'CDC',  438,    '2018-05-22 00:00:00'),
(NULL,436,'ion',    'CDC',  438,    '2018-05-22 00:00:00'), 
(NULL,437,'ion',    'CDC',  439,    NULL)

存储过程的第一次调用提供plan_id (@plan_id) 并获取父节点。然后，这些节点在页代码中循环，后续调用同一存储过程，但comp_id元素在循环中，然后通过另一个调用循环查找其子级。

对于具有许多元素的大型计划，这是非常低效的。(例如，如果使用 @plan_id = 607 执行此操作，则将使用 comp_id 提取父节点;并且将在下一次调用中提供父节点，@plan_id为 null)。

DECLARE
@plan_id INT =null,
@parentid INT =null
IF @plan_id is not null 
BEGIN   
SELECT label, color, comp_id, start_date FROM #temp WHERE plan_id = @plan_id 
END 
ELSE 
BEGIN 
SELECT label, color, comp_id, start_date FROM #temp WHERE parentid = @parentid  
END 

即存储过程将在 @plan_id = 607 和 @parentid = null 的情况下调用; @plan_id = 空和 @432;@plan_id = 空，@parentid = 433;@plan_id = 空，@parentid = 434; @plan_id = 空，@parentid = 435;@plan_id = 空，@parentid = 436;和 @plan_id = 空，@parentid = 437

例如：当 @plan_id = 607 时，输出为：

我想做的是，在单个高效的存储过程中构建它，该过程将返回显示页面所需的所有内容。

根据我们的示例，我希望获得以下结果：

SELECT label, color, comp_id, start_date FROM #temp 

第一次执行时，plan_id = 607，存储过程给出：

label   color   comp_id start_date
abc     CDC     432     2018-05-22 00:00:00.000
abc     CDC     432     2018-05-22 00:00:00.000
abc     CDC     433     2018-05-22 00:00:00.000

然后，需要 432 和 433 来获取 parentId;然后它将调用存储过程以使用父 ID 执行，使计划 Id 保持空。例如，当 parentid = 432 时，它将返回以下输出：

label   color   comp_id start_date
def     CDC     434     2018-05-22 00:00:00.000
def     CDC     434     2018-05-22 00:00:00.000

该表只是一个示例，用于显示其工作原理和预期输出。

否则，plan_id 和 parentid 是存储过程的参数，结果是在联接多个表并使用所需条件进行筛选之后的结果。

任何人都可以帮助我优化这一点，以便使单个调用高效的存储过程？

Answer 1

它看起来像一个简单的递归公用表表达式 (CTE)：

declare @Samples as Table ( PlanId Int, ParentId Int, Label VarChar(20), Color VarChar(10), CompId Int, StartDate DateTime );
insert into @Samples ( PlanId, ParentId, Label, Color, CompId, StartDate ) values
(607, NULL,'abc',   'CDC',  432,    '2019-05-22 00:00:00'),
(607,NULL,'abc',    'CDC',  432,    '2018-05-22 00:00:00'),
(607,NULL,'abc',    'CDC',  433,    '2018-05-22 00:00:00'),  
(NULL,432,'def',    'CDC',  434,    '2018-05-22 00:00:00'),
(NULL,432,'def',    'CDC',  434,    '2018-05-22 00:00:00'),
(NULL,433,'def',    'CDC',  435,    NULL),
(NULL,433,'def',    'CDC',  435,    NULL), 
(NULL,434,'obj',    'CDC',  436,    '2018-05-22 00:00:00'),
(NULL,434,'obj',    'CDC',  436,    '2018-05-22 00:00:00'),
(NULL,435,'obj',    'CDC',  437,    NULL), 
(NULL,436,'ion',    'CDC',  438,    '2018-05-22 00:00:00'),
(NULL,436,'ion',    'CDC',  438,    '2018-05-22 00:00:00'), 
(NULL,437,'ion',    'CDC',  439,    NULL);
select * from @Samples;
with Things as (
-- Get all of the plans ...
select PlanId, ParentId, Label, Color, CompId, StartDate
from @Samples
where ParentId is NULL
union all
-- ... add the children one level at a time.
select S.PlanId, S.ParentId, S.Label, S.Color, S.CompId, S.StartDate
from Things as T inner join @Samples as S on T.CompId = S.ParentId
)
select PlanId, ParentId, Label, Color, CompId, StartDate
from Things;