SQL Server Performance

Weird thing in Stored Procedures.

Discussion in 'Performance Tuning for DBAs' started by vvkp, May 12, 2005.

  1. vvkp New Member

    Hi Friends,

    I have two stored procedures with same code. Only the declaring values .....

    so the first one was executed without any problem in 20seconds.

    The second one was being executing continuously for hours together no completion. No blocks.

    In EM under current activity Locks/Objects all the tables are under lock type 'Tab' and mode 'Sch-S'

    I gave the same date as input to both of them. For the first one its already in the procedure and to the second one I passed it as parameter at the time of execution. Why this happened? Any clue/help/advice?


    Thanks in advance,
    vvkp

    This is the first one

    Create proc dbo.spa_test
    as
    SET NOCOUNT ON

    Declare @start_date as DATETIME,
    @BrokerRep VARCHAR(100)
    SET @start_date = '01/31/2005'

    ... ... ... ... ... ... someTSQL. (same as it is, in both)

    **********************************

    This is the second one

    Create PROC dbo.Get_test
    (@start_date DATETIME)
    AS
    SET NOCOUNT ON
    DECLARE
    @BrokerRep VARCHAR(100)

    .. ... ... ... ... ...someTSQL. (same as it is, in both)


  2. Luis Martin Moderator

    Did you analize both execution plan?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. vvkp New Member

    Nope. The second stored procedure never completing.
    ~VVKP
  4. dineshasanka Moderator

    Second one was completed when i tried this


    Create PROC dbo.Get_test
    (@start_date DATETIME)
    AS
    SET NOCOUNT ON
    DECLARE
    @BrokerRep VARCHAR(100)
    print @start_date
    go

    Can youpost the entire code if possible.

    quote:Originally posted by vvkp

    Nope. The second stored procedure never completing.

  5. Adriaan New Member

    If you can't post the code, let us know if it contains a WHILE loop depending on the value of @startdate?

  6. dhilditch New Member

    And also, where do you call your stored procedures from? If you call them from outside of the SQL box, your date formatting may be an issue. If you use the ISO date format e.g yyyy-mm-dd then you will avoid any problems like this. Is this any help?

    Dave.

    Available for freelance work
    david@matiogi.com
  7. vvkp New Member

    this is the remaining code to both.......
    Advance thanks,
    vvkp
    *********************************************************************
    SELECT
    DB.Broker_Number
    , CASE DPF.Payment_Frequency_Code
    WHEN 'A' THEN SUM(ISNULL(FCR.Cash_Receipt_Premium,FP.Premium_Approved)/12)
    WHEN 'Q' THEN SUM(ISNULL(FCR.Cash_Receipt_Premium,FP.Premium_Approved)/3)
    WHEN 'S' THEN SUM(ISNULL(FCR.Cash_Receipt_Premium,FP.Premium_Approved)/6)
    ELSE SUM(ISNULL(FCR.Cash_Receipt_Premium,FP.Premium_Approved))
    END Monthly_Premium
    , CASE DPF.Payment_Frequency_Code
    WHEN 'A' THEN SUM(ISNULL(FCR.Cash_Receipt_Premium,FP.Premium_Approved))
    WHEN 'Q' THEN SUM(ISNULL(FCR.Cash_Receipt_Premium,FP.Premium_Approved)/3)*12
    WHEN 'S' THEN SUM(ISNULL(FCR.Cash_Receipt_Premium,FP.Premium_Approved)/6)*12
    ELSE SUM(ISNULL(FCR.Cash_Receipt_Premium,FP.Premium_Approved)*12)
    END Annualized_Premium
    , SUM(FL.Life_Approved) Issued_Lives
    , CONVERT(VARCHAR,MAX(ISNULL(FCR.Effective_Date,FP.Effective_Date)),101) Effective_Date
    , MAX(DBR.Rep_Id) Rep_Id
    , COUNT(FL.Case_Number) Cases
    , MAX(DB.Last_Name +'/'+ DB.First_Name) BrokerName
    FROM dbo.Fact_Life FL WITH(NOLOCK)
    LEFT JOIN dbo.Fact_Cash_Receipt FCR WITH(NOLOCK)
    ON FL.Case_Number = FCR.Case_Number
    AND FCR.Time_Id IN(SELECT MAX(Time_Id) FROM dbo.Fact_Cash_Receipt WITH(NOLOCK) WHERE FL.Case_Number = Case_Number)
    INNER JOIN dbo.Fact_Premium FP WITH(NOLOCK)
    ON FL.Case_Number = FP.Case_Number
    AND FL.Plan_Id = FP.Plan_Id
    INNER JOIN dbo.Dim_Broker DB WITH(NOLOCK)
    ON FL.Broker_Id = DB.Broker_Id
    INNER JOIN dbo.Dim_Broker_Rep DBR WITH(NOLOCK)
    ON FCR.Current_Broker_Rep_Id = DBR.Broker_Rep_Id
    INNER JOIN dbo.Dim_Time DT WITH(NOLOCK)
    ON FCR.Time_Id = DT.Time_Id
    INNER JOIN dbo.Dim_Payment_Frequency DPF WITH(NOLOCK)
    ON FL.Payment_Frequency_Id = DPF.Payment_Frequency_Id
    WHERE
    (
    (DT.[Day] BETWEEN DATEADD(d, -DAY(@Start_date)+1,DATEADD(m,-2,@Start_date)) AND @Start_date
    AND FCR.Terminate_Date NOT BETWEEN '1/1/1970' AND @Start_date AND DB.Last_Name NOT LIKE '%BENEFITMALL%')
    OR (FP.Effective_Date BETWEEN DATEADD(d, -DAY(@Start_date)+1,DATEADD(m,-2,@Start_date)) AND @Start_date
    AND FP.Terminate_Date NOT BETWEEN '1/1/1970' AND @Start_date AND FP.Premium_Approved > 0 AND DB.Last_Name NOT LIKE '%BENEFITMALL%')
    )
    AND DBR.Rep_Id LIKE ISNULL(@BrokerRep,'%')
    GROUP BY DB.Broker_Number, DPF.Payment_Frequency_Code
  8. Adriaan New Member

    I'm never sure about the different programming languages and how they handle "CASE" thingies, but I always try to avoid repeating complete calculations/evaluations. For instance in VB when you use <b>IIf(&lt;condition&gt;, &lt;true_part&gt;, &lt;false_part&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /></b> it always evaluates both parts before returning either result - and you can get nasty run-time errors.<br /><br />Also, I would not use functions like ISNULL() for potentially large rowsets: I have noticed that for instance COALESCE(col, othercol) can make a query a lot slower than CASE WHEN col IS NULL THEN othercol ELSE col END<br /><br />So I would rewrite those expressions alonge these lines:<br />SUM((CASE WHEN FCR.Cash_Receipt_Premium IS NULL THEN FP.Premium_Approved ELSE FCR.Cash_Receipt_Premium END)<br />/(CASE DPF.Payment_Frequency_Code WHEN 'A' THEN 12 WHEN 'Q' THEN 3 WHEN 'S' THEN 6 ELSE 1 END)) Monthly_Premium<br /><br />................<br /><br />Don't use DATEADD within the WHERE statement if you can avoid it: you would be better off using a local DATETIME variable to store the result of DATEADD(d, -DAY(@Start_date)+1,DATEADD(m,-2,@Start_date)).<br /><br />You could also gather the data from the JOINed tables in a single derived table that has only <b>WHERE DB.Last_Name NOT LIKE '%BENEFITMALL%'</b> as criteria. You apply all the date criteria on the derived table.<br /><br />Finally, check if all the columns that have criteria are in any way covered by indexes, and again if all the columns used for JOINs are also indexed.

Share This Page