A Complete Guide on SQL Optimization Techniques

• 1.What is a Query Optimization in SQL?
• 2.SQL Query Optimization Techniques
• 3.SQL Query Optimization Tips
• 4.Conclusion

What is a Query Optimization in SQL?

• The process of selecting the most effective way to carry out the SQL statement is known as a query optimization.
• Due to the SQL’s non-procedural nature, an optimizer is permitted to merge, rearrange, and process data in the any sequence.
• Based on the statistics gathered regarding the accessed data, database optimizes every SQL statement.
• The optimizer evaluates different access techniques, such as full table scans or index scans, various are join techniques, such as nested loop joins and hash joins, various join orders, and potential transformations to identify best plan for SQL query.
• SQL Query Optimization Techniques

Use CASE instead of UPDATE

• Although using a UPDATE is a natural form that seems logical, many developers are overlook it and also it is simple to spot.
• For example, when inserting data into the temp table and if want to display a value where it is already exists. For example, if any of the customers with the more ratings needs to be “preferred”, then when it runs, it inserts a data into the table and runs an UPDATE and then column is set to be prefer.
• The drawback is that every time the UPDATE statement is done, it has to run two times, for each single write to the table.
• To solve this usage of inline CASE in a SQL query resolves by testing each row for the rating and state is set to be ‘Preferred’.
• Hence, performance is not be hindered.

Removing Outer Joins

• This depends on capacity or influence a person has for changing a table content.
• The possible solution is to remove the OUTER JOINS by keeping placeholder rows in the both tables.
• Example, below table with the OUTER JOIN explained to guarantee all info is getting.
• Resolving this is to add a placeholder row into table of customers and UPDATE all NULL values to placeholder key in sales table Not only it removed the urge of OUTER JOIN but also made it is standard for the salespeople with no customers.
• This removes the need for developers to write a following statement ISNULL(customer_id, “Customers is zero”).

Avoiding functions on a RHS of the operator

Consider example:

• SELECT *FROM Employee
• WHERE YEAR(AccountModifiedOn) == 2016
• AND MONTH(AccountModifiedOn) =7
• By taking into an account that AccountModifiedOn has Index, the example where the query is changed in such a way that index is not used again.
• So after rewriting, it becomes
• SELECT *FROM Employee
• The above query increases a performance extremely.

LIMIT Command

• This command can be used while controlling no. of rows to be displayed from a result set.
• Only required rows will be displayed by a result set. In order to give on-demand computation of rows for production purpose, one must use a LIMIT within production dataset.
• SELECT * FROM Users
• LIMIT 8;

Avoid Using a SELECT DISTINCT

• In SQL, the SELECT DISTINCT command is used to retrieve a distinct results and remove the duplicate rows.
• It basically joins together comparable rows in order to finish this task, then deletes them.
• The GROUP BY procedure is more costly.
• Therefore, one may add a extra properties to SELECT process to retrieve the various results and eliminate duplicate data.
• It is preferred to be include additional attributes in SELECT query to boost performance and reduce a time while fetching unique rows.

SQL Query Optimization Tips

Here are few examples of the most effective tips for a SQL queries.

Proper Indexing:

• An index is the type of data structure that accelerates a data retrieving from database table.
• When run a query in a SQL Server, the optimizer creates an execution plan.
• If it finds out that there is missing index that might be created to improve the performance, it will create a suggestion that will be displayed in a warning section.
• This suggestion will tell which columns should be indexed in a current SQL and how performance will enhanced after that.

Using SELECT

Using SELECT in place of a SELECT*:

• Data is retrieved from a database using SELECT.
• It is not advisable to extract all the data from large databases because doing so would need more resources to query such a big amount of data.
• The following query will retrieve all data from an Employees table causing computer to use lots of memory and processing.
• Less efficient method SELECT * FROM employees
• Alternatively, it is advised to specify a exact columns required from a data as shown below:
• More efficientSELECT first_name, last_name, city, state FROM an employees

Try to avoid repeating queries:

• When run queries in a loop it reduces an efficiency of the entire sequence.
• It is suggested that are use bulk insert and then update suiting the situation.

Avoiding correlated subqueries:

Correlated subquery runs row by row and affects overall performance of process.

Always gather less information and aim for a precise outcomes:

• The query will run more faster when the retrieved data is less.
• Always try to filter as much data as possible on a server instead of using too many filters on a client-side.

Avoid using HAVING, use WHERE instead:

• The major purpose of the HAVING clause is that it filters rows only after all rows are be selected.
• In a SQL WHERE statements are calculated before HAVING statements, making a WHERE query quicker.

Avoid too many JOINS:

• A query could become overloaded if join many tables to it.
• Additionally, a vast number of tables from which a data is to be retrieved may lead to a not-so-efficient execution method.
• The sequence in which tables are be joined, how and when filters are applied, and when to use the aggregation must be identified by a SQL query optimizer when creating plan.

Conclusion:

Covered many important tips and techniques to improve a SQL Query performance. Suggested to keep them in mind while writing the queries as they will improve performance and provide a great user experience of the applications.