Graph in SQL Server on Linuxで100万件データを試してみる
SQL Server 2017ではGraph形式のデータが扱えるようになりました。Graphデータがどのようなものかは、前回の投稿でやってみましたが、データ件数がそれほど多くなかったので、少し多めのデータ(とりあえず100万件)でどうなるのか試してみたいと思います。
前回の記事は下記です。Microsoftのサンプルで一通り試してみたので参考にしてください。
環境
私がお試しでいろいろやっている環境は、Azure Virtual Machine上のRedhat Enterpraise Linuxに構築したSQL Server Linuxです。Azure Virtual Machineは、Standard DS2 v2 (2コア、7GBメモリ)です。SQL Server on Linuxの機能検証に最低限必要なリソースで構成しています。Redhat Enterpraise Linuxは、7.3です。これは、AzureでSQL Server on Linuxを構築する際に選択の余地がなかったので、こうなってます。
SQL Server on Linuxの構築方法も過去記事にあるので、良ければ参照ください。
テストデータ準備
Graphデータとして作成するのは友達関係を表すデータモデルです。Personデータ(Node)とfriendOfデータ(Edge)です。Microsoftのサンプルでも出ていたデータ定義を流用して使っていきます。大量のテストデータを準備しないといけないのでそこからやっていきます。テストデータって結構作成するの面倒だったりするのでいろいろ調べて参考にさせてもらいました。
とりあえず100万件のIDデータの作成をします。chiseiさん参考にさせてもらってます。
-- ベースの10件の作成 CREATE TABLE digit(num integer); INSERT INTO digit VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); GO CREATE TABLE digit2(num integer); --直積で大量データ生成 INSERT INTO digit2(num) SELECT d1.num + d2.num*10 + d3.num*100 + d4.num*1000 + d5.num*10000 + d6.num*100000 as num FROM digit d1, digit d2, digit d3, digit d4, digit d5, digit d6; GO
この基礎データをもとにPersonデータをINSERT~SELECTで作成します。Personテーブルは、Nodeになるので、AS NODEを忘れずに。
CREATE TABLE Person ( ID INTEGER PRIMARY KEY, name VARCHAR(100) ) AS NODE; GO --直積データから作成 INSERT INTO Person(ID, name) SELECT num, CONCAT('user',num) FROM digit2; GO
Personデータは比較的簡単にできました。friendOfデータは、だれとだれが友達かというデータなので少しだけ手間です。仮に今回は、ある人に対して5人の友達がいることにしましょう。100万人登録したので、friendOfデータは500万ということになります。500万レコード作成していきます。
まず、組み合わせをたくさん作っていきましょう。なんでもよいので中間テーブルを作成して100万件ずつ生成しています。INSERT~SELECT文は5回実行してください。ランダムな人を5回組み合わせています。
CREATE TABLE tempData(fromID integer,toID integer); GO --5回実行 INSERT INTO tempData(fromID,toID) SELECT num, ROW_NUMBER() OVER(ORDER BY NEWID()) AS toID FROM digit2 ORDER BY num; GO
500万レコードできたのでこれを元にfriendOfデータを作成していきます。Edgeデータは、from_nodeとto_nodeの2つの$node_idを指定しないといけないのでこのような面倒な方法になりました。5分ぐらいデータ投入に時間がかかります。
CREATE TABLE friendOf AS EDGE; GO INSERT INTO friendOf SELECT person1.$node_id, person2.$node_id FROM tempData INNER JOIN Person person1 on tempData.fromID = person1.ID INNER JOIN Person person2 on tempData.toID = person2.ID ORDER BY tempData.fromID; GO
これで、Person(Node)に100万件、friendOf(EDGE)に500万件できました。
検索してみる
さて、大量データができたので検索やってみよう。1人につき5人友達がいることになるので10人先(友達の友達の…)で100万以上を超えます(まぁ本来は重複があるのですが)。ですので、9人先までたどってみます。今回、5人しか友達いない設定で10回聞いて回ると100万人超えるって、現実世界で10回聞いて回ると考えるとすごいことになりそうですよね。
SELECT DISTINCT person2.name FROM Person person1, friendOf friendOf1, Person person2 WHERE MATCH( person1-(friendOf1)->person2 ) and person1.name = 'user100' ORDER BY person2.name; GO
これをどんどん拡張して行って、9階層までやってみます。途中割愛します。9回目は下記です。
SELECT DISTINCT person9.name FROM Person person1, friendOf friendOf1, Person person2, friendOf friendOf2, Person person3, friendOf friendOf3, Person person4, friendOf friendOf4, Person person5, friendOf friendOf5, Person person6, friendOf friendOf6, Person person7, friendOf friendOf7, Person person8, friendOf friendOf8, Person person9 WHERE MATCH( person1-(friendOf1)->person2 -(friendOf2)->person3 -(friendOf3)->person4 -(friendOf4)->person5 -(friendOf5)->person6 -(friendOf6)->person7 -(friendOf7)->person8 -(friendOf8)->person9 ) and person1.name = 'user100' ORDER BY person9.name; GO
性能取ってみました。ネットワークの問題とかで正確ではないかもしれないですが、3回計測して平均とりました。
階層 | 友達の数 | 検索時間 |
---|---|---|
2 | 5 | 00:00:00.788 |
3 | 25 | 00:00:01.592 |
4 | 125 | 00:00:01.812 |
5 | 625 | 00:00:02.872 |
6 | 3121 | 00:00:02.459 |
7 | 15518 | 00:00:03.562 |
8 | 75256 | 00:00:04.997 |
9 | 323990 | 00:00:09.857 |
とりあえず性能測ってみたものの、いいのか悪いのか正直まだ評価できないです。SQLだけ眺めると9テーブルをJOINしてって書くよりも、MATCHで書いた方が断然、直感的でわかりやすいですね。
今回は、Graphデータで遊んでみました。では。