SQL index for array property elements

Sometimes, it comes in very handy (especially for the EAV model) to use array properties in a class and be able to qickly search by their elements: both the key and the value.

Let’s take a look at a simple example:

<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>

After population

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>

, the following data will appear in our tables: A global with data:

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"

A global with indexes:

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")

Let’s run the following query now:

The query runs, but uses full scanning and not our indexes. If we look at our tables in the SMP (System Management Portal), we won't find idx1 and idx2 there, although we know for sure that the data was generated.

This happens because the SQL engine “sees” only those indexes for array properties that are based exclusively on the fields of the subtable array and contain a key, i.e. propArray(KEY). Both of our indexes contain the “entity” field, which is missing in the “attributes” subtable.

You will also not see the <FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx3 On attributes(ELEMENTS);</FONT>, since it doesn't contain attributes(KEYS), but the following indexes:

• <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>

will be visible and will be taken into account in queries. However, they are not optimal for all types of queries.

So what is the most effortless method of unhiding indexes for the elements of an array property from the SQL engine?

Caché 2015.1 allows you to project a collection as a table field, if this collection projects into a subtable using the SetCollectionProjection/GetCollectionProjection methods.

This functionality is disabled by default.

Earlier versions of Caché do not have these methods, but you can try to enable this feature manually:

%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>

After you make this change, make sure to recompile the classes.

So, let's turn this parameter on and see what it does.

We can now see our indexes in the SMP, and there is a hidden collection-field called “attr” in the “eav” table. However, our query still doesn't see the idx1/idx2 indexes.

To fix the situation, let's use the already familiar predicate FOR SOME %ELEMENT:

The idx1 index is now used in the query. Let's change it a bit:

The last two examples use the idx2 index instead of idx1.

UPD: now the same can be done using SQLPROJECTION, namely:

<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>

---

This is a translation of the following article. Thanks [@Evgeny Shvarov] for the help in translation.

This post is also available on Habrahabr^ru.

Inspired by 17383689^ru.

Special thanks to [@Alexander Koblov] for the tip in the framework of WRC.