1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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