SQL Series | How to optimize the SQL queries

Enhancing Performance and Efficiency in SQL

Hey there, SQL enthusiasts! 👋

Have you ever wondered how you can optimize your queries to achieve lightning-fast results? Well, you’re in the right place! In this blog, we’ll explore some tips and tricks to optimize your SQL queries.

Let’s jump into the SQL world 🌏!

1. Analyzing Query Execution Plans

Execution plans provide insights into how the database engine processes and retrieves data. By examining these plans, you can identify potential bottlenecks, such as missing indexes or inefficient joins. Utilize tools like EXPLAIN or SQL Server Management Studio to visualize and analyze the execution plans, empowering you to make informed optimization decisions.

2. Use Column names

Instead of relying on the wildcard (*) to fetch all columns from a table, list the column names you require. This way, you only retrieve the necessary data and minimize the strain on your database and speed up the retrieval process.

❌ Not a best practice

SELECT * FROM employee;

✅ Best Practice

SELECT emp_id,first_name, last_name, dept_id, salary FROM employee;

3. Use filters when necessary

To narrow down your results effectively, ensure you use appropriate conditions and indexes. This enables the database engine to quickly locate and retrieve the desired data in lesser time.

❌ Not a best practice

--Query to view the employees for a specific department

SELECT emp_id, first_name .... FROM employee; 

--OR

SELECT * FROM employee;

✅ Best Practice

--Always good to specify the filter conditions if we have any

SELECT emp_id,first_name, last_name, dept_id, salary FROM employee 
WHERE dept_id = 10;

4. Limit the results

Fetching a large number of rows when you only need a handful of records can be a waste of resources. To fine-tune your queries, use LIMIT or TOP clauses to restrict the number of returned rows.

❌ Not a best practice

-- Avoid fetching all records if we want to view the sample 5 or 10 records

SELECT emp_id, first_name .... FROM employee; 

--OR

SELECT * FROM employee;

✅ Best Practice

-- Fetching only a few records will always be a good option for a sample view

SELECT TOP 10 * FROM employee;

--OR

SELECT * FROM employee LIMIT 10;

5. Create indexes

Indexes are like a guidebook for the database to quickly find and fetch data. They create a map of the table’s contents, making it easier for the database to search for specific information. By choosing the right columns to index, you can greatly improve the speed of your queries.

A well-designed index can do wonders in making your queries run faster.

6. Writing JOINS efficiently

Joining tables in SQL is important for combining data, but it’s essential to optimize your joins to avoid slow query execution. To do this, choose the right join types (like INNER JOIN, LEFT JOIN, or RIGHT JOIN) and make sure that the columns used for joining are indexed properly.

6. Avoid Unnecessary distinct conditions

DISTINCT keyword will help us in retrieving the duplicate records. However, it will unnecessarily consume more computing power to perform this operation. If we add the primary key and other columns to our query, it will naturally give us non-duplicated records.

❌ Not a best practice

-- Without a primary key

SELECT DISTINCT first_name, last_name, salary FROM employee;

✅ Best Practice

-- Adding primary and other columns produce more distinct records

SELECT emp_id, first_name, last_name, dept_id, salary FROM employee;

7. Avoid subquery if join is possible

Using joins instead of subqueries leads to more efficient execution and more effective computation.

❌ Not a best practice

SELECT * FROM products p WHERE p.prod_id = (SELECT s.prod_id
FROM sales s WHERE s.cust_id = 100 
AND s.quantity_sold = 1 );

✅ Best Practice

SELECT p.* FROM products p, sales s WHERE p.prod_id = s.prod_id 
AND s.cust_id = 100 AND s.quantity_sold = 1; 

8. Avoid using OR in JOIN conditions

If we use OR in our JOIN conditions, the query gets slowed down by a factor of 2.

❌ Not a best practice

SELECT * FROM costs c INNER JOIN products p 
ON c.unit_price = p.prod_min_price OR c.unit_price = p.prod_list_price; 

✅ Best Practice

SELECT * FROM costs c INNER JOIN products p ON c.unit_price = p.prod_min_price 
UNION ALL 
SELECT * FROM costs c INNER JOIN products p ON c.unit_price = p.prod_list_price;

9. Try to use UNION ALL in place of UNION

UNION ALL is faster than UNION as UNION ALL does not consider duplicates but UNION will look for duplicate records whether they exist or not.

❌ Not a best practice

SELECT customer_id FROM sales
UNION
SELECT customer_id FROM customers;

✅ Best Practice

SELECT customer_id FROM sales
UNION ALL
SELECT customer_id FROM customers;

10. Avoid any functions on the right-hand side of the operator

The query performance will increase greatly if we avoid using the functions on the right-hand side of the operator.

❌ Not a best practice

SELECT * FROM sales
WHERE EXTRACT (YEAR FROM TO_DATE (time_id, ‘DD-MON-YYYY’)) = 2021 
AND EXTRACT (MONTH FROM TO_DATE (time_id, ‘DD-MON-YYYY’)) = 2002;

✅ Best Practice

SELECT * FROM sales
WHERE TRUNC (time_id) BETWEEN TRUNC(TO_DATE(‘12/01/2001’, ’mm/dd/yyyy’)) 
AND TRUNC (TO_DATE (‘12/30/2001’,’mm/dd/yyyy’));

Thanks for joining me on this thrilling SQL journey! Until next time, happy learning and keep SQL-ing! 😊✨

Follow us on Medium and LinkedIn for more interesting information and great learnings!!!

Medium: CloudnLoud and Hema Yerramsetti

LinkedIn: CloudnLoud and Hema Yerramsetti