Query Optimization in SQL- Best Practices and Examples

Understanding Query Optimization

SQL statements are commonly used to retrieve data from databases. If you have experience working with databases, you have likely encountered slow-running queries. The slow response time can be due to system load or inefficiently written queries, which is the more common reason.

• Query optimization is an important skill for SQL developers and database administrators (DBAs).
• To improve the performance of SQL queries, developers and DBAs need to understand the query optimizer and the techniques it uses to select an access path and prepare a query execution plan.
• Query tuning involves knowledge of techniques such as cost-based and heuristic-based optimizers, plus the tools an SQL platform provides for explaining a query execution plan.
• The best way to tune performance is to try to write your queries in several different ways and compare their reads and execution plans. In this blog, I propose various techniques that you can use to try to optimize your database queries.

Tips for better Query Optimization :

1. Use ‘regexp_like’ to replace ‘LIKE’ clauses

Bad practice

good practice

2. Use ‘regexp_extract’ to replace ‘Case-when Like’

Bad practice :

good practice

3. Join and Subquery Optimization

Bad practice

select * from large_table l join small_table s on s.id=l.id

In this query, the large table large_table is processed first. This can lead to the database handling a significant amount of data upfront, which increases memory usage and slows down the overall query performance.

Good Practice

select * from small_table s join large_table l  on s.id=l.id

By starting with the smaller table small_table, the database initially processes a smaller result set. This allows for more efficient use of resources, as fewer rows are handled during the initial join, leading to faster query execution.

Additional Tip: When optimizing joins, also consider indexing the join columns. Indexing the columns used in the join condition can further improve performance, especially when combined with an optimized join order.

4. Use EXISTS Instead of IN for Subqueries for large datasets

the Exists clause stops searching the data whenever it finds a match. it makes queries faster when we are dealing with large datasets.

Example :

select first_name,last_name,mobile_number,amount from customer  
where  exists( select 1 from orders where order.customer_id=customer.id and 
order.amount > 1000 )

5. Minimize the usage of wildcard characters :

usage of wildcard characters such as ‘%’, and ‘_’ in SQL queries. these can slow down the querying process try to avoid these to get faster data.

Example :

select name,mobile_no,job,department from employee where name like 's%';

6. Use Column Names Instead of * :

• If you are selecting only a few columns from a table, it is important to note that there is no need to use SELECT * in your query.
• While using SELECT * might seem like an easier and quicker option to write, it can actually lead to increased execution time for the database when it processes your query.
• This is because SELECT * retrieves all columns from the specified table, which can significantly slow down the query’s performance, particularly if the table contains a large amount of data.
• By specifically selecting only the columns you truly need, you are not only reducing the overall size of the result table but also minimizing network traffic.
• This practice, in turn, contributes to enhancing the overall performance and efficiency of your query, allowing your database operations to be executed more swiftly and effectively.
• Therefore, it is always a best practice to limit your SELECT statements to those columns that are essential for your needs, ensuring optimal performance and resource usage.

Example :

select * from employee where employee_id=1234;

when you write *, without mentioning columns then the database will check all the rows which are present in that employee table. it leads to a table full scan and there will be a load on the database and traffic so it will take longer to get the data fetched.

Best Practice :

select employee_name,job_role,mobile_number from employee where employee_id=1234

7. Avoid using OR in join conditions :

Placing an ‘OR’ in the join condition will significantly slow down the query.

Example :

select p.product,c.cost from cost c inner join products p on c.product_ic=p.product_id
or c.type=p.product_type

Improved Query :

select p.product,c.cost from cost c inner join products p on c.product_ic=p.product_id

union all

select p.product,c.cost from cost c inner join products p on c.type=p.product_type

8. Try to Use UNION ALL in Place of UNION:

UNION ALL is faster than UNION because it does not perform duplicate checks. The UNION operator fetches unique records by removing duplicates, which adds an extra step for sorting and comparing the result sets. This additional processing can slow down the query.

