Stored Procedure Practices:
- Do not use Case statements in where clauses, they are a performance nightmare and hard for SQL to optimize.
- You shouldn’t put IF statements that run different queries in a SP as the query optimizer will only optimize the branch that gets executed the first time. When you take the alternate branch, you will have table scans for everything.
- To get around this somewhat by having SP_WITH_RECOMPILE which will force a recompile each time – better solution is to split into separate SPs that can each be optimized and have the main SP call into the right one based on a simple/fast IF
- Any hidden IO in the functions, should be unwound out of the function and put in the main query instead
- As a general philosophy, from what we’ve seen in the database coding {or lack thereof in understanding how the query optimizer works in general} they should just be retrieving data from the database and the middle-tier or client should be doing all this display formatting.
- A lot of function calls in the SPs should be unwound – again perhaps refactoring to the middle tier as opposed to putting function calls to outside C# .dlls in where clauses – you will see a cleaner code base and better options for performance enhancements.
Regards,
Arun
No comments:
Post a Comment