How to Optimize SQL Query Performance

This morning, I stumbled on SQL queries written by me only, and learnt something interesting??

Performance tuning is a critical data management skill and even minor query modifications can lead to significant execution time differences. Recently, I ran two SQL queries on customer order data (from #OracleLive) that produced the same results but had significantly different execution times. Here’s what I found and why it matters when optimizing queries.

Query 1: Using an Inner Join with a Subquery

SELECT tbl.Order_date, O.Customer_ID, O.Store_ID, Count(O.ORDER_ID) as Order_Count
FROM CO.ORDERS O
INNER JOIN
(
    SELECT ORDER_ID, TO_CHAR(ORDER_TMS, 'YYYY-MM-DD') AS Order_date
    FROM CO.ORDERS
) tbl
ON O.ORDER_ID = tbl.ORDER_ID
WHERE 1=1
AND O.Customer_ID = 283
AND O.STORE_ID = 1
GROUP BY tbl.Order_date, O.Customer_ID, O.Store_ID
HAVING Count(O.ORDER_ID) > 1
ORDER BY Order_date;        

Query 2: Using a Subquery with GROUP BY

SELECT Order_date, STORE_ID, CUSTOMER_ID, COUNT(order_id) as Order_Count
FROM (
    SELECT CUSTOMER_ID, STORE_ID, order_id, TO_CHAR(ORDER_TMS, 'YYYY-MM-DD') AS Order_date
    FROM CO.ORDERS
) tbl
WHERE 1=1
AND STORE_ID = 1
AND CUSTOMER_ID = 283
GROUP BY Order_Date, STORE_ID, CUSTOMER_ID
HAVING COUNT(order_id) > 1
ORDER BY Order_date;        

?? The Queries & Execution Time

?? Query 1 Execution Time: 0.001 sec

?? Query 2 Execution Time: 0.006 sec

?? Both queries count orders by date for a specific customer and store. But why does Query 1 outperform Query 2?

Why is Query 1 Faster?

?? Join Placement:

• Query 1 performs an INNER JOIN on a subquery that extracts the Order_date.
• Query 2 applies a subquery first, and then filters it in the outer query.
• Query 1 benefits from indexed joins, while Query 2 forces a table scan on the derived table.

?? Query Execution Plan:

• Query 1’s ON condition is indexed, leading to efficient lookups.
• Query 2 materializes the derived table, consuming more memory and processing time.

?? Order By Impact:

• Query 2 includes ORDER BY Order_date, which adds an extra sorting operation.
• Query 1 avoids unnecessary sorting, keeping it lightweight.

Key Takeaways for Writing Efficient SQL

? Use Joins Wisely – Joining on indexed columns speeds up performance.

? Avoid Unnecessary Subqueries – Materialized subqueries can increase execution time.

? Minimize Sorting – Only use ORDER BY when necessary.

? Leverage Execution Plans – Analyzing query plans helps identify bottlenecks.

SQL performance optimization isn't just about correctness—it’s about efficiency.

#SQL #DataAnalysis #PerformanceTuning #SQLQueryOptimization