1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Stored procedure taking a lot of time when 'else' part is uncommented'!

Discussion in 'Database' started by Curious89, Apr 10, 2010.

  1. Curious89

    Curious89 New Member

    Joined:
    Apr 10, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Hello all,

    I am facing a very strange problem in my stored procedure. (i am using SQL Server 2005)
    Please help.

    I have got 4 cursors declared. I need 1st two cursors when a variables's (say, var_groupby) value is 'R' and the 3rd and 4th when var_groupby is 'P' .

    Code:
    if var_groupby = 'R'
     
    begin
    declare cursor1........... local static
    select * from tab where ................................................
     
    declare cursor2........... local static
    select * from tab where ................................................
    end
     
    else if var_groupby = 'P'
     
    begin
    declare cursor3........... local static
    select * from tab where ..............................................
     
    declare cursor4........... local static
    select * from tab where .............................................
     
    end
     
    When i execute this it takes 1 min 45 secs to give the result.

    Now when i comment the else part, i.e.:

    Code:
    if var_groupby = 'R'
     
    begin
    declare cursor1........... local static
    select * from tab where ................................................
     
    declare cursor2........... local static
    select * from tab where ................................................
    end
    -- commented the below 'else' part
     /*
    else if var_groupby = 'P'
     
    begin
    declare cursor3........... local static
    select * from tab where ..............................................
     
    declare cursor4........... local static
    select * from tab where .............................................
     
    end */
     
    Now, when i execute this it takes 21 secs to give the result.

    In both the above cases i am executing the stored procedure with var_groupby = 'R'. (The stored procedure is of approx. 2500 lines.)

    Why is it happening so? Why is it taking so much time when i uncomment the 'else' part? How to resolve this? :crazy:
     

Share This Page