Indices Not Working

Question

Question

Keith Davis · May 31, 2019

Forgive my lack of understanding, but I am working on a vendor created Cache database and the indexes specified in the classes for ODBC connections are not working. A few of them are created, but of the 50+ classes we have, only a handful of them are created when the class is compiled, yet no errors occur.

Example 1:

Example 2:

The code is creating the Index, and again, not compile errors, but most of the indices are not built.

Discussion (21)1

Log in or sign up to continue

Dan Pasco · Jun 18, 2019

Fair enough. Let's make this more fun! First of all, we need to understand the IDKEY index. InterSystems is somewhat unique in the market with this one. Most SQL vendors support indexes and most support "clustering" data with indexes to further improve the performance of certain queries. Think of our IDKEY index as the "Master Data Index" (sometimes referred to as the "Master Map" or "Data Map"). The "Master Data Index" (MDI) is simply the index that has every column "clustered" with it - all data values are present in the IDKEY index structure. Every table (persistent class) has an IDKEY index and every column (property in Object terms) is stored with it.

Any other index - not the MDI - is simply there to improve query performance. Each non-MDI index has an index key and perhaps some data clustered with it. InterSystems is not different from most other SQL implementations. You can create a new table and add indexes to it using standard DDL statements. We, like most vendors, have some non-standard syntax to support features unique to our product but it is mostly familiar syntax. You can also define a class directly using one of our IDE's or your favorite editor. And all of this will just work - no special effort required and your experience will be similar to that of using any other SQL.

Your example is different. We support low-level storage mapping to legacy data structures. There are two additional storage classes that support this, one of which is used by your example - %CacheSQLStorage. This storage class was introduced specifically to provide a bridge from legacy applications to Caché/InterSystems IRIS. Mapping legacy data structures can be a complicated task and it can be confusing. And this is where you are - you have an application that is using our support for bridging existing data structures to our DBMS.

And none of that helps you with your problem! So let's try this. Let's define your class without using the legacy data mapping. I'll choose some simply data types just to illustrate.

Class User.Patient Extends %Persistent
{

Index AcctIndex On accountNo;

Property accountNo As %String;

Property citySt As %String;

Property dob As %Date;

Property name As %String;

Property patientNo As %Integer;

Property rel2Guar As %String;

Property sex As %String;

Property ssn As %String;

Property street1 As %String;

Property street2 As %String;

Property telephone As %String;

Property zip As %String;

}

That's it - no special work involved, just a quick and easy class definition with an index on accountNo defined. This is what it looks like in the SQL Manager:

This is an example without the legacy global mapping. %BuildIndices is automatically generated and there is nothing else you would need to do to define, populate and maintain this index - other than to build it initially if it was added to a previously populated extent.

In my next comment (this one is getting long), I'll convert the default storage structure into a mapped storage structure using %CacheSQLStorage.

2 0

Dan Pasco · Jun 18, 2019

In reviewing the storage definition you present above, I realize that the list of properties has little to do with the SQL Map definitions it includes. So I will continue with my example, using the properties your example defines (they are present in the storage definition only for the purpose of computing statistics and other storage specific metadata).

Without altering the class definition, other than to add a couple of keywords to make life a bit simpler in the class header and the conversion of the storage to use SQL Mapped Storage (%CacheSQLStorage in your version), and adding %Populate - our test data generator - I came up with this example data storage:

USER>d ##class(Patient).Populate(10)

USER>zw ^User.PatientD

^User.PatientD=10

^User.PatientD(1)=":J5201:Z5211:58985:Isaacs,Michael A.:501759566:H2536:A8788:377-96-6394:J7857:G3137:R4692:42233"

^User.PatientD(2)=":S4498:Z4308:62077:Cooke,Mario C.:832248338:Z9867:V1891:859-50-1555:I9221:H3938:W7632:25909"