On the other hand, UNION ALL simply combines the result sets from two queries without checking for duplicates, making it faster and more efficient, especially when you’re certain that there will be no duplicate records or you don’t need to eliminate them.

Example:

SELECT ename, deptno FROM emp
UNION ALL
SELECT ename, deptno FROM dept;

This query will combine results from both the emp and dept tables without checking for duplicates, improving performance compared to:

SELECT ename, deptno FROM emp
UNION
SELECT ename, deptno FROM dept;

In the case of the UNION the database engine will perform an extra step to remove duplicates, which could slow down the execution, especially with large datasets.

9. Remove unnecessary Mathematics Calculations :

remove the improperly written calculations in the query to improve the query performance because whenever the query runs automatically it will calculate all the necessary maths for every row and gives the data.

Example :

select employee_name,salary from employee where salary +10000<25000

Improved Query

select employee_name,salary from employee where salary <15000

10. Avoid SUB Queries in WHERE clause :

Subqueries Limit Index Use: Subqueries in the WHERE clause can prevent the optimizer from using indexes effectively, often leading to slower full table scans.

Higher I/O Costs: Subqueries, especially correlated ones, can increase disk I/O operations, slowing down performance, especially with large datasets.

Fragmented Execution Plans: Subqueries can break the execution plan into inefficient parts, reducing parallel processing and slowing the query. Joins allow smoother execution flow.

Example :

select sum(salary)  from employee 
where emp_id in (select empid from hr)

Improved Query:

with t as(select empid from hr)
select sum(salary) from employee e join t on e.empid=t.empid

11. Use Indexes for Faster Data Retrieval

Indexes allow Oracle to locate rows faster, reducing I/O operations. Choosing the right index type is essential for performance gains.

Example:

CREATE INDEX idx_emp_deptno ON emp(deptno);

In this case, indexing deptno on the emp table can speed up queries that frequently filter based on department.

Query Before Optimization:

SELECT * FROM emp WHERE deptno = 10;

Query After Optimization:

SELECT /*+ INDEX(emp idx_emp_deptno) */ * FROM emp WHERE deptno = 10;

Adding the index hint forces Oracle to use the index, reducing the query execution time.

12.Partitioning for Large Tables

Partitioning breaks a large table into smaller, manageable pieces without affecting query logic, making data retrieval faster.

Example:

Partition the emp table by deptno:

CREATE TABLE emp_part
PARTITION BY LIST (deptno)
(
  PARTITION p_sales VALUES (10),
  PARTITION p_hr VALUES (20),
  PARTITION p_it VALUES (30)
);

Conclusion

Query optimization is a key factor in ensuring efficient database performance. Whether you’re working with Oracle or PostgreSQL, using indexes, analyzing execution plans, and optimizing joins are fundamental steps. Regularly reviewing slow queries and making adjustments can dramatically improve the performance of your SQL queries.

By implementing these techniques, you can handle large datasets, reduce resource consumption, and improve query response times in both Oracle and PostgreSQL.

---

Latest Posts :

• Mastering Oracle SQL Joins: The Ultimate Guide to Data Retrieval and Performance
• Mastering Oracle Database Architecture: A Comprehensive Overview
• Mastering PostgreSQL Architecture: 6 Powerful Steps to Optimize Performance & Scalability
• Understanding Oracle Triggers – A Comprehensive Guide with Examples
• SQL Query Design Patterns: Writing Efficient Queries
• Master Oracle Interview Questions: Key Concepts & Examples for 2024
• Ace Your PL/SQL Interview Questions for Success! Part-4
• Forward and Backward Navigation in Oracle PL/SQL- Expert Examples
• Top 10 Common Oracle Errors and Proven Ways to Fix Them
• Query Optimization in SQL- Best Practices and Examples

Categories :

• Interview Questions (12)
• PLSQL (6)
• PostgreSQL (1)
• SQL (11)