Tips for Writing a Good Store Procedure Part II

WHERE clauses :

• In a WHERE clause, the various operators used directly affect how fast a query can run. Here are the conditional operators used in the WHERE clause, ordered by their performance. =, >, <, >=, <=, <>, !=, !>, !<.
•  Avoid necessary where clause and mulitiple "AND" and "OR" clause. For example see below example :
  
  SELECT name FROM employee WHERE LOWER(name) = 'ab'
  SELECT name FROM employee WHERE name = 'AB' OR name = 'ab'

  Avoid DISTINCT and ORDER BY :

  • If no need then avoid using distinct and orderby clause in your query cause it causes extra load on database engine.

  CAST and CONVERT :

  • Try to use CAST instead of CONVERT. CAST is ANSI-92 standard but CONVERT works in MS SQL server only. Also, Convert may be deprecated in future MS SQL releases.
  • It is better to use CONVERT only when you need to format the DATETIME datatype with the style option. CAST can not do this.

  
  

  Avoid using cursors :

  
  

  • Try to use temporary table or table variables with identity column and then iterate all the tables using WHILE loop and a looping counter, which will map with the identity column.