September 29, 2020
index-optimization

SQL Queries Optimization Tips

Every time we writes lot of SQL queries. But most of the time we don’t consider its efficiency. Query optimization is the vital process for any database related applications. Its importance is high. It can makes any complex application simple. This article describes how we can optimize SQL query? Summary of the article:

  • What is SQL Query Optimization?
  • Some SQL Query Optimization Tips

What is SQL Query Optimization?
Query Optimization is the process to write the query in such a way that it execute quickly. It is very important for any standard application.

Some SQL Query Optimization Tips
All time we use lot of SQL queries in our application. But we don’t consider about its performance. If we follow some tips then our query will be more efficient. Some of them are.

  • Use views and stored procedures instead of heavy-duty queries.  It reduces network traffic
  • Try to use constraints instead of triggers, whenever possible.  Constraints are much more efficient than triggers and can boost performance
  • Use table variables instead of temporary tables
  • Try to use UNION ALL statement instead of UNION. The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist
  • Try to avoid using the DISTINCT clause
  • Try to avoid using SQL Server cursors, whenever possible. If required must use cursor standard way.
  • Try to avoid the HAVING clause
  • Include SET NOCOUNT ON statement into stored procedures. It shows the number of rows affected by a T-SQL statement. This can reduce network traffic, because your client will not want to see this
  • Try to return only the required column rather than all columns of a table
  • Don’t use unnecessary complex join
  • Don’t use more number of trigger
  • Use indexing in the table and follow its standard

Hope we can now optimize our SQL queries to get better result. Most of the time we use SQL with others programming language. So we should also consider their code writhing techniques and optimizations.

Rashedul Alam

I am a software engineer/architect, technology enthusiast, technology coach, blogger, travel photographer. I like to share my knowledge and technical stuff with others.

View all posts by Rashedul Alam →

7 thoughts on “SQL Queries Optimization Tips

    1. able variables are visible only to the connection that creates it and are deleted when the batch or stored procedure ends. A table variable is created in memory and its performance is higher than #temp tables. Because, it has less locking and logging. It can have a primary index.

Leave a Reply

Your email address will not be published. Required fields are marked *