Question: How to Write Efficient Query?
Answer: There is no right or wrong answer to this question. Honestly, there is no definition of an efficient query as well. I often see this question in many interview sessions as well, I often get this question from many blog readers. Everyone wants to write an efficient query.
Let us quickly discuss what one can do so the query gives optimal performance.
- Do not retrieve additional rows or columns in the resultset
- Avoid using views with sequence object which blocks expanding views
- Check query statistics IO and try to keep it low (Watch the video)
- Check query statistics TIME and try to keep it low (Watch the video)
- Avoid using triggers on the table as they tend to slow down performance (Watch the video)
- Follow the best practices for the query parallelism
- Use ANSI standard T-SQL for better interoperability
- Avoid using SELECT * in the query (Watch the video)
- Use EXISTS over IN to get better performance
- Keep the transactions as short as possible
- Avoid using cursors
- Include SET NOCOUNT ON statement
You can also learn more about it in Performance Challenge – Write Efficient Query – SQL in Sixty Seconds #140.
Well, above are a few of the SQL Server Performance Tips which I can think of which I see often implemented at my client’s place. Well, that’s it for today. If you know any SQL Server Performance Tip which you want to add to the list, please leave a comment and I will update this blog post with the due credit to you.
On a separate note, today this blog post completes 14 years of daily blogging and this is the 5286th blog post. I am pretty happy that I am able to blog daily for 14 years. However, the time is changing and the direction of the blog is also going to change and I will write more about it tomorrow.
Reference: Pinal Dave (https://blog.sqlauthority.com)