New post
Question
· Jul 12, 2018

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

#Object Data Model #ObjectScript #SQL #Caché

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?

Thanks,

Laura

Discussion (4)2
Log in or sign up to continue
Laura Cavanaugh · Jul 12, 2018

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 {
d ..TagsArray.SetAt(tag,tag.Tag)
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.

0 0
Timothy Leavitt · Jul 12, 2018

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

}
0 0