Sql Query Optimization

Hi Friends,

I am going to cover how we can optimize the SQL QUERY.
The following steps helps in query optimization:-

• Try to use name of columns instead of ‘*’ in select query.
• Make sure that you create clustered index on your table i.e., a table being queried should have primary key in it. This makes searching faster.
• Columns being used after 'where' section or joins in SQL should have index on it. For e.g., if any non-primary key or column of your table is being used after where clause or joins should have non- clustered index on it.
• Make use of sub query rarely. Try to build query using joins than a sub query.
• Avoid Cursors as they take a lot of execution time.
• Use Group By instead of DISTINCT clause.
• Limit number of rows using TOP keyword according to your requirement.
• Create compound index if using multiple columns after Where or Order By clause.
• Use Exists rather than IN keyword
• Use UNION than UNION ALL to avoid duplicate records.
• Set No Count On in a stored procedure to swift the search.
• Make use of Execution Plan to investigate which table takes more time to execute and many more information that can help you to find out the slipup.
• Use Tuning Advisor Performance tool to investigate for you what went wrong? And apply the recommendations to sort it out.

The above mentioned steps would definitely help most of you to optimize query. For any queries related to SQL or .NET .You can send me your mails on priyanka.chandok@gmail.com. I will try to reach you with a reply in form of blog asap.

Regards,

Priyanka Chandok

Software Engineer

HeadStrong