How to efficiently query collection properties

Article

David Loveluck · Feb 1, 2018 1m read

Here is a snippet that I learned yesterday

You can define an index on a collection property but when I tried to use it, I failed. I was using

     Select ….. where …. :xx %INLIST collproperty

But this will not use an index, but the equivalent syntax

     SELECT .. WHERE ... FOR SOME %ELEMENT(collproperty) (%VALUE=:xx)

will use the index

Check out

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

Dave

Discussion (2)1

Vitaliy Serdtsev · Feb 2, 2018

Please note that the option with %INLIST does not yet use “collection” indexes, and will therefore be slower than the one provided above ^proof

0 0

Robert Cemper · Feb 2, 2018

To my understanding the main purpose of introducing %INLIST in our SQL was
to have a more handy option than the classic IN (....)

%INLIST takes a Single $LISTBUILD() as parameter with a Variable number of values that you pass at runtime by only 1 "?"

while IN (p1,p2,p3,p4) or better IN (?,?,?,?) requires a fixed static number of parameters during execution.
so you have to know the maximum number of values ahead when you create your query
and eventually to fill unused parameters by some impossible value to feed the query

2 0