Optimize a stored procedure

Question

I have a stored procedure that is called multiple times to get all the elements necessary to display the page.

Sample data:

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)

The first call of the stored procedure provides a plan_id (@plan_id) and gets the parent nodes. Then, these nodes are looped through in the page code with subsequent calls to the same stored procedure, but with the comp_id of the element in the loop, which is then looped through with another call to find its children.

This is very inefficient on a large plan with many elements. (for example if you execute this with @plan_id = 607, the parent nodes will be extracted using the comp_id; and they will be provided in the next calls with @plan_id being 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 

i.e. The stored procedure will be called with @plan_id = 607 and @parentid = null; @plan_id = null and @432; @plan_id = null and @parentid = 433; @plan_id = null and @parentid = 434; @plan_id = null and @parentid = 435; @plan_id = null and @parentid = 436; and @plan_id = null and @parentid = 437

For example: when @plan_id = 607, the output is:

What I want to do is, to build out this in a single efficient stored procedure that will return everything required to display the page.

As per our example, I want to have results like:

SELECT label, color, comp_id, start_date FROM #temp 

The first time it executes with plan_id = 607, the stored procedure gives:

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

Then it will take 432 and 433 to get the parentIds; and then it will call the stored procedure to execute with the parent ids, keeping the plan Id null. For example, with parentid = 432, it would return the following output:

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

The table is just an example to show how it works and the expected output.

Otherwise, plan_id and parentid are parameters of the stored procedure and the results are after joining multiple tables and filtering with required conditions.

Can anyone help me optimize this, so as to make a single call efficient stored procedure?

Answer 1

It looks like a straightforward recursive Common Table Expression (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;