Query optimization is a technique for obtaining required data in a very efficient manner when we deal with databases which are having a very large amount of data and very complex data. And also there is a scenario i.e. in general queries are tested with a database, which is having very fewer data at the time of development of software and development goes on the local system, so query response is very quick. But when software or project goes live then queries start to face a very large amount of data and very complex data. So, before writing the query, there is a need to ensure that the query syntax should be such that it could efficiently fetch the required data. So, there are several ways to optimize or improve the performance of queries and there are as below:

[1] Define the Requirement before writing the query:

Try to define the exact requirement, before writing the query.

[2] Use Fields Name in Query instead of SELECT * table_name

Inefficient:

Efficient: because will fetch only specified column

[3] Use the WHERE clause instead of the HAVING clause

Inefficient: Because the HAVING clause filters the rows after selecting all the rows. So, It should be used only for filtering the values based on a condition not anywhere else, It is just like a filter only.

Efficient: In SQL operating order, HAVING statements are determined after the WHERE statement. So, a Query with a WHERE clause executes faster than a query with a HAVING clause.

[4] Use JOIN rather than SUBQUERY

Inefficient: Because for the execution of every row of the customer table, an inner query or subquery will execute first then the outer query will execute. So, It will reduce the performance. JOIN is used for such cases.

Efficient:

[5] Avoid DISTINCT from the SELECT query

Inefficient: Using DISTINCT to remove duplicates is an inefficient way because it works by creating groups in the query. However, It consumes lots of power in computation. So, performance can be improved by increasing the number of fields.

Efficient:

[6] Check Whether my record Exist Or Not

Inefficient: EXITS() or COUNT()  both are used for matching a record entry. However, COUNT() is not efficient just because it scans the whole table even if the targeted thing is found in the first row. And EXITS() works such as execution will exit as soon as the targeted thing is found.

IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE ‘%JOHN%’) > 0 PRINT ‘YES’

Efficient:

IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE ‘%JOHN%’)PRINT ‘YES’

 

[7] Wisely Use wildcard characters

Inefficient:

This finds data that is not required data other than required data. As It finds “Ramesh”, “Ram”, “And Raghav” which is required but it also finds “Kiran”, “Viral” which was not required.

Efficient:

But this finds only required data as “Ramesh”, “Ram”, and “Raghav”.

[8] Whenever Possible, Use UNION ALL instead of UNION

Inefficient: UNION performs a DISTINCT on the result set, eliminating any duplicate rows

Efficient: UNION ALL does not remove duplicates, and it is therefore faster than UNION.

Conclusion: By the above description of queries’ execution. It can be concluded that the optimization operation is a necessary operation that should be taken care of by the software developers during the writing of SQL Queries for the development of the software.