Sunday, 1 September 2013

Sql Server - Performance Tips

In this article we are going to see the performance impact of some of the queries in SQL SERVER.

SELECT instead of SET:
   Always prefer to use SELECT keyword instead of SET , to assign a value for variable.Because in select we can assign a value for multiple variable at a time at a single execution. It takes a one value from a result,if it have more than one value in the result set.

Exists instead of Count():
  Always try to use the Exists keyword to check the existence of record, instead of Count(*).Count() needs a full record set to be retrieved

UnionAll instead of Union:
  Always try to use  UnionAll because Union use to return distinct values due to this some internal operations take more time.

Joins instead of SubQuery:
 Try to use Joins instead of subQuery, Because subQuery takes more time to retrieve the records in from table.

Order by :
  Try to avoid order by in Query ,  try to select the data in db and order the data in front end . it takes few second only.If you use order by in Queries. It takes more time to order and takes the data.

Functions :
  Try to avoid the usage of functions in where condition, It takes the more time to execute the function for each and every record.

Dynamic Query:
  Try to avoid the dynamic query, Because at the run time only query framed and executed so the cache is not possible for that query. 

 Try to use While loop instead of using Cursor , because cursor is executed in record by record in sequence.

More Joins :
  Avoid more joins in tables,because this gives very slow performance hit,so try to kept the data in optimized table structure.

Indexes :
  Try to create a indexes for the tables to retrieve the records very fast in SELECT, and Avoid the more non-clustered indexed column in Where clause.

I Hope From this article you can see some of the performance tips in SQL SERVER.

No comments:

Post a Comment