Updated on 27th Dec, 21 8754 Views

SQL optimization techniques have always been a popular topic in database management. SQL database optimization techniques can be an extremely difficult task, in particular for large-scale data wherever a minute variation can result or impact drastically on the performance. Although SQL programming looks easy to learn and the commands used don’t point to an algorithm that retrieves data, its straightforwardness, however, takes it in a deceptive manner.

Check this SQL Full Course Tutorial:

In this SQL optimization techniques article, I will be sharing some of the best SQL optimization techniques with my readers. They are as follows:

  • The Execution plan over SQL Server DBA could be used in writing indexes. The main function of it is to graphically display data by retrieving from the SQL Server query optimizer.
  • To get back the execution plan (in SQL Server Management Studio), click on ‘Include Actual Execution Plan (Ctrl+M)’ prior to running the SQL query.
  • Thereon, a 3rd tab called ‘Execution Plan’ will pop up with detected missing index. Right click on the execution plan and There’s a ‘missing index details’ choosing that will resolve the issue.

Server Performance Tuning

Use CASE instead of UPDATE

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

Enrich your knowledge in What is PL SQL, various features, advantages, subprograms, PL SQL tutorial.

How to Optimize “Query” Checklist

  • Lots of problems occur when we put a lot of tables in SQL in a single join operation. The near fix is to do half the join with less number of tables and cache the output in a temporary table. Then carry on with the rest of the query on the temporary table.
  • Run UPDATE STATISTICS on below SQL tables
    • Many systems will run this for optimizing the weekly data on a scheduled basis.
  • Archive the deleted records or delete it from the below SQL tables
    • Doing this in scheduled mode once a week or a day will optimize Checklist.
  • Rebuild Indexes, tables
  • Dumping the SQL database(instant but will help fix corruption)
  • Running DBCC will also check possible corruption in the database

Want to learn more? Read this extensive SQL Tutorial!

Limit the size of the Working Data Set

  • Verify the tables used in the SELECT to check if any possible filters can be applied to WHERE statement. As during the span of time, the SQL query grows, the solution would be to look or specify the query to check only the limited or monthly data.

Learn about various Data Types in SQL such as numeric, character, and date and time with this SQL tutorial.

Removing Outer Joins

  • This depends on the capacity or influence a person has for changing the table content.
  • The possible solution is to remove OUTER JOINS by keeping placeholder rows in both tables. Example, the below table with OUTER JOIN defined to guarantee all info is getting.

Outer Joins SQL

  • Resolving this is to add a placeholder row into the table of customers and UPDATE all NULL values to the placeholder key in the sales table

Removing Outer Joins

  • Not only it removed the urge of OUTER JOIN but also made it a standard for salespeople with no customers.
  • This eliminates the need for developers to write the following statement ISNULL(customer_id, “Customers is zero”).

Read our blog on SQL for Data Science to help gain an understanding of databases and data management.

When to use temp tables

  • This problem is yet another quite difficult to go through. We can use a temp table in no. of areas, for as stopping from double-dipping to the big tables. This can also be used to drastically reduce the processing power mandatory to join big amounts of data.
  • When joining of data from one table to a large table, the performance hindering is reduced when the subset of big table data is joined after pulling out of it. This will also work when we have several queries to make similar joins at the same table.

Avoiding functions on the RHS of the operator

Consider the example:

WHERE YEAR(AccountModifiedOn) == 2016
AND MONTH(AccountModifiedOn) =7
  • By taking into account that AccountModifiedOn has an Index, the example where the query is changed in such a way that the index is not used again.

So after rewriting, it becomes

WHERE AccountModifiedOn between ‘7/1/2016’ AND ‘7/30/2016’ 
  • Thus the above query increases the performance extremely.

Learn more about SQL BETWEEN operators and how it works with examples.

SQL query becomes faster when we use actual column names in  SELECT statement instead of “*”

Writing query as

SELECT no., name, l_name, age, course FROM student_info;

Instead of

SELECT * FROM student_info;

Helps in performing faster queries

Check this SQL Tutorial for Beginners video:

HAVING clause is used as a filter just to filter rows after all the rows are selected.

For example, writing a query as

SELECT course, count(course)
FROM student_data
WHERE subject != ‘Social’
AND subject ! = ‘Science’
GROUP BY course;

Instead of

SELECT course, count(course)
FROM student_data
GROUP BY course
HAVING course! = ‘Social’ AND course!= ‘Science’

Helps in performing faster queries

Check out the PL/SQL Tutorial to learn more about PL SQL Collections and Records.

When we have more than 1 subquery in the main query.

By trying to minimize the number of subquery block in the main query, we can improve the performance.

For Example, Using this query

FROM employee_intellipaat
WHERE (salary, experience) = (SELECT MAX(salary), MAX(experience) FROM employee_data)
AND Dept = ‘Sandals’

Instead of

FROM employee_intellipaat
WHERE (salary, experience) = (SELECT MAX(salary), MAX (experience) FROM employee_data)
AND emp_dept = ‘Sandals’

Get enrolled in SQL Server Online Training Course and take your career to a whole new level!

Course Schedule

Name Date
SQL Training 2022-05-21 2022-05-22
(Sat-Sun) Weekend batch
View Details
SQL Training 2022-05-28 2022-05-29
(Sat-Sun) Weekend batch
View Details
SQL Training 2022-06-04 2022-06-05
(Sat-Sun) Weekend batch
View Details

Leave a Reply

Your email address will not be published. Required fields are marked *

Looking for 100% Salary Hike ?

Speak to our course Advisor Now !

Associated Courses

Subscribe to our newsletter

Signup for our weekly newsletter to get the latest news, updates and amazing offers delivered directly in your inbox.