asked
Refactoring a Stored Procedure that has too many IF statements
I have a Stored Procedure that has grown into quite a mess over time. It began from a user need to pass parameters, and return differently filtered result sets based on the valued of the parameters. So, For a SP with parameters A and B, the pseudo code looked something like: @A varchar @B varchar If (A = SomeValue and B = SomeValue) begin Select X,Y,Z where D=E and F=G End If(A = SomeOtherValue and B = SomeOtherValue) begin Select X,Y,Z where D=N and F=O End This worked fine and was easy to manage with just a couple parameters. However, over time, more and parameters have been added so the different combinations have grown in to a few dozen IF statements. I am trying to find a way to refactor this into something more manageable, but not sure even how to begin. Would maybe Case Selects be a good way to go here or, would that end up being just as complex?
If I understood your question correctly, this is not the best way to approach this situation. Keep in mind that SP compiles the execution plan with the first parameters that you will provide. Say If you run this SP with these pair (A = SomeValue and B = SomeValue) and compile the execution plan. Say, this pair returns 10 rows back! Now you have a plan in cache. The next time you run your SP with these If(A = SomeOtherValue and B = SomeOtherValue) parameters using the cached plan which suppose to return only 10 rows but in reality returns 10,000. The performance will degrade! Try modularizing you root SP, calling smaller stored procedure from the root one. It's hard to tell without knowing more information about your data. Also you could run your procedure using all kinds of different combinations of parameters with EXEC YOURSP WITH RECOMPILE in your development environment to see if you get different execution plans, then you could adjust your code accordingly. You might also consider using OPTION(RECOMPILE) on the statement level but in your case, I don't think it will help because you have the IF logic in the body of SP itself.
Thanks for the reply. I will run that by my boss. Unfortunately, we work in an environment where we have to go offsite with a copy of the code to rebuild the EDMX, and that requires changing mappings, and is really not that easy to do. So, while adding more, smaller SP's might be the best way to optimize things, getting to that point requires a lot of hoops to be jumped through. It does sound like with different query execution plans being constantly cached and changed that might be the best way to go in the long term - I guess I was hoping that was not the case.
I agree. Breaking it into sub-procedures so that each one is independent of the others is a good approach.
15 People are following this question.
