SQL Server Performance

Strange stored procedure execution issue

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by thakor, Jan 15, 2013.

  1. thakor New Member

    Hi all,
    I got to interesting issue and I have no clue what is/could be happening in the system.
    We run SQL 2005 server and we do reporting system.
    We have couple of procedures doing queries in a very similar way.

    All was going fine, but two days ago one of the procedures started to behave very slow.

    What I can see. The procedure runs for several minutes. It does a select query inside using CTE and parameters (datetime and nvarchar).

    I thought it is something in the data causing bad execution, so I analyzed the query.

    But, when I take the body of the sp to a new window in sql studio and execute it it runs fast as all the other procedures.

    I tried to drop and create the sp again, I have created a copy of the sp, but it has no effect. Sp runs for ages the query for milliseconds.

    There is quite lot of processes now on the database, many clients doing their work, but no one works with the sp.

    Any ideas what to check?
    Thanks in advance.
    Tom
  2. FrankKalis Moderator

  3. thakor New Member

    OK, let's change the question.
    What is the main difference between executing stored procedure and executing statement, that is the body of the stored procedure?
    I have significant performance difference. The statement itself is much faster than when it is wrapped with the stored procedure.

    The statement:

    Code:
    DECLARE @StartDT DATETIME, @EndDT DATETIME, @Area NVARCHAR(50), @Language NVARCHAR(10);
    
    SET @StartDT = '2013-01-16';
    SET @EndDT = '2013-01-17';
    SET @Area = 'Cold_Block';
    SET @Language = 'EN';
    
    WITH filterData (Recipe, RecipeType, UnitType, Unit, Circuit, [SERVER], [DATABASE]) AS
        (
            SELECT DISTINCT
                rer.Recipe,
                rer.RecipeType,
                tc.Translation,
                eqr.EQ_DestDescription,
                reorv.Circuit,
                @@SERVERNAME,
                DB_NAME()
    
            FROM dbo.tvf_rp3_REUnitProcedureByTime(@StartDT, @EndDT) reup
                JOIN (
                    SELECT 'Line' as UT,* FROM dbo.tvf_rp3_EQUnit('CIPLCMN', NULL)
                    UNION
                    SELECT 'Tank' as UT,* FROM dbo.tvf_rp3_EQUnit('CIPTCMN', NULL)
                ) equ ON reup.EQ_UN_ID = equ.EQ_UN_ID
                JOIN dbo.tvf_rp3_RERecipe(@Area) rer ON reup.RE_RC_ID = rer.RE_RC_ID
                JOIN dbo.tvf_rp3_REOperationReportValues(NULL, NULL, NULL, @StartDT, @EndDT) reorv ON reup.RE_UP_ID = reorv.UPID AND reorv.Circuit IS NOT NULL
                JOIN dbo.tvf_rp3_TranslateCollection('UnitType', @Language) tc ON equ.UT = tc.Name
                JOIN dbo.EQ_Route eqr ON eqr.EQ_UN_ID_Manager = equ.EQ_UN_ID AND eqr.CipCircuit = reorv.Circuit
        )
        -- select all from CTE
        SELECT * FROM filterData
        -- add empty
        UNION
        SELECT NULL, NULL, NULL, NULL, NULL, @@SERVERNAME, DB_NAME()
        WHERE (SELECT COUNT(*) FROM filterData) = 0
    The stored procedure:
    Code:
    CREATE PROCEDURE [dbo].[sp_ft3_CIPObject]
        -- parameters
        @StartDT    DATETIME,
        @EndDT        DATETIME,
        @Area        NVARCHAR(50),
        @Language    NVARCHAR(10)
    AS
    BEGIN
        -- define CTE
        WITH filterData (Recipe, RecipeType, UnitType, Unit, Circuit, [SERVER], [DATABASE]) AS
        (
            SELECT DISTINCT
                rer.Recipe,
                rer.RecipeType,
                tc.Translation,
                eqr.EQ_DestDescription,
                reorv.Circuit,
                @@SERVERNAME,
                DB_NAME()
    
            FROM dbo.tvf_rp3_REUnitProcedureByTime(@StartDT, @EndDT) reup
                JOIN (
                    SELECT 'Line' as UT,* FROM dbo.tvf_rp3_EQUnit('CIPLCMN', NULL)
                    UNION
                    SELECT 'Tank' as UT,* FROM dbo.tvf_rp3_EQUnit('CIPTCMN', NULL)
                ) equ ON reup.EQ_UN_ID = equ.EQ_UN_ID
                JOIN dbo.tvf_rp3_RERecipe(@Area) rer ON reup.RE_RC_ID = rer.RE_RC_ID
                JOIN dbo.tvf_rp3_REOperationReportValues(NULL, NULL, NULL, @StartDT, @EndDT) reorv ON reup.RE_UP_ID = reorv.UPID AND reorv.Circuit IS NOT NULL
                JOIN dbo.tvf_rp3_TranslateCollection('UnitType', @Language) tc ON equ.UT = tc.Name
                JOIN dbo.EQ_Route eqr ON eqr.EQ_UN_ID_Manager = equ.EQ_UN_ID AND eqr.CipCircuit = reorv.Circuit
        )
        -- select all from CTE
        SELECT * FROM filterData
        -- add empty
        UNION
        SELECT NULL, NULL, NULL, NULL, NULL, @@SERVERNAME, DB_NAME()
        WHERE (SELECT COUNT(*) FROM filterData) = 0
    
    END
    GO
    
    As you can see, there is no difference in the code, they are copied from the same file :)

    So, what makes the performance difference?
  4. FrankKalis Moderator

    The code is pretty identical, however have you compared the execution plans if they are as well? Take your time to read through this white-paper, if you haven't done already. It gives some explanations as to what might be the issue.

Share This Page