Monday, December 14, 2009

Stored Procedure Practices

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.




No comments:

Post a Comment