updating a lot of UD Text# fields is taking forever, what is a faster way?

[rmichaelsmi]

What I have found is that using the BRApi.Finance.Members.GetMemberInfo or Metadata.GetMember calls with True for includeProperties or even the first call to BRApi.Finance.UD.Text(...) all incur an average of just over 2 seconds and we are looping through 27,000+ projects so 15 hours to check and update the text fields. It seems that it gets cached either in OneStream or the SQL server so a second call done in the update function is no additional cost. The Dimension Library object obviously does this a much faster way, maybe by specifying the individual properties wanted in one of the optional objects versus all properties?

There has to be a faster way.  When using the Dimension Library and setting Grid View's Settings to NOT display parent child relationships, you gain access to the Text# fields.  You can update a lot of these at one time FAST.

I have even updated a function I used to add calling the GetMembersUsingFilter with the option to include All properties and the memberinfo for them…  using the below code.  This MAY have helped by halving the time but I am not sure that wasn’t just a result of caching.  I also passed in the MemberInfo Object by Reference into the function Updating the text fields instead of making it get the object again.  I think this made some difference but still takes forever.

Dim includeLocalDescription As Boolean = False
Dim culture As String = ""
Dim includeAllDescriptions As Boolean = False
Dim includeAllProperties As Boolean = True
Dim includeMemberInfoForProperties As Boolean = True
Dim includeMemberDim As Boolean = False
Dim includeSupportsChildren As Boolean = False
Dim parentIndentLevel As Boolean = False

Dim mbrDisplayOptions As New MemberDisplayOptions( _
   includeLocalDescription, culture, includeAllDescriptions, includeAllProperties, _ 
   includeMemberInfoForProperties, includeMemberDim, includeSupportsChildren, _ 
   parentIndentLevel)

'Execute the filter and check the result
Dim memList As List(Of MemberInfo) = BRApi.Finance.Metadata.GetMembersUsingFilter(si, _
   dimensionName, memberFilter, True, , mbrDisplayOptions)

EDITED: Additional context:  The source of our data is an external system.  It is providing the data for Text1-5 and we pull using SQL from a table.  We are looping through the projects comparing the Text fields and updating any that have changed. 

[JackLacava]

Chances are that the Dimension Library does some caching when the application is first open, and/or uses internal methods to speed up things. It then probably uses something like GetAllMembers.

2 seconds for a GetMemberInfo seems a bit high anyway... do you use IDs?

[rmichaelsmi]

See above EDITED section for some more context.  We do an initial pull from OneStream using the GetMembersUsingFilter in which we get the MemberInfo Object and use IDs from there.  That function is very fast.  The slow part is in retrieving and updating the Text Fields.  

Depending on which way I tried, calling the Members.GetMemberInfo or the Metadata.Member with the includeProperties flag set to True takes the nearly 2 seconds.  I tried using GetUDProperties or using UD.Text() both and all were hit some where with a 2 second cost.

[JackLacava]

I wonder if there is some corruption somewhere, like a table that lost its indexes, or a lot of orphans, or a hierarchy that is particularly bonkers. Maybe some optimization could be done at the db level, logging the queries  to determine where the slowdown is (you can use ReadMemberPropertiesNoCache to ensure you're hitting the database every time).

In any case, if the api really is that slow, you can speed things up by doing the reading part via SQL directly on the MemberProperty table (you just have to figure out the PropertyID for Text5, but that should be easy enough), and then use the api only for the specific members that need changing.

[rmichaelsmi]

I timed around every call and it is the ones I shared.  We pull the ids so we know those.  Hitting the backend just to see what has changed may be the way we have to go.  I do not want to update that way as there is supposed to be history, logging, etc taking place and SOX would likely not like the direct updates.  WIll see what this buys us.  In any event, this still does not address the 2 second call per member which is terrible.

[JackLacava]

I agree those 2 seconds don't seem normal to me. You should probably take it up with support - to speed up the experience, you might want to build the smallest Business Rule that can trigger the 2secs wait, so they can take it and run it on reference environments to figure if it's a bug or an environmental issue - in which case, they will want to look at your app. Good luck!

[RobbSalzmann]

Is your requirement to show a grid view with 27,000 members? 
What is your requirement for looping over 27,000 projects?

I would consider finding a way to do this with SQL and Data Management.

[rmichaelsmi]

See new EDITED context.  We pull the text field values from a sql table and are comparing to what is in OneStream and updating.  The text field values are from an external source.

[RobbSalzmann]

The one thing that comes to mind is a UD dim with 27,000+ (and growing?) members.  If you have just 10 more members in any other UDs, you're pushing the optimal Data Unit size before even thinking about how many accounts you have.

Have you considered keeping these projects in a SQL table created in the application database and using a hybrid approach?

If you want to post your code we might bable to provide extra eyes to help optimize your code.  The performance difference between passing byref and byval at the current number of iterations will be almost imperceptible.  

[photon]

Have you tried using something like BRApi.Finance.Members.ReadMemberPropertiesNoCache? I'm using one of the adjacent functions and I'm able to compare descriptions from our source system to OS in no time at all. Saving an update costs about 1 second but if there are no changes it takes just a few seconds to run through ~5,600 source records.

[rmichaelsmi]

I had looked at that briefly, but wasn't sure of the true impact of the nocache.  Sounds like it would be worth a try as I would expect most of the Text fields to remain somewhat static once initially corrected.  Limiting the cost to only when an update is needed is definitely a goal.  WIll let you know.

Did you also try ReadWriteableMemberNoCahce, by chance?

[photon]

Yup! That's the start and I then expose VaryingMemberProperties and then EntityVMProperties to check/update a whole bunch of properties.

[photon]

Although, I guess I should point out that the reason I didn't suggest my particular method is because the snippets around this method come pre-populated with warnings about how it doesn't update the metadata updates table and you mentioned this might annoy your SOX auditors. Of course, SOX auditors love to audit things that have nothing to do with SOX and they shouldn't even be looking at but that's a little off-topic. If you need the official metadata update tables to be kept in sync then you could write code to update them (I should probably look into this for our purposes.) Alternatively, create your own table to track these updates separately or (as I have done) just write all updates out to a CSV and email them.

The first time I ran this in our Dev environment, it updated 5,000 members in about an hour. Since then, it mostly makes 0 updates and runs in 15 seconds.