^User.PatientD(3)=":J8016:S3895:53889:Quilty,Nellie J.:150706592:J3845:B6691:998-18-8294:B482:D5191:R7961:59453"

^User.PatientD(4)=":H8837:T289:43380:Quince,Samantha U.:115923507:F6623:S6188:407-88-9788:R8672:Y1441:A9501:60822"

^User.PatientD(5)=":N1854:W4497:55465:North,Susan A.:784860058:Z8257:E2831:187-93-1523:T3167:U4316:A9955:34073"

^User.PatientD(6)=":A4324:Z7427:61318:Lopez,Will F.:133068033:K7869:R5254:302-73-2490:B2970:F1889:P8421:26050"

^User.PatientD(7)=":N4346:Y671:65137:Moon,Milhouse Z.:459189579:E6160:R3362:507-37-8752:L8401:R7909:F4245:60716"

^User.PatientD(8)=":N4328:V1682:47890:Zweifelhofer,Terry V.:360767849:B8856:E145:466-23-4632:K4269:X2839:S1937:49318"

^User.PatientD(9)=":M7370:B6086:49261:Rotterman,Lawrence S.:353537548:S6329:B9164:874-34-2035:D4140:U7504:N1456:66241"

^User.PatientD(10)=":W995:B5004:50613:Ironhorse,Barb I.:809117324:S6518:V1966:873-92-8543:Z9470:H6976:G2259:40210"


USER>zw ^User.PatientI

^User.PatientI("A"," A4324",6)=""

^User.PatientI("A"," H8837",4)=""

^User.PatientI("A"," J5201",1)=""

^User.PatientI("A"," J8016",3)=""

^User.PatientI("A"," M7370",9)=""

^User.PatientI("A"," N1854",5)=""

^User.PatientI("A"," N4328",8)=""

^User.PatientI("A"," N4346",7)=""

^User.PatientI("A"," S4498",2)=""

^User.PatientI("A"," W995",10)=""

And this is the full definition of the class - with the index map defined as a new SQL Map Definition in the storage. I highlighted a few things to show the connections between the logical class definition and the corresponding items in the storage definition.

Class User.Patient Extends (%Persistent, %Populate) [ SqlRowIdName = Patient, StorageStrategy = SQLStorage ]
{

Index AcctIndex On accountNo;

Property accountNo As %String;

Property citySt As %String;

Property dob As %Date;

Property name As %String;

Property patientNo As %Integer;

Property rel2Guar As %String;

Property sex As %String;

Property ssn As %String;

Property street1 As %String;

Property street2 As %String;

Property telephone As %String;

Property zip As %String;

Storage SQLStorage
{
<SqlIdExpression>$i(^User.PatientD)</SqlIdExpression>
<SQLMap name="DataMasterMap">
<Data name="accountNo">
<Delimiter>":"</Delimiter>
<Piece>2</Piece>
</Data>
<Data name="citySt">
<Delimiter>":"</Delimiter>
<Piece>3</Piece>
</Data>
<Data name="dob">
<Delimiter>":"</Delimiter>
<Piece>4</Piece>
</Data>
<Data name="name">
<Delimiter>":"</Delimiter>
<Piece>5</Piece>
</Data>
<Data name="patientNo">
<Delimiter>":"</Delimiter>
<Piece>6</Piece>
</Data>
<Data name="rel2Guar">
<Delimiter>":"</Delimiter>
<Piece>7</Piece>
</Data>
<Data name="sex">
<Delimiter>":"</Delimiter>
<Piece>8</Piece>
</Data>
<Data name="ssn">
<Delimiter>":"</Delimiter>
<Piece>9</Piece>
</Data>
<Data name="street1">
<Delimiter>":"</Delimiter>
<Piece>10</Piece>
</Data>
<Data name="street2">
<Delimiter>":"</Delimiter>
<Piece>11</Piece>
</Data>
<Data name="telephone">
<Delimiter>":"</Delimiter>
<Piece>12</Piece>
</Data>
<Data name="zip">
<Delimiter>":"</Delimiter>
<Piece>13</Piece>
</Data>
<Global>^User.PatientD</Global>
<Structure>list</Structure>
<Subscript name="1">
<Expression>{Patient}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<SQLMap name="IndexNName">
<BlockCount>-4</BlockCount>
<Global>^User.PatientI</Global>
<Structure>list</Structure>
<Subscript name="1">
<Expression>"A"</Expression>
</Subscript>
<Subscript name="2">
<Expression>$$SQLUPPER({accountNo})</Expression>
</Subscript>
<Subscript name="3">
<Expression>{Patient}</Expression>
</Subscript>
<Type>index</Type>
</SQLMap>
<StreamLocation>^User.PatientS</StreamLocation>
<Type>%Storage.SQL</Type>
}

}

