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: https://community.intersystems.com/post/querying-list-property-sql, 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 WHERE FOR SOME %ELEMENT(table.Tags) as T (%Key='TWO')
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?