List Constituents - greater than 5000 records?

Options
Chris Zello
Chris Zello
Fifth Anniversary Kudos 2 Name Dropper Participant
in SKY Developer General Discussions

If a list has more than 5,000 records, and my Limit has already been raised to 5,000, what are my options to get a flow to work on a larger list?

Tagged:

Comments

  • Matt Thacker
    Matt Thacker
    Fourth Anniversary Kudos 3 Name Dropper Participant

    Hey @Chris Zello, this is how I'd go about solving this (there's probably easier ways).

    I know the image might be hard to see, here's a link to the full size image.

    Essentially, start by initially two variables, an Offset number, and Constituent Array.

    1. Execute the first List Constituents action with the max number of constituents in the limit field (5,000) and the Offset variable value as the Offset.
    2. Put the return value of that action in an Apply to Each and append each value to the Constituent array.
    3. Outside of the Apply to Each, increment the Offset variable by 5,000
    4. The next is a Do While step. In the left hand side of the Do While condition, use the expression empty(outputs('List_constituents')?['body/next_link']) and the right side is ‘true’
    5. Do another List Constituent action with the Limit and Offset Variable
    6. Iterate over the returned values and append to the Constituent array
    7. Increment the Offset by 5,000

    So this will continue until there the flow reaches the last page in pagination. After this section, you can work with the Constituent array in the rest of your flow.

    You might want to add a Parse JSON action after this section so that you can work with the attributes of the array, rather than having to hardcode them into your flow actions.

    f1d74e706565721d61a8583666c1f041-huge-im


  • Alex Wong
    Alex Wong Community All-Star
    Ninth Anniversary Kudos 5 PowerUp Challenge #3 Gift Management Name Dropper

    @Chris Zello
    here's a post I did a while back:
    https://community.blackbaud.com/forums/viewtopic/426/60417

    it has a flow you can import, while it is for gift table, it has the concept of handling more than 5000 records in there, how to do loop until you got all the records, though it doesn't use the offset that Matt said.

    Import it, take a look and see if you can grasp the concept of it.

    You can also follow Matt's instruction, but I would advise against step #2 Put the return value of that action in an Apply to Each and append each value to the Constituent array.

    putting each constituent record into an array using apply to each means you are looping 5000x for each 5000 records, this will take a long time. Instead, you can either

    1. do one list constituent action which is inside of the do until directly, and do the processing you want to do directly in the do until loop
    2. use compose to join what's in the array variable using the union expression with the new list of 5000 records
      1. initialize constitent array as empty array []
      2. after the list constituent action, use Compose action to union the constituent array with the array from list constituent (body/value)
        union(variables('ConstituentArray'), outputs('List_constituents')?['body/value'])
      3. then set variable action to set the constituentarray variable with the now combined array from the compose action.
    dcff848dc54fcde9bbc3069f9c030cf8-huge-im
    a2524b9b3ad8d39ecf8a592d6c2264e4-huge-im
  • Matt Thacker
    Matt Thacker
    Fourth Anniversary Kudos 3 Name Dropper Participant

    @Chris Zello, I'd probably use @Alex Wong format as it's a bit cleaner to implement out of the box.

    But from a performance perspective, Union is no more efficient than an Apply to Each over each entry. Both forms have the same Big O, O(n+m). Union will run faster, all things equal, but it has to do the same steps regardless: concat the arrays, check for duplicates, drop duplicates (if found). These have to be run synchronously.

    However, with the Apply to Each method, we know all of the IDs in an array returned are going to be unique so there's no need to care about the order in which the values are appended to array. So we can utilize the Concurrency Control for the Apply to Each rather than running it synchronously. Even setting the Degree of Parallelism to 20, my flow ran 68% faster than the original one I posted.

    bd5356b2da25ece0f9d59446611fbb95-huge-im

  • Alex Wong
    Alex Wong Community All-Star
    Ninth Anniversary Kudos 5 PowerUp Challenge #3 Gift Management Name Dropper

    @Matt Thacker
    Yes, concurrency will increase the speed Apply to Each runs, however, it is still processing each constituent one at a time, the overhead of running loops is heavy.

    15b416e1669024f38dcedd840b269711-huge-im
    1660eca536bf0bffc8b96c2f06689762-huge-im

    With 20 concurrency setting, a 500 record limit took 36s, while a 5000 record limit took 5 minutes, just about 10x (directly proportional to # of records.

    However, using union and set var does not need apply to each and the 2 actions only takes 3s total, for both 500 records, or 5000 records

    cb6bb7a97ef28fc19e4ab54ea704ccfc-huge-im

    Like other data opertion and expression in power automate, they avoid the need to loop and save tons of processing time. (i.e. use Filter Array action instead of apply to each to condition on field to find a record; use Select action to select a property of an object in an array of object instead of apply to each and then get the property from each object; etc)

  • Ben Regier
    Ben Regier
    Sixth Anniversary Kudos 3 Name Dropper Participant

    Loop performance is one of the parts of Power Automate that makes me want to tear my hair out.

    Yes, using a union() expression is going to be significantly faster than looping over your result set. Why? No one knows.

    There's no intuitive reason union() should be that much faster - creating a loop and adding each item to a variable is a simple, straight-forward process. It should be fast, especially if you have the option of increasing concurrency. It should certainly be comparable to using a union() expression, which has to do comparison in addition to appending to an array. But instead, looping is an order of magnitude slower!

    A loop is a core tool in a programmer's toolbox, but I find myself writing flows that are much more complex than they need to be just so I can avoid loops (and especially loops within loops) to increase performance. It's a bizarre thing to have to work around.

    Sorry, rant over.

  • Alex Wong
    Alex Wong Community All-Star
    Ninth Anniversary Kudos 5 PowerUp Challenge #3 Gift Management Name Dropper

    @Ben Regier
    Actually there is a reason why.

    when using a loop, esp in Power Automate, there is a LOT of overhead, some you see, some you don't. For example, every loop, every action has to be recorded, each dynamic content (input json, output json), loop index, what loop are we in, etc etc. PA has to track a lot of information and run history.

    BTW, concurrency should be used in care, you do not want to result in a “timing synchronization" issue where something is overwritten when not supposed to.

    However, when using an action or expression that operates on the full array, PA is not tracking anything other than what is the input and resulting output.

    In this example:

    every loop, you are tracking append 1 constituent into the array, so every loop has the array with the existing records, then add 1 more records.

    meanwhile, in using union expression, the “backend” work to join 2 array together can use various speed-up programming methodology (i.e hashing, various greedy algorithm applied, etc), meaning it employes smarter joining of the 2 array behind the scene. (union also removes any duplicate object that is exactly the same, which is good and bad depending on your need).

    ADDED NOTE: by the way, there is one more drawback other than speed and that is # of actions in the flow, which can trigger a throttling of the flow. If you got 50K constituent to append to the array variable, that would be 50K actions, whereas the compose union + set variable is only going to be 10 actions total.

  • Ben Regier
    Ben Regier
    Sixth Anniversary Kudos 3 Name Dropper Participant

    @Alex Wong
    There's a reason why, yes, but my point is that the overhead that Power Automate decided to add to loops creates a scenario where loop performance is much worse than any new user would rationally expect it to be. So bad, in fact, that part of learning to use Power Automate well involves learning a series of workarounds to avoid using loops even when a loop would be the simplest solution.

    I just wish different decisions had been made to prioritize loop performance over things like run history detail.

  • Matt Thacker
    Matt Thacker
    Fourth Anniversary Kudos 3 Name Dropper Participant

    I think Ben is spot on. I think Apply to Each gets a bad rap because it's slow. But the trade off is that's it's easier to understand the logic of what is happening and easier to troubleshoot/maintain. Power Automate is supposed to be a low barrier of entry into automation for “citizen developers.”

    The hill that I will die on is that Power Automate should not be a part of infrastructure for production data pipelines. When efficiency and timeliness is business critical, actual programming tools should be used.

Categories