and this is how it appears in the SQL Manager display of the indexes:

4 0

Dan Pasco · Jun 13, 2019

Hi Keith,

%BuildIndices/%PurgeIndices are generated for any class that primarily extends %Library.Persistent. When such a class defines one or more indexes and that class uses SQL Storage then the class must also define an index map in the storage definition. Without that the index simply won't be maintained - it doesn't exist as far as SQL is concerned. Your display in the original posting is a view of the SQL table definition. The SQL table definition is a projection of the class definition to SQL.

Your first example shows an index that is defined as the IDKEY index - that is the index definition that corresponds to the SQL Master Map (data map). I'm not surprised that it doesn't show up in the indexes display of the SQL Manager.

If WRC isn't an option then a good place to start is to work through just one of your classes. If you can choose one simple example perhaps we can suggest corrections for you.

-Dan

1 0

Robert Cemper · May 31, 2019

Well the code you posted shows a rather ancient type of table mapping away from standards.

And the errors you got underlines this as standard methods like %BuildIndices and %PurgeiIndices are not implemented.

I see 3 possible options:
- you find in the code (not necessarily in the class definition) some method to (re) generate the indices.
Without experience with Caché and Object Script a rather impossible task.

- you create a copy of the class definition and move all data there by INSERT INTO new.table SELECT * from old.table
but it is unclear from what you showed to us if INSERT into your table is supported at all

- least risky. you out-comment all broken indices except [IDKEY] and just use a flat table scan.
this is a fast workaround at the price of a poor performance on bigger tables.

honestly. - not a funny situation

3 0

Robert Cemper · May 31, 2019

You gave the answer already!

By compilation, you DEFINE indexes at their rules.

But you need to generate their content separately by ClassMethod

%BuildIndices

in each class.

Or from management portal.

Then they are ready to use.

5 0

Robert Cemper · Sep 15, 2020

with complete I mean something starting with

Class.... Extends %Persitent......
all Properties, ....
until the last line of Storage definition. Just a copy out of Studio
actually at least I just see a little bit here, some part there like a puzzle.

1 0

Dan Pasco · Sep 16, 2020

You should not write %BuildIndices/%PurgeIndices. Can you provide the SQL map definition and the index definition of one of your indexes - just as an example? Perhaps we can start with a simple index on a table with a smaller number of rows?

0 0

score 1 · Answer 1 · 2019-05-31T15:55:36-04:00

Looks like you have a custom SQL Storage. And in this case, you have to have manually defined storage for indexes as well. And only after that, it will build indices.

score 0 · Answer 2 · 2019-05-31T16:31:52-04:00

Right after I posted this, I started working on the theory of what you are describing and got one to work...sort of. I can see the index now, but I don't really understand how the storage for these is supposed to work (I barely understand SQL storage at all).

Also, it seems like the inverse is not true. I have some that have the storage map created, but no matching Objectscript property and the index builds just fine...well, except the index does not match the storage map description.

Example:

score 0 · Answer 3 · 2019-06-18T08:22:27-04:00

I don't think I can use the WRC. I am not an Intersystems customer, the vendor that built and maintains the product that uses Cache is the customer. We were provided access so that we can integrate our own systems into theirs.

%BuildIndices/%PurgeIndices are generated for any class that primarily extends %Library.Persistent. When such a class defines one or more indexes and that class uses SQL Storage then the class must also define an index map in the storage definition. Without that the index simply won't be maintained - it doesn't exist as far as SQL is concerned. Your display in the original posting is a view of the SQL table definition. The SQL table definition is a projection of the class definition to SQL.

I get that part, and I get how to add those methods to my class, what I don't get is how to build the actual index. I can't find an example simple enough to, nor in reading the documentation do I, understand how to "create" an index. Again, I come from a MySQL background (and some MongoDB), and indexes are just specified, not created.

I'm not sure what you need for a "simple" example.

I want to create this Index (I realize the IdKey covers this one, but that is not showing up in the SQL Manager, so I want to test this one):

Index Account On Account;

<Storage name="demoStor">
<ExtentSize>100000</ExtentSize>
<Property name="accountNo"/>
<Property name="citySt"/>
<Property name="dob"/>
<Property name="name"/>
<Property name="patientNo"/>
<Property name="rel2Guar"/>
<Property name="sex"/>
<Property name="ssn"/>
<Property name="street1"/>
<Property name="street2"/>
<Property name="telephone"/>
<Property name="zip"/>
<SequenceNumber>80</SequenceNumber>
<SQLMap name="PatDemoMap">
<Data name="Account">
<Delimiter>":"</Delimiter>
<Piece>1</Piece>
</Data>
<Data name="AdmissionDate">
<Delimiter>":"</Delimiter>
<Piece>14</Piece>
</Data>
<Data name="DateChanged">
<Delimiter>":","^"</Delimiter>
<Piece>25,1</Piece>
</Data>
<Data name="Diag1Code">
<Delimiter>":"</Delimiter>
<Piece>17</Piece>
</Data>
<Data name="DischargeDate">
<Delimiter>":"</Delimiter>
<Piece>15</Piece>
</Data>
<Data name="Ethnicity">
<Delimiter>":"</Delimiter>
<Piece>35</Piece>
</Data>
<Data name="MedicalRecord">
<Delimiter>":"</Delimiter>
<Piece>11</Piece>
</Data>
<Data name="Note">
<Delimiter>":"</Delimiter>
<Piece>18</Piece>
</Data>
<Data name="Notes1">
<Node>"1"</Node>
</Data>
<Data name="Notes2">
<Node>"2"</Node>
</Data>
<Data name="OperatorChanged">
<Delimiter>":","^"</Delimiter>
<Piece>25,2</Piece>
</Data>
<Data name="PatInsOrd">
<Node>"0"</Node>
</Data>
<Data name="PatPolicy">
<Node>"P"</Node>
</Data>
<Data name="Patient">
<Delimiter>":"</Delimiter>
<Piece>2</Piece>
</Data>
<Data name="PrefLanguage">
<Delimiter>":"</Delimiter>
<Piece>36</Piece>
</Data>
<Data name="ProviderCode">
<Delimiter>":"</Delimiter>
<Piece>13</Piece>
</Data>
<Data name="Race">
<Delimiter>":"</Delimiter>
<Piece>34</Piece>
</Data>
<Data name="RecallInfo">
<Node>"9"</Node>
</Data>
<Data name="ReferringDocCode">
<Delimiter>":"</Delimiter>
<Piece>16</Piece>
</Data>
<Data name="Relationship">
<Delimiter>":"</Delimiter>
<Piece>21</Piece>
</Data>
<Data name="ReminderCode">
<Delimiter>":"</Delimiter>
<Piece>19</Piece>
</Data>
<Global>^PTG</Global>
<Subscript name="1">
<Expression>{Account}</Expression>
<Invalidcondition name="1">
<Expression>{L1}="Z"</Expression>
</Invalidcondition>
<LoopInitValue>99</LoopInitValue>
<StopExpression>{L1}="Z"</StopExpression>
</Subscript>
<Subscript name="2">
<Expression>{Patient}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^Adsc.MedicsPremier.PatientsS</StreamLocation>
<Type>%CacheSQLStorage</Type>
</Storage>

