¿De qué trata este artículo y a quién va dirigido?
Casi todo el mundo trabaja con SQL, pero incluso los desarrolladores experimentados a veces no pueden responder una pregunta sencilla. ¿Cómo ejecuta el DBMS el INNER JOIN más común?
Por otro lado, los desarrolladores en C # u otros lenguajes de programación orientada a objetos a menudo piensan en un DBMS como un simple repositorio. Y publicar algunas reglas comerciales en SQL es malo. A diferencia de ellos, se crean bibliotecas como Linq2Db (que no debe confundirse con Linq2Sql , autores completamente diferentes y diferentes bibliotecas). Cuando lo usa, todo su código está escrito en C # y obtiene todos los beneficios de un lenguaje escrito. Pero esto es una formalidad. Luego, este código se traduce a SQL y se ejecuta en el lado del DBMS.
Para comprender mejor cómo funciona el mismo código en SQL y en C #, intentaremos implementar el mismo código en el primero y segundo, y luego analizaremos cómo funciona. Si sabe bien lo que son Nested Loop , Merge Join , Hash Join , probablemente tenga sentido que lea el artículo en diagonal. Pero si no lo sabe, el artículo debería serle útil.
Trabajando con múltiples colecciones
Supongamos que tenemos algún tipo de centro de servicio para el mantenimiento de automóviles: una estación de servicio (SRT). Hay dos entidades: Persona - clientes del centro de servicios y Visita - una visita específica a este centro. Persona, además del identificador, contiene el nombre, apellido y estado de actividad (por ejemplo, si un cliente cambia el coche por otra marca, pasa al estado inactivo y no nos visitará en un futuro próximo). Visita, además del identificador, contiene un enlace al cliente, la fecha de la visita y el monto que el cliente pagó por esta visita. Todo lo anterior se puede diseñar utilizando las siguientes clases de C # para el caso más simple:
internal sealed class Person
{
internal int Id { get; set; }
internal string FirstName { get; set; }
internal string LastName { get; set; }
internal bool IsActive { get; set; }
}
internal sealed class Visit
{
internal int Id { get; set; }
internal int PersonId { get; set; }
internal DateTime Date { get; set; }
internal decimal Spent { get; set; }
}
// ...
internal Person[] persons = new Person[];
internal Visit[] visits = new Visit[];
// ...
( PostgreSQL) :
create table public.visit
(
id integer,
person_id integer,
visit_datetime timestamp without time zone,
spent money
) tablespace pg_default;
create table public.person
(
id integer,
first_name character varying(100) COLLATE pg_catalog."default",
last_name character varying(100) COLLATE pg_catalog."default",
is_active boolean
) tablespace pg_default;
- - 2020 , , . ?
Nested Loop
, . . - .
public decimal NestedLoop()
{
decimal result = 0;
var upperLimit = new DateTime(2020, 12, 31);
foreach (var person in persons)
{
if (person.IsActive == false)
{
continue;
}
foreach (var visit in visits)
{
if (person.Id == visit.PersonId && visit.Date <= upperLimit)
{
result += visit.Spent;
}
}
}
return result;
}
:
, . O(N²), - , .
, SQL :
select setseed(0.777);
delete from public.person;
insert into public.person(id, first_name, last_name, is_active)
select row_number() over () as id,
substr(md5(random()::text), 1, 10) as first_name,
substr(md5(random()::text), 1, 10) as last_name,
((row_number() over ()) % 5 = 0) as is_active
from generate_series(1, 5000);/*<-- 5000 */
delete from public.visit;
insert into public.visit(id, person_id, visit_datetime, spent)
select row_number() over () as id,
(random()*5000)::integer as person_id, /*<-- 5000 */
DATE '2020-01-01' + (random() * 500)::integer as visit_datetime,
(random()*10000)::integer as spent
from generate_series(1, 10000); /* 10000 - */
CTO P 5000, V - 10000. , . . , . - . (P,V) (5000, 10000). : C# (Nested Loop) . . 20.040 , . 20.27 . 40 . SQL .
select sum(v.spent) from public.visit v
join public.person p on p.id = v.person_id
where v.visit_datetime <= '2020-12-31' and p.is_active = True
2.1 . . .. 10 , .
Merge Join
20 . Nested Loop - . … Merge Join Sort-Merge Join. , . . - . , - . , , , . O(N*log(N)).
- 1.4 C#. . 20 . , , . ? ! Hash Join .
Hash Join
. . , , Join. :
O(N). .NET Linq . (Grace hash join, Hybrid hash join) - . C# , 0.9 .
! , . . . Nested Loop - , Merge Join - ( ). Hash Join - .
- . -. (P, V) (50, 100). : Nested Loop - 2.202 , Merge Join - 4.715 , Hash Join - 7.638 . :
C# :
Method |
Nested Loop |
Merge Join |
Hash Join |
|---|---|---|---|
(10, 10) |
62.89 ns |
293.22 ns |
1092.98 ns |
(50, 100) |
2.168 us |
4.818 us |
7.342 us |
(100, 200) |
8.767 us |
10.909 us |
16.911 us |
(200, 500) |
38.77 us |
32.75 us |
40.75 us |
(300, 700) |
81.36 us |
52.54 us |
54.29 us |
(500, 1000) |
189.58 us |
87.10 us |
82.85 us |
(800, 2000) |
606.8 us |
173.4 us |
172.7 us |
(750, 5000) |
1410.6 us |
428.2 us |
397.9 us |
X1 X2 ? . . , 2020 ? C#. , , . . , . , . , Array List? , .. API. , - . … . . LinkedList? . .
Method |
Nested Loop |
Nested Loop with Linked List |
|---|---|---|
(10, 10) |
62.89 ns |
262.97 ns |
(50, 100) |
2.188 us |
8.160 us |
(100, 200) |
8.196 us |
32.738 us |
(200, 500) |
39.24 us |
150.92 us |
(300, 700) |
80.99 us |
312.71 us |
(500, 1000) |
196.3 us |
805.20 us |
(800, 2000) |
599.3 us |
2359.1 us |
(750, 5000) |
1485.0 us |
5750.0 us |
. :
, Nested Loop. Indexed Nested Loop Hash Join. ( ).
, . , . - .. - . , . PostgreSQL (page), (tuples). . , .
, :
, buffer pool buffer manager. . Nested Loop, Merge Join Hash Join. , . (Query Plan).
C#. (P, V) (50000, 100000). C# 145.13 . Nested Loop - 305.38 , Hash Join - 36.59 . :
set enable_hashjoin to 'off';-- Nested Loop
set enable_mergejoin to 'off';
set enable_material to 'off';
select sum(v.spent) from public.visit v
join public.person p on p.id = v.person_id
where v.visit_datetime <= '2020-12-31' and p.is_active = True
Nested Loop 11247.022 . :
, Nested Loop. :
set enable_hashjoin to 'on';
set enable_mergejoin to 'on';
set enable_material to 'on';
select sum(v.spent) from public.visit v
join public.person p on p.id = v.person_id
where v.visit_datetime <= '2020-12-31' and p.is_active = True
- , Hash Join:
, 25.806 , C# .
, , . . , , ..
JOIN. C# SQL , . ( , ). , .
, - . , . C# … , .. . Linq Join Hash Join, , . .. .
- , , - .