Query Optimization

The most underrated but most important topic, which is a must while implementing the SQL Query, Stored Procedures, or Functions. While implementing any SQL operations knowing the syntax and structures is a good thing, but one must know optimization. Without knowledge of optimization, any developer can create DDL and DML statements, but they are not well-designed procedures. You know why? Because while executing those statements there are chances that it will take time or may create a deadlock situation. Proper joining is also considered to be part of optimization.

This above is an actual execution plan flowchart.

The most simple query execution flow is mentioned below:

1. From

2. Joins

3. Where

4. Group by

5. Having clause

6. Column list

7. Distinct

8. Order by

9. Top

1. From: First it will fetch all the records from the table mentioned after the ‘From’ keyword.

2. Join: joins are an essential part of any SQL statements. A developer must have proper knowledge of tables; otherwise, it can cause the wrong data population of data or extended execution time.

3. Where: It another filter applied to any query after applying joins. It is used to decrease no. of records provided filter wise.

4. Group by: It is used for grouping the records with aggregate functions or grouping the records particular provided column-wise.

5. Having: When we need to provide an aggregate function with a filter then we should use it in the ‘Having’ clause.

6. Column list: While putting ‘*’, we are calling all the columns and all records from a particular table. If not necessary then we must provide only those columns which are actually useful.

7. Distinct: It is used to remove duplicate records while fetching the details through a select statement.

8. Order by: This is useful to sort the data ascending or descending column-wise.

9. Top: It is used to limit the no. of records to be displayed on the screen.

Above mentioned query execution flow is 1^st step of the optimization ever keyword should be placed as per the above plan. The last 3 steps (7, 8, and 9) are most crucial because it will process all the records and then do operations accordingly.

‘Group by’ and ‘Having’ clauses are also taken time while execution because it uses aggregate functions in it. Along with that, it is necessary for you to know that if not necessary then don’t do for inbuilt functions. ‘Convert’ and ‘Cast’ is the most frequent built-in functions that can extend the execution because of conversion.

As mentioned above Joins are the most important part of any SQL statement because a good join increases the performance where wrong can mislead you.

First of all, while implementing a join please check whether tables are properly indexed or not. Indexing is very important while the creation of a table, a table must have at least one clustered index.
The lessor or no use of temp tables. Temp tables tend to increase the complexities of the query because it increases the continuous use of ‘tempdb’ database. If necessary then create a clustered index on that temp table which increases the performance and doesn’t wait for the temp table to be dropped automatically, drop it when it is of no use.

Go for the execution plan if the query is taking too much time, by seeing the plan we can easily fetch which query or portion of the query is taking the time. The execution plan shows which table used maximum process time from the overall time.

Make your indexes unique using integer or unique identifier which increases the performances. A table must have one clustered key and can have one or more nonclustered keys. Use small data types for indexing.
For the existence of any record don’t dependent on the count statement in the query.

For example, Always use ‘with (nolock)’ keyword to avoid locks while fetching records from the table. Avoid the use of ‘NOT IN’ statement in where condition, instead of that you can go for let join. In the same way no need to go for the ‘IN’ statement, you can simply use inner join.

Please avoid loops and cursors while creating any store procedure, because looping also causes CPU process usage and calling the same statement again and again. So avoid it is not needed. Use ‘UNION ALL’ instead of ‘UNION’ for combining two or more ‘select’ statements.