Click here to Skip to main content
15,883,705 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello,
I have one stored procedure with multiple if-elseif- else statements.When i try to remove this if elseif statement and execute only single portion of that query, it give result very fast, but when use this query with multiple another queries using if-elseif case statement, then it takes too much time...

What I have tried:

I have tried to remove if elseif statement and execute single query then it works very fast.But the same query with if statement then it took to much time for generate the result.
Posted
Updated 27-Jul-17 23:57pm
Comments
Suvendu Shekhar Giri 13-Jun-17 2:33am    
Please show your query.
Does your conditional statement contain a query?
Bhavesh Jogani IT 13-Jun-17 2:53am    
if @Status = 1
begin
select .....
end
else if @Status = 2
begin
select .....
end
else if @Status = 3
begin
select .....
end
else if....

there are many more else if statements in this stored procedure..
Suvendu Shekhar Giri 13-Jun-17 7:39am    
Ok. I guess the select statements inside each conditional block will be identical and in that case it may also be possible to write a single select statement with efficient use of CASE WHEN statements.

Is it possible to show 2 or 3 if..else.. in that case?
You can change the table and column names but please keep the query structure same.
Richard Deeming 13-Jun-17 10:54am    
Sounds like it could be parameter sniffing[^] - SQL is choosing an execution plan that works well for one set of parameters, but not for another.

Try moving the body of each branch into its own stored procedure, or adding OPTION RECOMPILE to your procedure.
ZurdoDev 13-Jun-17 8:56am    
These if statements will not slow down your performance. You need to use the tools to find out why it's slow.

1 solution

you can use CTE with this context.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900