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.
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
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;
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)