Wednesday, 29 June 2011

SQL Optimization: Transaction - Part 1

The optimization is an integral part of software development. There are a lot of things which we need to optimize as coding, speed, SQL statement, etc.. for example. Hence, this entry will provide some information about how to optimize SQL Transaction Statement that I got from the internet.
  • Try to restricts the queries result set by using WHERE clause: the SQL server will return to client only particular columns, not all table's column. It means reducing SQL network traffic
  • Try to avoid using the COUNT(*) clause:  you should use alternative way to count
  • Try to use constraints instead of triggers, whenever possible
  • Use table variables instead of temporary variables
  • Try to avoid the HAVING, DISTINCT clause, whenever possible
  • Try to use UNION ALL statement instead of UNION: because of UNION ALL does not look for duplicate rows, UNION does look for duplicate rows.