Query Optimization

Query Optimization

The most underrated but most important topic, which is must while implementing the SQL Query, Store Procedures or Functions. While implementing any SQL operations knowing the syntax and structures is 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 deadlock situation. Proper joining is also considered to be part of optimization.

This below is actual execution plan flowchart.

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 the essential part of any SQL statements. A 
          developer must have proper knowledge of tables; otherwise it 
          can cause 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 aggregate function with 
          filter then we should use it in ‘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 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 above plan. 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 takes 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’ an ‘Cast’ are most frequent built in functions which can extend the execution because of conversion. As mentioned above Joins are the most important part of any SQL statement because a good join increase the performance where a 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 creation of table, a table must have at least one clustered 
     index.

            Less or no use of temp tables. Temp tables are 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 don’t wait for temp table to be dropped
     automatically, drop it when it is of no use.

            Go for the execution plan if query is taking too much time, by 
     seeing the plan we can easily fetch which query or portion of the 
     query is taking time. Execution plan shows which table used 
     maximum process time from 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 non-clustered keys. Use 
     small data types for indexing. 

For the existence of any record don’t dependent on counts 
     statement in query. For  example,

            

           

Always use ‘with (nolock)’ keyword to avoid locks while fetching 
    records from the table. Avoid use of ‘NOT IN’ statement in where 
    condition, instead of that you can go for let join. Same way no 
    need to go for ‘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 same statement again and again. So avoid it is 
    not needed. Use ‘UNION LL’ instead of ‘UNION’ for combining 
    two or more ‘select’ statements.