.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Post New Resource Bookmark and Share   

Sql Query Optimization techniques

Posted By:Deco       Posted Date: October 30, 2010    Points: 75    Category:    URL: http://www.dotnetspark.com  

This article lists some steps to be taken to optimize the Sql query performance.

  • Table should have primary key
  • Table should have minimum of one clustered index
  • Table should have appropriate amount of non-clustered index
  • Non-clustered index should be created on columns of table based on query which is running
  • Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
  • Do not to use Views or replace views with original source table
  • Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
  • Remove any adhoc queries and use Stored Procedure instead
  • Check if there is atleast 30% HHD is empty - it improves the performance a bit
  • If possible move the logic of UDF to SP as well
  • Remove * from SELECT and use columns which are only necessary in code
  • Remove any unnecessary joins from table
  • If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT . INTO or INSERT . INTO, etc)


Further Readings:

Author: Alex         Company URL: http://www.dotnetspark.com
Posted Date: November 09, 2010

Nice Article

Post Comment

You must Sign In To post reply

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend