The basic of the advanced SQL queries: Tuning

Leandro Ramos — Fri, 30 Dec 2022 00:03:00 +0000

In this article I wanna show you things about SQL that I believe will make a difference in your queries.

First of all, how does SQL work? This point can be obvious for some one, but I think that could be mentioned here. Keep in mind, a query SQL works in order: FROM, WHERE, SELECT.

Look the table teachers

id_teacher	id_school	situation
1	1	1
2	1	1
3	2	1
4	1	0

For example, in a query:

SELECT teachers.* 
FROM teachers 
WHERE teachers.id_school = 1

The database will search all teachers, then search which one has a column id_school equals 1, so after this put in columns from your select, in this case, all columns of table teachers.

Output:

id_teacher	id_school	situation
1	1	1
2	1	1
4	1	0

Well, this is pretty simple, you know? Let's make this more interesting… Think about, your employer tells you to make a query that returns all teachers who are actively working in some school.

Let's consider the table above to the teachers and the following table to the school:

id_school	situation
1	1
2	1
3	0

The first thing that comes to mind is something like this:

SELECT teachers.* 
FROM teachers 
JOIN school ON (school.id_school = teachers.id_school) 
WHERE teachers.situation = 1

Output:

id_teacher	id_school	situation
1	1	1
2	1	1
3	2	1

Well this works, but how do I make this better? We know that SQL queries work on clause FROM first, after this, it goes to JOIN clause. With this in mind, what is easier to the database, search all of the teachers and then look who have a school linked or search all of the schools and then look who are the teachers?

The logic tells us that the table school is less than the table teachers, so we can conclude that it is easier for the database to look for the school table. Like this:

 SELECT teachers.* 
 FROM school 
 JOIN teachers ON (teachers.id_school = school.id_school)
 WHERE teachers.situation = 1

Output:

id_teacher	id_school	situation
1	1	1
2	1	1
3	2	1

The same result, but with better performance. This is one of the most important lessons that I learned about SQL.

Okay, we make a little deep into tuning SQL queries, come one continue talking about. The second point that I want to talk about are subqueries. In most cases you DON'T need this!

Subqueries are a vampires of SQL query, they are common used when you want put values from other table per line, like when you want to put in line the sum values of items from a order with you SELECT.

Consider the follow tables to the order and order_itens:

Order:

id	costumer
1	1
2	1

Order Itens:

id	id_order	value
1	1	93.3
2	1	12.99
3	2	10.19
4	2	12.5
5	2	79.99
6	3	99.13

For example:

SELECT order.*, 
      (SELECT SUM(order_itens.vl_item) 
       FROM order_itens 
       WHERE order_itens.id_order = order.id) AS vl_total_order 
FROM order

Output:

id	costumer	vl_total_order
1	1	106.29
2	1	102.68

This query returns what we want, all of the orders and the value of all items from the order. Okay, but how do I make this better?

The first thing that I suggest you do is detach this query, if this is possible, is the better option in my opinion. But if not, in SQL you have another option to do this exemple, we can make the query above with the WITH function.

From now i don't will talk more about this function, but maybe I can make another document just to talk about this. In fact if you don't know how to make a WITH correct, they can be more hindrance than a help, so be careful.

Ok, but sometimes we can run away from subqueries, for example you have to check if the line result exist on another table. Something like:

SELECT order_itens.* 
FROM order_itens 
WHERE order_itens.id_order IN (SELECT order.id 
                               FROM order)

Output:

id	id_order	value
1	1	93.3
2	1	12.99
3	2	10.19
4	2	12.5
5	2	79.99

Of course, the better way that we can do this, is with JOIN. But if you table don't have a PK and sequences in well defined, or something else, maybe with this will get more performance:

SELECT order_itens.* 
FROM order_itens 
WHERE EXISTS (SELECT 1 
              FROM order 
              WHERE order.id = order_itens.id)

Think about this, in this example we only need to know if line exists in another table, so just take the first one you find and done! This it may seem a small thing, but when we talk about tuning, we have a lot of small things that make a difference together.

That’s it! Thank you for reading, I will hopefully than this tips help you in your journey as a developer SQL. See you later!

DEV Community: Leandro Ramos

The basic of the advanced SQL queries: Tuning