June 19, 2019 at 7:12 pm
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).
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
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 sproc to execute with the parent ids, keeping the plan Id null. For example, with parentid = 432, it would give 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?
June 19, 2019 at 10:06 pm
It isn't clear from the sample data - what is your expected results?
I think you are looking for either an outer join - or a recursive CTE...here is an example of an rCTE:
Declare @temp Table (
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);
Select *
From @temp t;
With rParent
As (
Select t.plan_id
, t.parentid
, t.label
, t.color
, t.comp_id
, t.[start_date]
From @temp t
Where t.plan_id = 607
Union All
Select r.plan_id
, t.parentid
, t.label
, t.color
, t.comp_id
, t.[start_date]
From @temp t
Inner Join rParent r On r.comp_id = t.parentid
)
Select *
From rParent
Order By parentid
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply