Avoid Aggregate Looping

Let’s say you encountered a situation where you must(or happened to) write logic like this when you cannot join the table for any reason :

Aggregate inside for loop Aggregate inside for loop

Often times, you can easily join the two table together to return the data you needed. But there’s several condition where you cant join the table because several reason like the table is on another server or source data for the list was a List datatype.

This example should be avoided at any cost. The reason for avoiding this type of logic is it could lead to performance issues, if the data is somewhat large, it will cause timeout when calling this action.

There are several ways for you to rewrite this type of logic to improve application performance such as using Advanced SQL with “IN”, or using index() function to search the data.

Method 1 : Using Index() function

Perhaps you have some function that takes Site list as input parameter and ticket list as the output.

Using the Low Code mindset, you will create logic like the picture above. However, its not best practice for application performance. To improve this, we can rewrite the logic so it doesn’t loop the aggregate

Joining SiteId as single string

Purpose of this new variable is to store site id list in new format. We can convert the data using StringJoin() function and set separator with “;” (you can use any other symbol as separator)

So the data would look like ABC123;ABC124;ABC125;ABC188

Now we can modify the filter in aggregate.

| Before, we filter by site id and loop the aggregate

To this :

After, use index function

So we can modify the logic to this

Removed loop and change ListAppend to ListAppendAll

Method 2 : Using “IN” in Advanced SQL

First, we must add the dependency from “Sanitization” module and assign SiteID list as input to the action.

Then, create query using Advance SQL like this with input parameter text and expand inline set to “Yes”

Don’t forget to set “Expand Inline” properties to Yes

And the final result will be like this