Optimize a stored procedure

  • keneangbu

    Mr or Mrs. 500

    Points: 566

    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?

    • This topic was modified 3 weeks, 6 days ago by  keneangbu.
  • Jeffrey Williams 3188

    SSC Guru

    Points: 88020

    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

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply