@Enrico Parisi
See my answer above.

That's weird, ##class(loggpro.IndexTest).%BuildIndices() indeed recreates the index properly. But the way I do it, with Portal's SQL, Rebuild Index just wipes the index out. Please check how Portal works for you.

Now all my fields are of type %Library.Integer but Rebuild Index still just deletes ^mytest("idx"). Does Rebuild Index work for you?

Here it is. InsertData properly creates both ^mytest("data") and ^mytest("idx"). Rebuild Index does nothing except deletes ^mytest("idx").

Class loggpro.IndexTest Extends %Persistent [ ClassType = persistent, ProcedureBlock, SqlCategory = INTEGER, StorageStrategy = itemstore ]
{

Property aaa;
Property bbb;
Index prim On aaa [ IdKey, PrimaryKey ];
Index idxBbb On bbb;

ClassMethod InsertData()
{
	for a=1:1:10 {	
		s o=##class(loggpro.IndexTest).%New()
		s o.aaa=a
		s o.bbb=a+100
		d o.%Save()
	}
}

Storage itemstore
{
<SQLMap name="indexmap">
<Global>^mytest</Global>
<PopulationType>full</PopulationType>
<Subscript name="1">
<Expression>"idx"</Expression>
</Subscript>
<Subscript name="2">
<Expression>{bbb}</Expression>
</Subscript>
<Subscript name="3">
<Expression>{aaa}</Expression>
</Subscript>
<Type>index</Type>
</SQLMap>
<SQLMap name="itemmap">
<Data name="bbb">
<Node>"bbb"</Node>
</Data>
<Global>^mytest</Global>
<Subscript name="1">
<Expression>"data"</Expression>
</Subscript>
<Subscript name="2">
<Expression>{aaa}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^CacheTemp</StreamLocation>
<Type>%CacheSQLStorage</Type>
}

}

I have a question regarding to the indexing: for me example shown in the article works fine, the index global is populated property for inserted records but then I have two problems:

- Rebuild Index just deletes the whole index global and creates nothing instead.

- Show Plan indicates that my index cannot be used directly when selecting on the indexed property. Instead, the executor has to create a temporary map applying %SQLUPPER to the property.

Any advice how to make fully compatible index?

Cache for Windows (x86-64) 2018.1.5

Cooooooooool!!!

I've never worked with NET client but I suppose it works on request/response principle, so it is not possible to get async call from the database.

Great article! Thank you for sharing your experience and thoughts with us.