0
405

投稿者

Toshihiko Minamoto
インターシステムズジャパン
STAFF
フォロー
記事 Toshihiko Minamoto · 4月 19, 2021 8m read

配列プロパティ要素のための SQL インデックス

#Caché #ObjectScript #SQL #インデックス付け #パフォーマンス

クラスの中で配列プロパティを使い、その要素 (キーと値の両方) によってスピーディに検索を実行できると非常に便利な場合があります (EAV モデルの場合は特に重宝します)。

それでは、簡単な例を見てみましょう。

<FONT COLOR="#000080">Class User.eav Extends %Persistent
</FONT><FONT COLOR="#000000">{

</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx1 On attributes(ELEMENTS) [ </FONT><FONT COLOR="#000080">Data </FONT><FONT COLOR="#000000">= entity ];
</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx2 On (attributes(KEYS), attributes(ELEMENTS)) [ </FONT><FONT COLOR="#000080">Data </FONT><FONT COLOR="#000000">= entity ];
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">entity;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">attributes </FONT><FONT COLOR="#000080">As array Of %String</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">SQLTABLENAME </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800080">"attributes"</FONT><FONT COLOR="#000000">) [ </FONT><FONT COLOR="#000080">SqlFieldName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">attr </FONT><FONT COLOR="#000000">];
</FONT><FONT COLOR="#000080">/// d ##class(User.eav).RepopulateAll()
ClassMethod </FONT><FONT COLOR="#000000">RepopulateAll()
{
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%DeleteExtent</FONT><FONT COLOR="#000000">()
  
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">name</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$TR</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"Sibe^rian pi^ne ce^dar"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"^"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#0000ff">$c</FONT><FONT COLOR="#000000">(769))
  
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">=..</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">entity</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"Human"
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(22,</FONT><FONT COLOR="#008000">"Age"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(186,</FONT><FONT COLOR="#008000">"Height"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"Jack"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"Name"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Save</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">=..</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">entity</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"Tree"
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(186,</FONT><FONT COLOR="#008000">"Age"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(22,</FONT><FONT COLOR="#008000">"Height"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"Pines"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"Family"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">name</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"Name"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Save</FONT><FONT COLOR="#000000">()
  
  </FONT><FONT COLOR="#008000">/*
  
  ;or
  
  &sql(insert into eav(entity) select 'Human' union select 'Tree')
  &sql(insert into attributes(eav,element_key,attr)
  select 1,'Age',22 union
  select 1,'Height',186 union
  select 1,'Name','Jack' union
  select 2,'Age',186 union
  select 2,'Height',22 union
  select 2,'Family','Pines' union
  select 2,'Name',:name)
  */
  
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">Reindex</FONT><FONT COLOR="#000000">()
}
</FONT><FONT COLOR="#000080">/// d ##class(User.eav).Reindex()
ClassMethod </FONT><FONT COLOR="#000000">Reindex()
{
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%BuildIndices</FONT><FONT COLOR="#000000">(,1)
  </FONT><FONT COLOR="#0000ff">d $system</FONT><FONT COLOR="#008080">.SQL</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">TuneTable</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"SQLUser.eav"</FONT><FONT COLOR="#000000">,1)
  </FONT><FONT COLOR="#0000ff">d $system</FONT><FONT COLOR="#008080">.SQL</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">TuneTable</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"SQLUser.attributes"</FONT><FONT COLOR="#000000">,1)
  </FONT><FONT COLOR="#0000ff">d $system</FONT><FONT COLOR="#008080">.OBJ</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Compile</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">(),</FONT><FONT COLOR="#008000">"cu/multicompile=1"</FONT><FONT COLOR="#000000">)
}
}</FONT>
データを読み込んだ後に、以下を実行します。
USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">User.eav</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">RepopulateAll</FONT><FONT COLOR="#000000">()</FONT>
すると、テーブルに以下のデータが表示されます。
IDentity
eav テーブル
1Human
2Tree
eavIDattrelement_key
attributes テーブル
11||Age22Age
11||NameJackName
11||Height186Height
22||Age186Age
22||Height22Height
22||NameSibérian píne cédarName
22||FamilyPinesFamily
データを持つグローバル
USER>zw ^User.eavD
^User.eavD=2
^User.eavD(1)=$lb("","Human")
^User.eavD(1,"attributes","Age")=22
^User.eavD(1,"attributes","Height")=186
^User.eavD(1,"attributes","Name")="Jack"
^User.eavD(2)=$lb("","Tree")
^User.eavD(2,"attributes","Age")=186
^User.eavD(2,"attributes","Family")="Pines"
^User.eavD(2,"attributes","Height")=22
^User.eavD(2,"attributes","Name")="Sibérian píne cédar"
インデックスを持つグローバル
USER>zw ^User.eavI
^User.eavI("idx1"," 186",1)=$lb("","Human")
^User.eavI("idx1"," 186",2)=$lb("","Tree")
^User.eavI("idx1"," 22",1)=$lb("","Human")
^User.eavI("idx1"," 22",2)=$lb("","Tree")
^User.eavI("idx1"," JACK",1)=$lb("","Human")
^User.eavI("idx1"," PINES",2)=$lb("","Tree")
^User.eavI("idx1"," SIBÉRIAN PÍNE CÉDAR",2)=$lb("","Tree")
^User.eavI("idx2","Age"," 186",2)=$lb("","Tree")
^User.eavI("idx2","Age"," 22",1)=$lb("","Human")
^User.eavI("idx2","Family"," PINES",2)=$lb("","Tree")
^User.eavI("idx2","Height"," 186",1)=$lb("","Human")
^User.eavI("idx2","Height"," 22",2)=$lb("","Tree")
^User.eavI("idx2","Name"," JACK",1)=$lb("","Human")
^User.eavI("idx2","Name"," SIBÉRIAN PÍNE CÉDAR",2)=$lb("","Tree")

それでは、以下のクエリを実行しましょう。

entity
select entity from eav where attributes->attr = 22
Human
Tree

クエリは実行されるものの、インデックスではなくフルスキャンが使われています。 SMP (System Management Portal) のテーブルを見ると、そこには生成されたはずの idx1 と idx2 がありません。

これが起るのは、SQL エンジンには、サブテーブルの配列のフィールドだけを基に作成された配列プロパティで、キー (すなわち propArray(KEY)) を持つもののインデックスしか「見えない」ためです。 両方のインデックスに「entity」フィールドがあるにもかかわらず、「attribute」サブテーブルにはそれが見当たりません。

また、attributes(KEYS) を持たない <FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx3 On attributes(ELEMENTS);</FONT> も表示されません。但し、こちらのインデックス

  • <FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx4 On (attributes(KEYS), attributes(ELEMENTS));</FONT>
  • <FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx5 On (attributes(ELEMENTS), attributes(KEYS));</FONT>
は表示され、クエリに考慮されます。 しかし、これらはあらゆる種類のクエリに最適という訳ではありません。

では、SQL エンジンが配列プロパティ要素のインデックスを見えるようにするには、どのメソッドを使うのが一番簡単なのでしょうか？

Caché 2015.1 では、SetCollectionProjection/GetCollectionProjection メソッドを使ってサブテーブルに表示できるコレクションであれば、それをテーブルフィールドとして表示することができます。

ですが、この機能はデフォルトで無効になっています。

これらのメソッドは、Caché の過去のバージョンにはありませんが、手動で有効化できるかを試すことはできます。

%SYS><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#000000">^%SYS(</FONT><FONT COLOR="#008000">"sql"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"sys"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"collection projection"</FONT><FONT COLOR="#000000">)=1</FONT>

この変更を実行した後は、必ずクラスをもう一度コンパイルしてください。

それでは、このパラメーターをオン (1) にして、その動作を見てみましょう。

SMP にインデックスが表示されるようになりました。また、「eav」テーブルには「attr」という隠れたコレクションフィールドがあります。 しかし、それでも私たちが実行したクエリはインデックス idx1/idx2 を見つけられません。

それでは、お馴染みの述語 FOR SOME %ELEMENT を使って、この状況を解決しましょう。

entity
select entity from eav where for some %element(attr) (%value = 22)
Human
Tree

インデックス idx1 がクエリで使用されています。 これを少しだけ変更します。

entity
select entity from eav where for some %element(attr) (%value = 22 and %key= 'Age')
Human
entity
select entity from eav where for some %element(attr) (%value = 22 and %key= 'Height')
Tree
最後の 2 つの例では、インデックス idx1 の代わりにインデックス idx2 が使用されています。

UPD: SQLPROJECTION を使えば、これと同じことができます。つまり、以下を実行します。

<FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">attributes </FONT><FONT COLOR="#000080">As array Of %String</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">SQLPROJECTION </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800080">"table/column"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">SQLTABLENAME </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800080">"attributes"</FONT><FONT COLOR="#000000">) [ </FONT><FONT COLOR="#000080">SqlFieldName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">attr </FONT><FONT COLOR="#000000">];</FONT>

この記事は、こちらの記事を翻訳したものです。 [@Evgeny Shvarov]、翻訳作業にご協力いただきありがとうございました。

この記事は、Habrahabr露 でもお読みいただけます。

本記事を書くきっかけとなったサイト: 17383689露

WRC のフレームワークについてヒントをくださった [@Alexander.Koblov] に深く感謝します。

ディスカッション (0)2
続けるにはログインするか新規登録を行ってください