In this article, we want to discuss about the efficient way to Optimize SQL Queries in SQL Server. Before we get started, if you want to know about how to use cursor in sql, please go through the following article: Using Cursor in SQL.

Why optimize a query?

Many of us are feel the query that we wrote is final and once it works and provides the result then that query is the best. Actually the Query optimization is not only the technique to make your query to fetch details or to execute many CRUD operations but also they are the main scope to make the end user continue with your application.

It’s the same situation when we access some websites, and fetching of data pulls most of your time, then you may prefer to use other websites which get you all results instantly. Optimization is not only to create impact technically, but also in a business point of view has a very large impact.

How to Optimize SQL Queries?

Hope now you feel that optimizing a query is better and also there are a few things that you have to consider before writing a query or before deploying the same.

Step 1: Avoid using “*” in select query

We have the habit of always writing a query using “*” in select operations

Where the select will fetch all the columns of the table, whenever there is a need for the column then use it, don’t fetch all the columns of the table. Always use column specific names.

Step 2: Avoid using Copy Paste of Code randomly

We have the habit of copying the code from existing code or from online forums whenever required, while copying please using only the code that exactly applies to the logic, reusing the similar code or copying unwanted lines which may not create impact to your query but may take time for execution, so during code re usability always use only code that exactly required to fetch results for you.

Step 3: Avoid using functions in Where Clause

When we have to execute any query there will be a conditional check using the where clause for filtering of data.

When we use any of the system defined or user defined functions in the where clause, then each time; i.e., each of the rows in the table will be checked against the function results which in turn execute the function each time. Obviously this will take more time and we all know the parameter less function will always return the same values. Better assign the function value to a variable and then use it in a where clause.

Step 4: Avoid using Joins between two types of columns

As all of you know the Type Conversion during the data manipulation is another place where a large amount of time will be consumed, when we are trying to join two types of columns then the other column has to be converted, the values of the lower column has to be converted and this will take some time for converting for each of the row values.

Step 5: Avoid using COUNT(*) from tables for getting row count

We usually use the COUNT(*) to get the number of rows available in a table.
Example:

Example:

Note: This query will not do full scan of table rows, where if you need to check for any value for that condition available then this query may be useful, not always provide the exact count of rows.

Step 6: Avoid using DISTINCT when Join Tables

When we use the Distinct keyword it looks as if the query will be automatically optimized by the internal SQL engine, that is true when you use the Distinct for filtering operation when we use the distinct during the table join operations which has one to many relations then it’s not advisable.
Example:

When we use the “Exists” query for fetching some operations during table join then it’s better comparing it to the Distinct compares.
Example:

Step 7: Avoid Using Temp Tables

Using the temporary table is always an easy functionality for accessing the data values, but we should be careful about the right scenario to use the temp tables. When more than one table are joined and some conditional operations are compared and executed it’s not suggested to store the values in temp table and access the large quantum of values.

It’s always suggested to use the “View”, where the View has more benefits that the table operations are executed once during the query can be fetched during execution and can be used optimized.Note: The temporary table may occupy the internal memory and that make the process execution slowness.

Step 8: Avoid Using Triggers

Trigger usages are the expensive process in the SQL, so try to avoid firing and executing the triggers. Don’t use the triggers with any constraints and also avoid using the same triggers for multiple CRUD operations.

Step 9: Avoid Dead locks during query executions

The deadlock handling is not an easy job for the larger transnational data volumes, when we handle quite large data processing with multiple processes then we should be clear on dead lock occurrence scenarios and try to avoid it.When you write any queries, views, functions and store procedures always follow the same order of access of the tables.

Always try to break the operations in transactions as small blocks for easy understanding in troubleshooting. Larger transaction blocks may create deadlocks or dependency and consume more operational and execution times.

Step 10: Avoid Using Locks during read process

Lock of the tables may affect the other process to get impact; there is an alternative query to use “WITH (NOLOCK)” which is equivalent to “READ UNCOMMITED” at transaction isolation.This is also called a dirty read but this operation of using “With (NoLock)” can prevent the deadlock occurrences by multiple reads. Example:

Hope this will help you to make your SQL optimized and helps to reduce the execution time to some extents. Thank you.

How To Optimize SQL Queries

Leave a Reply