I added these to my class, which compiles, but of course does nothing:

ClassMethod %BuildIndices() As %Library.Status
{
}

ClassMethod %PurgeIndices() As %Library.Status
{
}

score 0 · Answer 4 · 2019-05-31T16:09:07-04:00

Keith Davis · May 31, 2019

Thanks. I've tried that and it does not work (no errors, no indices built).

0 0

score 0 · Answer 5 · 2019-05-31T16:15:13-04:00

I guess it would help to completely read your response. Also, I was very wrong, almost all of these generate this error:

Adsc.MedicsPremier.Demographics ERROR #5758: Method not implemented: Adsc.MedicsPremier.Demographics::%BuildIndices/%PurgeIndices

But you need to generate their content separately by ClassMethod

%BuildIndices
in each class.

What does that mean? I'm a PHP / C# developer and some of the Objectscript / Cache processes still eludes me.

score 1 · Answer 6 · 2019-05-31T16:52:36-04:00

I think it would be more efficient if you look at a series of articles about mapping globals to classes. So, you'll get some knowledge about how it can be configured. It is varied by different storage strategies.

Next, as Robert mentioned, you have to rebuild your indices. Look at the documentation for the details, how to do it.

score 0 · Answer 7 · 2019-05-31T18:34:32-04:00

I've looked at those articles, but given the old version of cache we are using (2013) and the horribly inconsistent ways these classes are written, plus my inexperience in this very "different" system than what I am used to, I have a hard time getting much out of them. I'll take another look.

score 0 · Answer 8 · 2019-05-31T16:10:29-04:00

Ok, I take that back. Some of these generate no errors and no indices are built, but some of them generate this error:

Adsc.MedicsPremier.Demographics ERROR #5758: Method not implemented: Adsc.MedicsPremier.Demographics::%BuildIndices/%PurgeIndices

What does that mean?

score 0 · Answer 9 · 2020-09-15T14:19:36-04:00

Thanks for the info, yes, this is an old version of Cache, and given the fact that we are stuck on this due to our vendor not upgrading the system, we have no choice.

I need the indices, that is the reason I am working on the system right now, as some of these tables are performing so poorly that we are getting timeouts.

score 3 · Answer 10 · 2019-05-31T18:54:33-04:00

OK. I experienced that scenario quite often.

Suggestion to verify that indices will help you.
- Take a typical class definition, make a copy without storage definition (or remove it)
- compile it with Chaché Default storage and run INSERT INTO new.table SELECT * from old.table a flat table scan
- verify the new table against your needs.
- if it fits then the problem has moved to "update frequency" which might be easier to solve.

It's, of course, limited to the critical tables and not the whole DB

score 0 · Answer 11 · 2019-06-13T08:35:00-04:00

Are there any online tutorials and/or simple examples of how to write the %BuildIndices and %PurgeIndices methods? The examples I've found are so complex, I'm not even sure where to start.

score 0 · Answer 12 · 2020-09-15T14:19:53-04:00

Keith Davis · Sep 15, 2020

Bump.

0 0

score 1 · Answer 13 · 2020-09-15T16:38:00-04:00

Have you ever considered asking ADSC for help ?
Do you have some maintenance contract with them as it seems to be their code.?
With a COMPLETE class definition and some sample data, it shouldn't be magic.

score 0 · Answer 14 · 2020-09-15T16:49:14-04:00

I have and am right now in the process of asking again, but they have not been helpful. What do you mean by "a COMPLETE class definition"? What they provided was class definitions based on our requirements (which are pretty specific).