locked
performance issues calling stored proc from within a stored proc - RRS feed

  • Question

  • Hi,

    Is there any significant execution time difference between the following two things

    1. calling a proc which also call another proc. 

    2. calling a proc which calls the query.

    EX for 1st scenario:

    updateStudentInfo() contains another proc called updateParentInfo(). updateStudentInfo() - this will update the student info into the table student

    updateParentInfo() - this will update the student's parent info to the same table student

    2. EX for 2nd  scenario:

    updateStudentInfo() - this will update the student info into the table student along with content of updateParentInfo() proc as a inline.

    if the time of execution of 2nd query is comparatively less than 1st query.

    My question is 

    which one is best way.

    Please suggest a best solution with explanation.

    Thanks in advance

    Bhoomesh.

    Tuesday, February 4, 2014 1:02 PM

Answers

  • Thanks for the answer.

    Yes, i agree with your answer.

    but if the 2nd proc is using many places and if we combine into single proc which means duplicating the code.

    is that fine?

    Well that's the precise point of testing both solutions.  If there's little or no performance difference then it makes perfect sense to use the most maintainable solution, i.e. using 2 different procedures so that the code can be reused.

    Of course it's fine to implement a solution that you can justify.  Maintainability is a very important factor in software development and clearly a justifiable reason to implement a solution with 2 different stored procedures assuming the performance of that solution is adequate.


    Tuesday, February 4, 2014 3:06 PM

All replies

  • It is unlikely to have difference between these two methods as the difference is looking only the the queries wrapped in the first method in another proc. Please correct if thats not the case. Or share your queries.

    Also, you may check the execution plan and confirm for any differences.

    Tuesday, February 4, 2014 1:14 PM
  • Does the second procedure accepts parameters?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 4, 2014 1:21 PM
    Answerer
  • yes, you are right. 

    here is the EX for 1st scenario:

    1st proc info

    CREATE PROCEDURE [dbo].[updateStudentInfo] 
    @p_iStudentId int, 
    @p_sStudentName varchar(256), 
    @p_sDescription varchar(128), 
    @p_iStudentClass int, 
    @p_iStudCategory int, 
    @p_iLanguage int
    AS 
    BEGIN   
    UPDATE student SET stud_name = @p_sStudentName,   stud_desc=@p_sDescription,   stud_class=@p_iStudentClass,   stud_category=@p_iStudCategory,   language=@p_iLanguage WHERE id = @p_iStudentId  
    if(@@rowcount=1)
    begin
    exec gp_sp_updateParentInfo @p_iStudentId
    end 
    end

    Second proc info:
    CREATE  PROCEDURE [dbo].[gp_sp_updateParentInfo]
    @p_iStudentId
    as
    begin
    update student set parent_name='Test', updated_time=getDate() where id = @p_iStudentId

    end

    =================================

    EX for 2nd  scenario:

    CREATE PROCEDURE [dbo].[updateStudentInfo] 
    @p_iStudentId int, 
    @p_sStudentName varchar(256), 
    @p_sDescription varchar(128), 
    @p_iStudentClass int, 
    @p_iStudCategory int, 
    @p_iLanguage int

    AS 
    BEGIN   
    UPDATE student SET stud_name = @p_sStudentName,   stud_desc=@p_sDescription,   stud_class=@p_iStudentClass,   stud_category=@p_iStudCategory,   language=@p_iLanguage WHERE id = @p_iStudentId  
    if(@@rowcount=1)
    begin
    update student set parent_name='Test', updated_time=getDate() where id = @p_iStudentId
    end 
    end

    Is there any improvement on the second method in terms of execution?

    • Edited by Bhoomesh Tuesday, February 4, 2014 1:56 PM
    Tuesday, February 4, 2014 1:54 PM
  • yes, it accept parameter
    Tuesday, February 4, 2014 1:54 PM
  • So it may or may not affected by parameter sniffing....if so which may slow down the perfromance

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 4, 2014 1:57 PM
    Answerer
  • No, what am asking is, instead of creating a separate proc for updating parent info, if i update the same information on the same or different table located on a single proc (first example).

    to be clear,

    which is the best way performance wise

    calling a proc within a proc  or

    calling all the statements inside a single proc

    Note:

    proc inside a proc updated data in different table, that is what we used as separate proc.

    hope you understood

    Tuesday, February 4, 2014 2:10 PM
  • My guess is that putting all your updates in a single procedure will result in the best performance but I suspect the difference in performance between the two solutions will be minute.

    In this case the right way to answer your question is to test both solutions.  That's what I do.  I'll run the two alternatives against every record in my database and time the performance of each.

    If my testing shows there is a very small difference in performance then I'll go with the solution which is least costly to maintain.  If there's a significant difference in performance then I generally go with the solution which performs the best assuming the difference in performance is great enough.

    Tuesday, February 4, 2014 2:26 PM
  • Thanks for the answer.

    Yes, i agree with your answer.

    but if the 2nd proc is using many places and if we combine into single proc which means duplicating the code.

    is that fine?

    Tuesday, February 4, 2014 2:44 PM
  • Thanks for the answer.

    Yes, i agree with your answer.

    but if the 2nd proc is using many places and if we combine into single proc which means duplicating the code.

    is that fine?

    Well that's the precise point of testing both solutions.  If there's little or no performance difference then it makes perfect sense to use the most maintainable solution, i.e. using 2 different procedures so that the code can be reused.

    Of course it's fine to implement a solution that you can justify.  Maintainability is a very important factor in software development and clearly a justifiable reason to implement a solution with 2 different stored procedures assuming the performance of that solution is adequate.


    Tuesday, February 4, 2014 3:06 PM
  • In your example, there should be very little difference in performance.  There is a small amount of overhead to store the variables and call the 2nd proc.  However, if you call the proc 1 billion times, 1 nano second difference will add up.

    In the specific example you gave, you would probably be better off using a trigger to update the other table or eliminate that field entirely and get it from the studentinfo table.

    Tuesday, February 4, 2014 3:44 PM
  • Thanks for all who responded my question.
    Wednesday, February 5, 2014 5:58 AM