SQL Server Performance

performance issue with stored procedure

Discussion in 'Performance Tuning for DBAs' started by aswin, Apr 21, 2004.

  1. aswin New Member

    I have a performance issue with stored procedure . When i try to execute my Stored Procedure it takes 2+ minutes to give the result.

    But if i try run the select statement part alone in Stored Procedure and run in Query analyzer it takes just 20-30 seconds to give the result. What could be the reason?


    Stored Procedure logic goes like this....
    CREATE Procedure sp_ReportWatsAgentThroughPut
    @StartDate DateTime,
    @StopDate DateTime,
    @AppType varchar(10),
    @AgentId varchar(25)
    AS
    --Business Logic Query
    Select ... From WatsAgentThroughPut W,
    WatsAgentThroughPutTotalFlaggedFields TF,
    WatsAgentThroughPutFirstCallFlaggedFields FC,
    WatsAgentThroughPutSecondCallFlaggedFields SC,
    BCBSFL..WatsAppTypes WA,
    WatsAgentThroughPutFirstCallBatches TFC,
    WatsAgentThroughPutSecondCallBatches TSC
    Where
    W.TransmissionDate *= TF.WatsFieldsDate AND
    ....
    ....
    W.TransmissionDate *= TSC.WatsFieldsDate AND

    W.ItemName *= TF.Ite_Name AND
    ....
    ....
    W.ItemName *= TSC.Ite_Name AND

    W.KeyerId *= TF.KeyerId AND
    ....
    W.KeyerId *= TSC.KeyerId AND
    Cast(W.TransmissionDate as DateTime) BETWEEN @StartDate AND @StopDate AND
    WA.AppType like @AppType AND
    SUBSTRING(W.ItemName, 1, 1) = WA.BatchPrefix AND
    W.KeyerId = @AgentId
    Group By W.TransmissionDate, W.KeyerId
    Order by W.TransmissionDate, W.KeyerId


    When i try to execute this Stored Procedure it takes 2+ minutes to give the result.

    But if i try using the following ....
    DECLARE @StartDate varchar(25)
    DECLARE @StopDate varchar(25)
    DECLARE @AppType varchar(10)
    DECLARE @AgentId varchar(25)

    SET @StartDate = '03/01/2004'
    SET @StopDate = '03/19/2004'
    SET @AppType = ''
    SET @AgentId = 'w4290'

    and then call the --Business Logic Select Staement and run in Query analyzer it takes just 20-30 seconds to give the result. What could be the reason? It seems strange for me as i'm new to DB...

  2. satya Moderator

    Try to recompile the stored procedure and also reindex the involved indexes on the tables.
    Best option is to run PROFILER during execution of this SP and submit the trace results to Index Tuning Wizard for any recommendation.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. gaurav_bindlish New Member

    Yup, recompiling the stored proceure and updating statistics for the tables under consideration should help.

    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.
  4. Raulie New Member

    Another tip I would change my code from TSQL Syntax to Ansi when performing joins, MicroSoft also has stated the the older outer join syntax will not be supported in future releases. Plus sometimes could generate unwanted results.

Share This Page