How to Write Efficient Query? – Interview Question of the Week #300

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.

You can also reach out to me on Twitter, LinkedIn, Facebook, and also on YouTube.

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.