Is there a way to prevent building indices when inserting records via `%Save()`

Question

Question

Michael Fortunato · Oct 25, 2022

#Key Question #SQL #Caché #InterSystems IRIS

Is there a way I can call %Save() on a %Persistent object and at the same time prevent indices from being built?

Discussion (19)7

Log in or sign up to continue

Joel Solon · Nov 1, 2022

Thanks Michael.

You could code it the oop way, and use $sortbegin on all the index globals for your multiple tables, which means you'd have to know or look up what the index global names are (since they're not always ^D and ^I anymore), and test 1000000 inserts (main table and referenced tables) with the index build deferred to the end, and time it to see how long it takes.

And then code it the sql way, inserting the same data into the multiple tables using %NOINDEX, and calling %BuildIndices() on all the classes at the end, and time it to see how long that takes. The sql way is supposed to be faster...

0 0

score 2 · Answer 1 · 2022-10-25T18:29:58-04:00

Why would you want to do this?
the index related to %Save() is no "build" but just setting a value to the index global.
setting an index is mostly less load than saving an object.

score 1 · Answer 2 · 2022-10-26T01:03:11-04:00

Alexander Koblov · Oct 26, 2022

No

1 0

score 0 · Answer 3 · 2022-10-26T06:02:42-04:00

I assume that you want to skip one (or more) indices for specific values.
You may use an "after" trigger to manually delete an index based on some of the properties.
For this approach, you will need to change this trigger, every time you add/change a property or an index.

score 0 · Answer 4 · 2022-10-26T07:15:19-04:00

Hi Michael,

I'm afraid the only way is to insert the record using SQL and use the %NOTRIGGER and %NOINDEX keywords to prevent the actions.

score 0 · Answer 5 · 2022-10-26T09:51:07-04:00

Herman Slagman · Oct 26, 2022

You can have a look at %Library.FunctionalIndex

0 0

score 0 · Answer 6 · 2022-10-26T10:59:47-04:00

You could do something truly horrible and disgusting with the storage definition, like:

<IndexLocation>@$Select($D(%SuppressIndexFile,0):"foo",1:"^DC.Demo.SuppressIndexFileI")</IndexLocation>

But that would be truly horrible and disgusting. (Echoing Robert Cemper's comment, knowing why would help!)

score 7 · Answer 7 · 2022-10-26T11:49:53-04:00

Hi Michael,

Maybe you are reproducing some application migration issue and are setting up some test data.

Purely as a "diagnostic" it seems possible to use the $SORTBEGIN and $SORTEND ObjectScript functions to effect this potentially without changing existing code.

Basically you can ringfence specific globals (normally for performance rationale) and transiently store the global writes in buffer. Then at end of "experiment", you can decide what to commit. ie: Commit the data writes but throw away the index writes.

Hope this helps.

Class TEST.Claim Extends %Persistent
{ Property PropA As %String; Property PropB As %String; Index ixdPropA On PropA; Index ixdPropB On PropB; /// Do ##class(TEST.Claim).TestWithIndex()
ClassMethod TestWithIndex()
{
K ^TEST.ClaimD,^TEST.ClaimI
for i=1:1:10 {
set obj=..%New()
set obj.PropA="PropA V"_i
set obj.PropB="PropB V"_i
do obj.%Save()
}
zw ^TEST.ClaimD,^TEST.ClaimI
}/// Do ##class(TEST.Claim).TestWithoutIndex()
ClassMethod TestWithoutIndex()
{
K ^TEST.ClaimD,^TEST.ClaimI
i $SORTBEGIN(^TEST.ClaimD) // Buffer the writes
i $SORTBEGIN(^TEST.ClaimI) // Buffer the writes
for i=1:1:10 {
set obj=..%New()
set obj.PropA="PropA V"_i
set obj.PropB="PropB V"_i
do obj.%Save()
}
i $SORTEND(^TEST.ClaimD,1) // Keep data
i $SORTEND(^TEST.ClaimI,0) // Throw Indexes away
zw ^TEST.ClaimD,^TEST.ClaimI
} Storage Default
{
<Data name="ClaimDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>PropA</Value>
</Value>
<Value name="3">
<Value>PropB</Value>
</Value>
</Data>
<DataLocation>^TEST.ClaimD</DataLocation>
<DefaultData>ClaimDefaultData</DefaultData>
<IdLocation>^TEST.ClaimD</IdLocation>
<IndexLocation>^TEST.ClaimI</IndexLocation>
<StreamLocation>^TEST.ClaimS</StreamLocation>
<Type>%Storage.Persistent</Type>
} }

Kind regards,

Alex

Update: For clarification the $Sortbegin on the ^*D global was simply to demonstrate different possible outcomes. The actual solution only required to discard the index ( ^*I) updates.

score 1 · Answer 8 · 2022-10-26T12:56:04-04:00

Timothy Leavitt · Oct 26, 2022

This is amazing.

1 0

score 1 · Answer 9 · 2022-10-26T22:28:22-04:00

Evgeny Shvarov · Oct 26, 2022

Yet another majestic thing about ObjectScript. Wicked cool.

1 0

score 0 · Answer 10 · 2022-10-27T12:35:39-04:00

Robert Cemper · Oct 27, 2022

I fail to imagine how to do this with ePy

0 0

score 0 · Answer 11 · 2022-11-01T09:43:08-04:00

@Alex Woodhead Why are you using $sortbegin on the ^D global? Just to show it's possible in this demo? Or are you recommending that general approach? It seems to me that the solution to the original question would be allowing the data to save normally (not using $sortbegin on the ^D global), and using $sortbegin on the ^I global and discarding those updates.
@Michael Fortunato Please tell us the reason for wanting to do this. It can't be that you want the index to be permanently out of sync with the data. Perhaps it's for performance for many inserts? If so, switching to a SQL approach with %NOINDEX (as @David.Satorres6134 suggested) is a good idea.

score 0 · Answer 12 · 2022-11-11T05:17:30-05:00

Hi @Joel Solon
Yes the $Sortbegin on the ^*D global was simply to demonstrate different possible outcomes. You are right the solution only requires to discard the index ( ^*I) updates.
I will update my above reply.

score 1 · Answer 13 · 2022-11-01T11:09:06-04:00

To elaborate:

1) I wanted to use the object interface because it handles multi table inserts when my %Persistent object has properties that are also %Persistent.

2) I wanted to prevent indices from being updated on %Save so that my bulk inserts would be faster. I can then defer the building of my indices after my bulk insert is done. I was aware of %NOINDEX but because of point 1) I was hoping to take an oop approach.

score 0 · Answer 14 · 2022-11-03T07:37:34-04:00

I understand your answer (#2) the way as this is a one time job. The simplest and quickest way to do this is:

- disable all indices (comment them out),
- compile the class or classes
- do the bulk insert
- enable all indices (by removing comment markers)
- compile the class or classes
- rebuild the indices

Class My.Class
{
    /*  disable all indices
    index1 someindex1 on someprop1;
    index2 someindex2 on someprop2;
    */
}

score 0 · Answer 15 · 2022-11-03T09:13:56-04:00

Following this discussion since beginning I'm wondering about the variant I've learned.
my personal approach would be.
#1)
have an exact copy of your class also pointing to the same storage
remove all index definitions from that clone class
run your bulkload
#2)
if you don't want to have the same storage
just eliminate the index definitions in the copy
clean its storage and run your bulk load
finally, merge the resulting globals

#' .)
It's not pure OO,
but writing to storage is never OO with Caché or IRIS

score 0 · Answer 16 · 2022-11-08T13:38:18-05:00

Developer Commu... · Nov 8, 2022

💡 This question is considered a Key Question. More details here.

0 0

score 1 · Answer 17 · 2022-12-08T04:24:42-05:00

I know I'm late to the game, but a few notes I thought were worth mentioning in the context of the question and the discussion here -

1. Re using $sortbegin/$sortend - if you want to take this approach, there are dedicated methods on all %Persistent objects that do exactly this - %SortBegin() and %SortEnd(). Note you can use this per index (or for all) and you don't have to worry about the name of the global etc. So this should definitely be preferred IMHO.

By the way, if you are interested, you can see that the "behind-the-scenes" of %Populate's Populate() method uses this approach (a little advanced generator code...), as well as %SQLImportMgr's GenerateImportRoutine() method.

2. I don't know if this is relevant for your case, but also for the benefit of the wider community and other use-cases, when it comes to bulk/batch fast operations, if Java or .Net are relevant, then our XEP (eXtreme Event Processing) framework could be a good option. And there indeed you have control over when to perform the indexing.

3. I know the discussion was geared towards OOP and not SQL (and the SQL %NOINDEX option was mentioned more than once), depending on how the data is arriving, you might want to consider using the latest (SQL-based) LOAD (BULK) DATA capability.

4. Relating back to #2 above, re XEP. Behind the scenes the XEP access uses (in some cases) a server-side %SaveDirect() method. In theory you can use this yourself. Note it does not address the no indexing topic (again you can consider using #1 - %SortBegin() and %SortEnd() for that) but it has some other performance advantages as apposed to a regular %Save() (it also has some limitations...). Note there is quite an amount of "hassle" about "preparing" the data for this method, as it needs to be in the $ListBuild format the class's storage expects (and possibly other data nodes). Therefore it is quite rarely used directly (not via XEP). But if you have high performance needs, it might be worth considering this, and comparing the benefit it could provide you vs. the drawbacks it has.

score 0 · Answer 18 · 2023-01-05T08:15:48-05:00

Make the index that you wish to build unavailable to queries (READ access). This is done using SetMapSelectability(). This makes the index unavailable for use by the Query Optimizer. This operation should be performed both when rebuilding an existing index and when creating a new index. For example:

WRITE $SYSTEM.SQL.SetMapSelectability("Sample.MyStudents","StudentNameIDX",0)