How can I increase stored procedure performance with multiple if else statement?

Question

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 12-Jun-17 20:30pm

Bhavesh Jogani IT

Updated 27-Jul-17 23:57pm

Add a Solution

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.

Bhavesh Jogani IT 13-Jun-17 23:21pm

Will you please suggest best tool for it?

ZurdoDev 14-Jun-17 7:24am

Put the sql into a query window and use the Display Estimated Execution Plan option.

1 solution

Add a Solution

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

ashokmaurya786 · Answer 1 · 2017-07-27T23:57:00

Solution 1

you can use CTE with this context.

Posted 27-Jul-17 23:57pm

ashokmaurya786

How can I increase stored procedure performance with multiple if else statement?

1 solution

Solution 1

Add your solution here

Preview 0

How can I increase stored procedure performance with multiple if else statement?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us