How to optimize a plug-in that calculates a rollup?

Like (0) Share

Report

Posted on 31 Oct 2023 22:43:55 by aepe_mmc

Hi everyone,

I've been wracking my brain trying to figure out the best solution to a problem I keep running into over and over. My company loves complicated rollups. Sometimes they can be accomplished by using the OOTB rollup, and sometimes not. In my scenario it is a calculation based on two aggregates that rolls up into an option set value:

Total "Green" Child Accounts / Total Child Accounts

If Total Green = 0, then Non-Customer

If Total Green > 0 and Total Green < Total, then Partial Customer

If Total Green = Total, then Full Customer

My current solution is to have a plug-in that triggers when the green field on the child account gets updated to recalculate the parent's status. However, the plug-in sandbox worker crashes, and I'm not sure why. All of my unit tests pass, so my best theory is that there are too many child records getting updated at the same time. Does anyone have any advice on how I could optimize this plug-in or change direction to make the behavior more consistent? Right now, I'm running a SQL script to identify and clean up the data discrepancies caused by the failures, but that's not sustainable.

All responses (5)

Answers (1)

aepe_mmc 17 on 01 Nov 2023 at 16:24:18

Like (0)

Report

Re: How to optimize a plug-in that calculates a rollup?

Yes! I wasn't aware of Power Automate work queues. Thanks for the links! I think I could convert the code I've written in the plug-in to a custom API that gets called one time on delay like you've described.
Verified answer

cchannon 4,702 Moderator on 01 Nov 2023 at 16:00:42

Like (0)

Report

Re: How to optimize a plug-in that calculates a rollup?

One option here is to use the new Power Automate Queueing mechanism. You can write all your transactions to a queue, then have one power automate flow (on a delayed trigger - NOT the Wait Action) pull all the messages off queue and process them in bulk.

Power Automate Work Queues:

Work queues - Power Automate | Microsoft Learn

Dataverse Delayed Trigger:

https://learn.microsoft.com/en-us/power-automate/dataverse/create-update-delete-trigger?tabs=classic-designer#wait-condition-using-delay-until
aepe_mmc 17 on 01 Nov 2023 at 15:52:47

Like (0)

Report

Re: How to optimize a plug-in that calculates a rollup?

Thanks all for the replies. I wasn't sure whether there was a way to delay the execution of my plug-in until the last record in a group gets updated. I still don't know the best way to approach this problem. I agree on the sentiment that the OOTB rollup doesn't cut it for most use cases, which is why I've been looking for an alternative. My calculations aren't taking too long, but I get these errors pretty frequently. There's nothing special about my logic. It's just two Linq statements that aggregate the data and then does the comparison. I was reading Microsoft's best practices and they say that we should target to have our code execute in 2 seconds. 😐
steeevid 321 Super User 2024 Season 1 on 01 Nov 2023 at 13:37:33

Like (0)

Report
Re: How to optimize a plug-in that calculates a rollup?

1. Power Automate with fetchXML aggregation for roll up and aggressive parallelism
How: trigger from child entity change -> fire parent flow and send parent GUID -> child flow run pre-defined fetchXML aggregation and if you have multiple same condition attribute, do them in the same fetchXML, if your aggregation have different filter conditon, do them in parallel action. -> then update parent entity. Recent why I seperate child flow and parent flow is that I have a complex business logic, i have cascade rollup across multiple level of tables and if condition on a parallel cascade stream changed, the rollup condition will also change, so I need to seperate parent and child flow so i can re-use calculation in different flows
Pros:
not actually slow, execution is generally 500ms for small data set and 1s for large dataset, since fetchXML is like native SQL.
Easy to build, low code solution
Naturally support parallelism
Cons:
No pre-image option, meaning that if you "DELETED" a row then the triggered action will not contain parent GUID, and your flow cant run / get error
Or if you changed a row's critical lookup value, then the previous lookup value's parent entity will not be updated because power automate won't know
Therefore only suitable for "Add" and "modify" trigger, and you need to direct your user to not use "delete" feature in app, ask them to use "deactivate" instead, and you treat deactivation in your flow is like a soft delete
And you need to stop user from changing essential lookup value from both form and grid, this could be hard depend on business
For complex roll up condition / cascade roll up / roll up that depend on parallel entity attributes, etc, the Parent -> Child network will get very complicated and you need to draw yourself a flow network diagram to navigate your way out
Power automate seems to have a cache system, if you flow didn't be used in 2-3 days, it take ages for it to run for the first time in few days, and it sometimes delay in starting so that you thought it's totally broken.
You need integrate a log system inside each flow, maybe write to a dataverse table so you can check flow run status, flow run link and "regarding" entity, so to monitor them. Power automate doesn't have a centralise place for this
2, Plugin:
How: you know how
Pros:
Fast in responding
can get pre-image so you won't lose any information about your trigger condition
write code locally, no headache jummping from different power automate flow and waiting webpage load to edit them
Cons:
No parallelism
2min hard shut off limitation
tedious server side debugging
3. Rollup column
This is a complete garbage and bullsh3t, roll up every 12 hours if not manually trigger you kidding me
4. Azure function
How: set up pay as you go on azure, write code on azure function and link to dataverse, it's like plugin
Pros: does not come with the limitation of a plugin
Cons: EXPENSIVE
cchannon 4,702 Moderator on 01 Nov 2023 at 00:58:40

Like (0)

Report

Re: How to optimize a plug-in that calculates a rollup?

Yeah, plugins have a hard cap at 2 min execution time. If your logic takes that long to get through, Dataverse will just drop your plugin execution straight into the trash.

Maybe try accomplishing the same thing with Power Automate flows instead? They aren't particularly fast, but they do offer a lot or parallelization options and if you're a bit creative about parallelization and child flows, you can get a ton of calculation done in a hurry. Most importantly, they don't time out like a plugin (I think there is some cap, but it is much much bigger).