SQL Server Performance

Group numbers in SPs

Discussion in 'Performance Tuning for DBAs' started by Raulie, Mar 31, 2004.

  1. Raulie New Member

    Does anyone know where I can find resources on the use of group numbers in stored procedures. I tried searching BOL but couldnt find anything. Are they worth while when you have a lot of Business Logic? Having seperate execution plans for each condition. Here is small example.

    CREATE PROCEDURE dbo.PA_LP;1
    @Ps_Id INT = 0
    AS

    SET NOCOUNT ON

    IF @Ps_Id = 0


    EXEC dbo.PA_LP;2
    ELSE
    EXEC dbo.PA_LP;3 @Ps_Id

    GO



    CREATE PROCEDURE dbo.PA_LP;2
    AS
    SET NOCOUNT ON

    SELECT COLUMNLIST from dbo.MYTABLE

    GO

    CREATE PROCEDURE dbo.PA_LP;3
    @Ps_Id INT
    AS
    SET NOCOUNT ON

    SELECT COLUMNLIST from dbo.MYTABLEl WHERE Ps_Id = @Ps_Id
  2. gaurav_bindlish New Member

    I think that the logic that you mentioned above can be handled in a single procedure. Having groups just aids in the management and I don't see a reason to use them when all the programming can be done in a single procedure using in-built syntax. e.g.
    CREATE PROCEDURE dbo.PA_LP
    @Ps_Id INT = 0
    AS

    SET NOCOUNT ON

    IF @Ps_Id = 0
    SELECT COLUMNLIST FROM dbo.MYTABLE
    WHERE Ps_Id = CASE @Ps_Id WHEN 0 THEN Ps_Id
    ELSE @Ps_Id END
    HTH...

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. Raulie New Member

    Thanks, so is having all the code in on exection plan the same than having multiple execution plans for differnt conditions?
  4. vbkenya New Member

    Quick Notes:

    1. The little info on SP group numbers is embedded within the CREATE PROCEDURE syntax help. the paucity of further discussion on the issue is perhaps due to the relative lack of usage (and calls for help) within the DBA/developer community.

    2. The use of SP group numbers has nothing to do with the amount of business logic to be implemented nor is it recommended. Writing the same procedures with different names would still achieve the same results as group-numbering them. Consolidating the logic in to one procedure might be of benefit especially on transactional integrity (if any), parallelism (if necessary) and optimal creation and use of indexes.

    3. One optimized plan is always better than three.

    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  5. gaurav_bindlish New Member

    Even if there are multiple conditions within the same procedure, I don't think there will be saperate plans fro the same.

    Also I second to what Nathan has said. Having single execution plan is always better.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  6. Raulie New Member

    Thanks for the input!
  7. derrickleggett New Member

    Even if there are multiple conditions within the same procedure, I don't think there will be saperate plans fro the same.

    Also I second to what Nathan has said. Having single execution plan is always better.

    I don't use group numbers, but we do have several stored procedures that have been broken into sub-procedures with a "wrapper procedure".

    If you don't do this, you end up recompiling the stored procedures when different plans are encountered. This can take as long and be as resource intensive as the query itself. If you are on a high-transaction system, this can kill you.

    Having a single execution plan is not always better, because it's impossible in many cases. If the conditions change and you have a lot of if/then logic in your query you will not have a single execution plan. You will have several plans that it has to compile each time conditions change. You will also sometimes get stuck with a bad plan that will kill you.

    If you break it into seperate procedure, you don't have to worry about it.

    Derrick Leggett

Share This Page