この記事は何に関するもので、誰に向けて書かれていますか?
ほとんどの人が SQL を使用しますが、経験豊富な開発者でさえ、単純な質問に答えられない場合があります。DBMS は最も一般的な INNER JOIN をどのように実行しますか?
一方、C# やその他の OOP 言語の開発者は、DBMS を単なるリポジトリと考えることがよくあります。また、SQL でいくつかのビジネス ルールを投稿することは悪いことです。それらとは対照的に、Linq2Db のようなライブラリが作成されます ( Linq2Sqlと混同しないで ください - 完全に異なる作成者と異なるライブラリ)。これを使用すると、すべてのコードが C # で記述され、型付き言語のすべての利点が得られます。しかし、これは形式的なものです。次に、このコードが SQL に変換され、DBMS 側で実行されます。
同じコードが SQL と C# でどのように機能するかをよりよく理解するために、最初と 2 番目に同じコードを実装して、その動作を分析します。Nested Loop、 Merge Join、 Hash Join が何であるかをよく知っている場合は 、記事を斜めに読むことはおそらく理にかなっています。しかし、わからない場合は、この記事が役立つはずです。
複数のコレクションの操作
車のメンテナンスのためのサービス センターのようなもの、つまりサービス ステーション (SRT) があるとします。 2 つのエンティティがあります 。Person - サービス センターの顧客と Visit - このセンターへの特定の訪問です。 個人に は、識別子に加えて、名、姓、活動ステータスが含まれています (たとえば、クライアントが別のブランドに車を変更した場合、彼は非アクティブ ステータスに移行し、近い将来に当社を訪問しません)。 訪問に は、識別子に加えて、クライアントへのリンク、訪問の日付、クライアントがこの訪問に対して支払った金額が含まれます。上記のすべては、最も単純なケースでは、次の C# クラスを使用してスタイル設定できます。
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, , . .. .
- , , - .