DEV Community

Kai
Kai

Posted on

Avoidance of excessive access in SQL

Nested Loops

The described iteration above can be compared with a simple programming Loop and the table with an array. Two tables with an two dimensional array and so on. In this case it is obvious to use some nested loops like for loops or while. This is a legitimate approach up to a certain point.

In the example above we have 100 thousands of records in one table. The nested loops for two of this tables cause a lot of accesses. The picture below shows these accesses.

Diagram

Field Dependencies

The picture above shows the exact match of a nested for loop of two SQL tables. These tables have at least one dependent field. So in this case the for loop go through every record in both tables. In detail:

for select id from tabA 
do 
begin
    for select id from tabB
    do 
    begin 
    // do something
    end 
end 
Enter fullscreen mode Exit fullscreen mode

First read a record of TabA and then all records of TabB. This is a load of accesses. in General "n²". This is a lot. Especially in SQL we have more ways to iterate through tables. The way of choice in this case is an "Inner Join". A Join matches two tables with at least on field each table. The Inner Join shows only the combined records where every condition is true.

SELECT * FROM employee
INNER JOIN departmend on employee.departmend_id = departmend.departmend_id;
Enter fullscreen mode Exit fullscreen mode

For example every employee who has an department will be shown in this combined result. This technique combined with some where conditions can reduce the records. This will also reduce the numbers of accesses, because there are no accesses on empty records

Top comments (0)