Tentang apa artikel ini dan ditujukan kepada siapa?
Hampir semua orang bekerja dengan SQL, tetapi bahkan pengembang berpengalaman terkadang tidak dapat menjawab pertanyaan sederhana. Bagaimana DBMS mengeksekusi INNER JOIN yang paling umum?
Di sisi lain, pengembang di C# atau bahasa OOP lainnya sering menganggap DBMS hanya sebagai repositori. Dan memposting beberapa aturan bisnis dalam SQL itu buruk. Berbeda dengan mereka, perpustakaan seperti Linq2Db dibuat (jangan dikelirukan dengan Linq2Sql - penulis yang sama sekali berbeda dan perpustakaan yang berbeda). Saat Anda menggunakannya, semua kode Anda ditulis dalam C # dan Anda mendapatkan semua manfaat dari bahasa yang diketik. Tapi ini formalitas. Kemudian kode ini diterjemahkan ke dalam SQL dan dieksekusi di sisi DBMS.
Untuk lebih memahami bagaimana kode yang sama bekerja di SQL dan di C #, kami akan mencoba menerapkan kode yang sama pada yang pertama dan kedua, dan kemudian kami akan menganalisis cara kerjanya. Jika Anda tahu betul apa itu Nested Loop , Merge Join , Hash Join - mungkin masuk akal jika Anda membaca artikel secara diagonal. Tetapi jika Anda tidak tahu, artikel ini akan bermanfaat bagi Anda.
Bekerja dengan banyak koleksi
Misalkan kita memiliki semacam pusat layanan untuk pemeliharaan mobil - stasiun layanan (SRT). Ada dua entitas: Orang - pelanggan pusat layanan dan Kunjungan - kunjungan khusus ke pusat ini. Orang, selain pengidentifikasi, berisi nama depan, nama belakang, dan status aktivitas (misalnya, jika klien mengganti mobil dengan merek lain, ia dipindahkan ke status tidak aktif dan tidak akan mengunjungi kami dalam waktu dekat). Kunjungan, selain pengenal, berisi tautan ke klien, tanggal kunjungan, dan jumlah yang dibayar klien untuk kunjungan ini. Semua hal di atas dapat ditata menggunakan kelas C # berikut untuk kasus paling sederhana:
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, , . .. .
- , , - .