Forum Discussion

rmichaelsmi's avatar
rmichaelsmi
New Contributor II
2 years ago

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

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. 

 

Admin
Extenders
Finance
  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago

    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's avatar
      rmichaelsmi
      New Contributor II
      2 years ago

      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's avatar
        JackLacava
        Honored Contributor
        2 years ago

        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.

         

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    2 years ago

    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's avatar
      rmichaelsmi
      New Contributor II
      2 years ago

      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's avatar
    RobbSalzmann
    Valued Contributor II
    2 years ago

    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's avatar
    photon
    Contributor
    2 years ago

    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's avatar
      rmichaelsmi
      New Contributor II
      2 years ago

      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's avatar
        photon
        Contributor
        2 years ago

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