SQL - PERFORMANCE TUNNING

1.SELECT fields instead of using SELECT *

Using SELECT * (read as “select all”) pulls all available data from a table. However, if a table has many fields and many rows, this taxes database resources by querying a lot of unnecessary data. Also, the execution time is very much.

SELECT fields are much cleaner approach and only pull the required columns and are more efficient.

2. Use WHERE instead of HAVING to define filters

As per the SQL order of execution, HAVING CLAUSE is executed after WHERE CLAUSE. Thus, we want to achieve filtration of data based on specific requirements we must USE clause instead of HAVING Clause.

3. AVOID SELECT DISTINCT

SELECT DISTINCT is an easy way to remove duplicates from a query. SELECT DISTINCT works by grouping all fields in the query to create distinct results. However, a large amount of processing power is required to execute such statements. Thus, we must try to avoid using SELECT DISTINCT, and select more fields to create unique results.

 4. USING LIMIT

Before running a query for the first time, we must limit our results to obtain only be desirable and meaningful records. This can be achieved by using a LIMIT statement. (In some DBMS systems, the word TOP is used interchangeably with LIMIT.) The LIMIT statement returns only the number of records specified. A LIMIT statement prevents taxing the database with a large query, only to find out the query needs editing or Modification.

5. AVOIDING MANY SUBQUERIES

We may have more than one sub queries in our main stored procedure. We must Try to minimize the number of subquery block in our query. This is fastening up the query execution.

Some Useful Tips

  • Usually IN has the slowest performance, thus IN is efficient whenever most of the filter criteria is in the sub-query. Whereas EXISTS is efficient when most of the filter criteria is in the main query.

  • We must use UNION ALL in place of UNION.

  • In order to store large binary objects, we need to first arrange them in the file system and add the file path in the database.

  • Using Case statements for SQL Verbs is also one of the efficient performance techniques

  • ALL SQL Verbs must begin on a new line.

  • Keep in mind the Right and left aligning of verbs within the initial SQL Verbs.