New post
Question
· Nov 29, 2016

SQL and indexing on collection properties

#Caché #Indexing #SQL

I have a class which defines a property as array of %String. Is it possible to index values of this property and use this property in SQL?

I have tried 'Index idx On prop(ELEMENTS)' and then a select from the generated collection table, but this is still orders of magnitude slower than queries to the containing class.

Discussion (2)1
Log in or sign up to continue
Brendan Bannon · Nov 29, 2016

Jiri

You have a couple of options:  I created an index in Sample.Person that is the same as yours and then look at this query

SELECT Name,FavoriteColors FROM Sample.Person
    WHERE FOR SOME %ELEMENT(FavoriteColors) (%Value = 'Red')

It uses the index and should give very good performance.

  • Read index map Sample.Person.FavColors, using the given %SQLUPPER(Subvalue(FavoriteColors)), and looping on ID.

  • For each row:
  •  Read master map Sample.Person.IDKEY, using the given idkey value.
     Output the row.

Have a look at the docs for info on Indexing Collections

The above is a little different as it is a List instead of an Array, but it should still work.

If you want better performance out of the Child Table created for the array you need to define the index to be on both the Element and the Key.  I think if you look in the Management Portal at the maps /  indices for your table you will see the one you defined is not projected in the child table.

I added the following Property and index:

Property Kids As array Of %String;

Index KidIndex On (Kids(ELEMENTS), Kids(KEYS));

and for this query:

SELECT id FROM Sample.Person_Kids WHERE Kids = 'Kieran'

I get a plan that uses the index.

  • Read index map Sample.Person_Kids.KidIndex, using the given %SQLUPPER(Kids), and looping on element_key and Person.

  • For each row:
  •  Output the row.

Either way should give you good performance.

hope this helps.

Brendan

0 0