Advanced SQL Queries to Optimize Database Performance
Understanding the basics of SQL is a good first step, but learning to write higher-performance SQL is important for improving your overall skill set. Over time, as your datasets grow larger and applications return the results, mastering additional techniques for building queries quickly becomes a necessity. Simple SELECT statements and even basic WHERE clauses aren’t enough to get the job done. Your interactions with the database will be much less frustrating as you learn to utilize some of the more advanced functionalities of the SQL programming language.
Studying advanced SQL queries and the structures that can be employed to significantly improve performance is the focus of this guide. Specifically, we will be concentrating on Common Table Expressions, windowing functions, and a few other techniques that can help you optimize your code to help improve the efficiency of the computing resources and time you use while improving the performance of your queries to get more maintainable code.
Progressive Learning from Advanced SQL to Other Constructs
When you learn to build complex structures of your data more logic, and processing instructions within the database engine, you will learn to build systems that capture data to be processed externally more effectively, and to avoid bottlenecks.
1. Common Table Expressions for Simplicity
Add CTE logic to your simpler queries to give a temporary and named results set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement to help improve the efficiency of your systems. CTEs help make your queries more readable and logical.
You can think of a CTE as a temporary view that improves readability without having permanent views clutter your database schema. CTEs exist for the duration of a single query.
When Do You Use This Process?
- You use this process when needing to reference a derived table multiple times in a single query.
- You also use this process to handle recursive queries. An example of a recursive query would be traversing through an organizational hierarchy.
- Additionally, this process can be used to clarify the steps of complex calculations or joins by breaking them down incrementally.
Example: Finding the Highest Selling Sales Representatives
Suppose you’re tasked with finding the highest-selling sales representatives for each region, specifically the top two sales representatives. Without a CTE, this query can, and will, become a complicated mess of multiple different sub-queries. However, when you’re able to utilize a CTE, the purpose and the logic become a lot clearer.
We will use the sales by each representative as a CTE to be able to derive the total sales a representative has earned and the total sales a representative has earned. Then, the sales representatives will be ranked by sales and by region.
WITH SalesByRep AS (
SELECT
RepName,
Region,
SUM(SaleAmount) AS TotalSales,
ROW_NUMBER() OVER(PARTITION BY Region ORDER BY SUM(SaleAmount) DESC) AS SalesRank
FROM
Sales
GROUP BY
RepName,
Region
)
SELECT
RepName,
Region,
TotalSales
FROM
SalesByRep
WHERE
SalesRank <= 2;
This example here first calculates the TotalSales using the CTE above, which also assigns a SalesRank with a window function. The final SELECT statement simply filters the top 2 performers from each region from these temporary result sets. The above method is cleaner as compared to a nested subquery.
2. Carrying out complex calculations using window functions
A window function works across a set of table rows that are somehow related to the current row. Unlike an aggregate function, for example SUM, that groups rows to form a single output row, a window function returns a value for each row based from a window of related rows.
This is great for coming up with running totals, moving averages, ranking of results to name a few, and this is achieved without complex self joins or cursors.
Key window functions:
Ranking: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
Aggregate: SUM(), AVG(), COUNT(), MAX(), MIN() with an OVER() clause.
Values: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()Example: Calculating a 7-Day Moving Average for Sales
There are often fluctuations in sales data which can make the data more difficult to analyze. A moving average can help to smooth out the data and highlight the longer trends. If you don’t use window functions, calculating databases in SQL will be difficult and inefficient.
SELECT
SaleDate,
DailySales,
AVG(DailySales) OVER (
ORDER BY SaleDate
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS '7DayMovingAverage'
FROM
DailySalesSummary;The OVER() clause defines the window. Here, it tells the AVG() function to calculate the average sales over a window of seven rows: the current row and the six preceding it, ordered by date. This calculation is performed for every row, providing a rolling average efficiently.
3. Query Optimization Beyond the Syntax**
Writing advanced queries is only part of the battle. You also need to ensure the database engine can execute them efficiently.
Using EXISTS Instead of IN
When checking for the existence of a value in a subquery, EXISTS is often more performant than IN. The IN operator requires the subquery to collect all matching results before the outer query can proceed.
Unlike the IN clause, which performs poorly, the condition of the EXISTS clause is satisfied as soon as one row is found, and then halts any further processing. This behavior is also much more efficient, especially as the result of the subquery gets larger.
Less Performant with IN:
SELECT
c.CustomerName
FROM
Customers c
WHERE
c.CustomerID IN (SELECT o.CustomerID FROM Orders o WHERE o.OrderDate >= '2025-11-01');More Performant with EXISTS:
SELECT
c.CustomerName
FROM
Customers c
WHERE
EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID AND o.OrderDate >= '2025-11-01');
While the optimizer will sometimes make the IN clause more performant as if it were an EXISTS subquery, it is still more accurate to structure the query as such.
The Power of UNION ALL vs. UNION
The distinctions between UNION and UNION ALL should also be made clear. Both commands allow the results of two or more queries to be combined.
- UNION removes duplicates from the result set, which incurs extra computational cost.
- UNION ALL leaves duplicates present in the final result set.
Use `UNION ALL` if you know that the combined result sets will not contain duplicates or if duplicates are allowed. It skips the unnecessary duplicate removal procedure, which will result in a significantly faster query execution.
Constructing Your Path to SQL Proficiency
Learning to use advanced SQL constructs like CTEs and window functions can change the way you interact with your database. With these techniques, you can solve sophisticated problems with beautiful and readable high-performing queries. By moving logic from your application layer into the database, you fully utilize the database engine and process the data efficiently at the source.
Combine these query writing skills with a good understanding of optimization strategies like using `EXISTS` and `UNION ALL` in the appropriate way. This will help you to write SQL that is not only powerful but also practical to the real-world application needs.
