Discussion (19)7
Log in or sign up to continue

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()
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()
$SORTBEGIN(^TEST.ClaimD)  // Buffer the writes
$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()
$SORTEND(^TEST.ClaimD,1)  // Keep data
$SORTEND(^TEST.ClaimI,0)   // Throw Indexes away
zw ^TEST.ClaimD,^TEST.ClaimI
} Storage Default
<Data name="ClaimDefaultData">
<Value name="1">
<Value name="2">
<Value name="3">
} }
Kind regards,


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.

@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 Satorres suggested) is a good idea.

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. 

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...

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;

Following this discussion since beginning I'm wondering about the variant I've learned.
my personal approach would be.
have an exact copy of your class also pointing to the same storage
remove all index definitions from that clone class
run your bulkload
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 

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.

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)