Indexing on a Multivalue file

Question

Question

Mindy Caldwell · Apr 21, 2023

#MultiValue Basic #InterSystems IRIS

Salesrep is the production file with only an index on the idkey (it has more properties than shown here).

Property SalesRepId As %String(MVATTRIBUTE = 0, MVJUSTIFICATION = "L", MVWIDTH = 10) [ Required ];

Property Surname As %String(MVATTRIBUTE = 2, MVJUSTIFICATION = "L", MVWIDTH = 25);

Property Forename As %String(MVATTRIBUTE = 3, MVJUSTIFICATION = "L", MVWIDTH = 25);
. . .

Property FundraiserId As %String(MVATTRIBUTE = 15, MVJUSTIFICATION = "L", MVWIDTH = 20);

Index SalesRepIdIndex On SalesRepId [ IdKey, PrimaryKey, Unique ];

Reports using SELECTs against the file on the FundraiserId property are running too slow for user acceptance.

I copied the class definition for Salesrep to Iseller and added an index on the FundraiserId

Property SalesRepId As %String(MVATTRIBUTE = 0, MVJUSTIFICATION = "L", MVWIDTH = 10) [ Required ];

Property Surname As %String(MVATTRIBUTE = 2, MVJUSTIFICATION = "L", MVWIDTH = 25);

Property Forename As %String(MVATTRIBUTE = 3, MVJUSTIFICATION = "L", MVWIDTH = 25);
. . .

Property FundraiserId As %String(MVATTRIBUTE = 15, MVJUSTIFICATION = "L", MVWIDTH = 20);

Index SalesRepIdIndex On SalesRepId [ IdKey, PrimaryKey, Unique ];

Index FundraiserIndex On FundraiserId;

At the Multivalue prompt, I copied the data from Salesrep to Iseller. And I can see in the Management Portal that the index was populated for Iseller.

However, select commands on the FundraiserId no longer work for exact values. I can select on the ISELLER (with index) if I put a space at the beginning of the value in quotes.

Example,

TRAX:SELECT SALESREP WITH FundraiserId "HTE_FSG_SPRING2022"

505 Items selected to list #0
TRAX>>clearselect
TRAX:
TRAX:SELECT ISELLER WITH FundraiserId "HTE_FSG_SPRING2022"

[401] No items present.
TRAX:SELECT ISELLER WITH FundraiserId " HTE_FSG_SPRING2022"

505 Items selected to list #0
TRAX>>LIST ISELLER FundraiserId

LIST ISELLER FundraiserId 01:46:19pm 21 Apr 2023 PAGE 1
ISELLER... FundraiserId........

HTE_101 HTE_FSG_SPRING2022
HTE_102 HTE_FSG_SPRING2022

Is this the expected behavior?

I was hoping to be able to add the index without having to modify all the programs that use a SELECT (or COUNT).

Product version: IRIS 2021.1

Discussion (4)2

Log in or sign up to continue

Edward Clark · Apr 27, 2023

The problem is the collation. MV Query expects the collation of a string-type property and its index to be either SqlString (or SPACE in older versions) for left-justified fields or MVR for right justified fields (that aren't dates or times). If the class and index had been created starting with the mv file dictionary and using PROTOCLASS and CREATE.INDEX the collation would have been created appropriately. If the class is created manually then the collation needs to be set.

I found a couple of useful notes in the documentation. At https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY... it says:

Multivalue users who upgrade their applications from a release prior to 2012.1 to 2012.1 or later may experience a slowdown in CMQL queries that had previously made use of an index. Previously, CMQL assumed the collation for left-justified strings was SPACE. It is now SQLSTRING(150). This will not match an existing index that was created with the previous default of SPACE collation.

To correct this issue, use studio to edit the class associated with the multivalue file and change the COLLATION parameter to SQLSTRING(150) on all properties that are used as index keys. Then rebuild the affected indexes.

and the documentation for CREATE.INDEX at https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY... notes:

CREATE.INDEX assigns SqlString(150) collation to both the index and the created indexfield property.

So you need to add collation to your property and index..

1 0

score 0 · Answer 1 · 2023-04-25T17:36:05-04:00

The index needs to have data for the query to work. It should be
Index FundraiserIndex On FundraiserId [ Data = FundraiserID];
and then rebuild the index.

score 0 · Answer 2 · 2023-04-26T16:55:15-04:00

Edward, Thanks for your suggestion.

Class modified as suggested. Index rebuilt.

Select command still fails unless I put the leading space.

TRAX:SELECT ISELLER WITH FundraiserId = "HTE_FSG_SPRING2022"

[401] No items present.
TRAX:SELECT ISELLER WITH FundraiserId = " HTE_FSG_SPRING2022"

505  Items selected to list #0
TRAX>>clearselect
TRAX:SELECT SALESREP WITH FundraiserId = "HTE_FSG_SPRING2022"

505  Items selected to list #0
TRAX>>clearselect
TRAX:

score 0 · Answer 3 · 2023-04-28T10:28:32-04:00

Edward, Thank YOU! You solved the problem. I'll need to do more testing but on first glance it appears that by adding COLLATION = "Space" to the property and recompiling the class, the CMQL select now works as I expect.