Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Database (http://www.go4expert.com/forums/database-forum/)
-   -   Stored procedure taking a lot of time when 'else' part is uncommented'! (http://www.go4expert.com/forums/stored-procedure-taking-lot-time-part-t21723/)

Curious89 10Apr2010 14:17

Stored procedure taking a lot of time when 'else' part is uncommented'!
 
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:


All times are GMT +5.5. The time now is 02:36.