😴 🧙🌈 ʕ•ᴥ•ʔ

The following is an explanation on how SQL Queries work and you can leverage their internals to optimize query performance.

https://dev.to/kanani_nirav/secret-to-optimizing-sql-queries-understand-the-sql-execution-order-28m1


In this article, we will learn how SQL queries are executed by the database engine and how we can use this knowledge to optimize our queries for better performance and accuracy. We will also learn about some common techniques and best practices for writing efficient and SARGABLE queries. 😎

What is SQL Execution Order? 🤔

SQL execution order is the actual sequence in which the database engine processes the different components of an SQL query. It is not the same as the order in which we write the query. By following a specific execution order, the database engine can minimize disk I/O, use indexes effectively, and avoid unnecessary operations. This results in faster query execution and lower resource consumption.

Let’s take an example of an SQL query and see how it is executed:

SELECT
customers.name,
COUNT(order_id) as Total_orders,
SUM(order_amount) as total_spent
FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE order_date >= '2023-01-01'
GROUP BY customers.name
HAVING total_spent >= 1000
ORDER BY customers.name
LIMIT 100;

Enter fullscreen mode Exit fullscreen mode

The execution order of this query is as follows:

  1. FROM Clause: The first step is to identify the tables involved in the query. In this case, they are customers and orders.

  2. JOIN Clause: The next step is to perform the join operation based on the join condition. In this case, it is customers.id = orders.customer_id, which connects the two tables by matching customer IDs.

  3. WHERE Clause: The third step is to apply the filter condition to the joined table. In this case, it is order_date >= ‘2023-01-01’, which selects only orders made on or after January 1, 2023. Now, it’s important to write a SARGABLE query to leverage indexes effectively, SARGABLE means Searched ARGUment ABLE and it refers to queries that can use indexes for faster execution. We Deep-Dive into SARGABLE Queries later in the article post.

  4. GROUP BY Clause: The fourth step is to group the rows by the specified columns. In this case, it is customers.name, which creates groups based on customer names.

  5. HAVING Clause: The fifth step is to filter the groups by a condition. In this case, it is total_spent >= 1000, which selects only groups with a total spent amount of 1000 or more.

  6. SELECT Clause: The sixth step is to select the columns and aggregate functions from each group. In this case, they are customers.name, COUNT(order_id) as Total_orders, and SUM(order_amount) as total_spent.

  7. ORDER BY Clause: The seventh step is to sort the rows by the specified columns. In this case, it is customers.name, which sorts the rows alphabetically by customer names.

  8. LIMIT Clause: The final step is to skip a number of rows from the sorted result set. In this case, it limits the result to a maximum of 100 rows.

SQL Execution Order

Image Source

Why SARGABLE Queries Matter? 🙌

SARGABLE stands for Searched ARGUment ABLE and it refers to queries that can use indexes for faster execution. Indexes are data structures that store a subset of columns from a table in a sorted order, allowing quick lookups and comparisons.

A query is SARGABLE if it uses operators and functions that can take advantage of indexes. For example, using equality (=), inequality (<>, !=), range (BETWEEN), or membership (IN) operators on indexed columns can make a query SARGABLE.

A query is not SARGABLE if it uses operators or functions that prevent index usage or require full table scans. For example, using negation (NOT), wildcard (LIKE), or arithmetic (+, -, *, /) operators on indexed columns can make a query not SARGABLE.

To write SARGABLE queries, we should follow some general guidelines:

Here are some examples of SARGABLE and non-SARGABLE queries:

Bad: SELECT ... WHERE Year(myDate) = 2022
Fixed: SELECT ... WHERE myDate >= '01-01-2022' AND myDate < '01-01-2023'

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm, OrderDate, GetDate ()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm, -30, GetDate())

Enter fullscreen mode Exit fullscreen mode

How to Tune Performance at Database Level?

Improving performance in the SQL execution order involves optimizing the steps followed by the database engine to process and execute SQL queries. Here are some ways to enhance performance in the SQL execution order:

Conclusion 🎉

In this article, we learned SQL execution order matters for query performance and database efficiency. We can enhance it with indexing, joins, filtering, SARGABLE queries, and best practices. This will increase our SQL speed and make our database systems high-performing.

#reads #kanani nirav #sql #performance