Laura Cavanaugh · Jul 12, 2018

How to make an array of Objects property use index to find rows with low cost

I have a class that has a property calledTags (like DescriptiveWords, but tags), where multiple tags are possible.  I am trying to decide on list of Objects vs. array of Objects.

Based on this post:, sounds like using an array of Objects is the better way to go. Indeed, I already noticed that it's not possible to have duplicates when using an array of Objects.

However, I am unable to make my queries on the array of Object use an index.  

Here is a code example, with queries and the cost of the query (see the comments for each Index)I have found when running the query in Mgmt POrtal:

Property TagsArray As array Of Data.Tag;

/// To query:
/// SELECT claim.ID, tag.* from table claim
/// JOIN table_TagsArray tag ON tag.Claim=claim.ID
/// WHERE tag.TagsArray->ID=1
/// Finds all the claims with Tag.ID=1
/// Cost: 104692 global references 576896 lines executed
Index TagsArrayIndex On TagsArray(ELEMENTS);

/// To query:
/// SELECT claim.ID, tag.* from table claim
/// JOIN table_TagsArray tag ON tag.Claim=claim.ID
/// WHERE tag.TagsArray->Tag='TWO'
/// Finds all claims with Tag.Tag = 'TWO'
/// Cost: 104695 global references 576747 lines executed
Index TagsArrayKeys On TagsArray(KEYS);

Property Tags As list Of Data.Tag;

/// To query:
/// SELECT ID,ClaimNumber,Tags FROM table
///    WHERE FOR SOME %ELEMENT(table.Tags) as T
///        (%Value='3')
/// Finds all the claims with Tag.ID=3
/// Cost: 22 global references 739 lines executed
Index TagsIndex On Tags(ELEMENTS);

/// To query:
/// SELECT ID,ClaimNumber,Tags FROM table
///    WHERE FOR SOME %ELEMENT(table.Tags) as T
///        (%Key='TWO')
/// Finds all the claims with Tag.Tag='TWO'
Index TagsKeys On Tags(KEYS);

Lastly, I was not able to get the query

SELECT ID, ClaimNumber,Tags FROM 


to return any results.

I don't really care if the tables are "normalized relational" tables -- so a list of Objects is fine with me, especially since the queries to find claims with those tags use the indexes, and are much less costly.  We'd have to enforce uniqueness, but we can handle that.

On the other hand, I've heard before that using array of Object sis beeter, but I won't use it unless I can figure out how to make the index(es) work.

What am I doing wrong?



3 0 4 512
Log in or sign up to continue


I have been killing index globals and rebuilding indices (a lot), because I was also changing how I added the Tag.  

/// Add Tag,Tag.Tag as Key, to claim array of Tags
Method AddTag(tag As Data.Tag) As %Status
set ok=$$$OK
try {
do ..Tags.Insert(tag)
catch (ex) {
set ok=ex.AsStatus() 
quit ok

I had some version of code yesterday where my last query worked, and used the index.  I imagine that we'll be looking for tags based on the "Tag" property (i.e. 'LLC' or 'TWO') rather than the ID, but we could just use the ID.

After further review, I'm really not sure why the first/second queries don't use the index. The problem with the last query is that %Key is the index in the list, not anything about Tag itself.

Here's a solution that performs well in my testing:

Class DC.Demo.Tag Extends (%Persistent, %Populate)

Index Tag On Tag [ Unique ];

Property Tag As %String;


Class DC.Demo.Tagged Extends (%Persistent, %Populate)

Relationship HasTags As DC.Demo.HasTag [ Cardinality = children, Inverse = Tagged ];

ClassMethod Run()
    Do ..%KillExtent()
    Do ##class(DC.Demo.Tag).%KillExtent()
    Do ##class(DC.Demo.Tag).Populate(50)
    Do ..Populate(5000)
    Do ##class(DC.Demo.HasTag).Populate(10000)


Class DC.Demo.HasTag Extends (%Persistent, %Populate)

Relationship Tagged As DC.Demo.Tagged [ Cardinality = parent, Inverse = HasTags ];

Property Tag As DC.Demo.Tag [ Required ];

Index UniqueTag On Tag [ IdKey ];

Index TaggedByTag On (Tag, Tagged);



Index TagsArrayIndex On (TagsArray(ELEMENTS), TagsArray(KEYS));