regarding performance for local variable

How do you know that the query is being called again?  Do the values change?  Or are you just seeing a large number of milliseconds?

How did you set the refresh of the union() based local variable?  Are you doing the union operation every turn?

a!localVariables(
local!addEditGridSelection: a!gridSelection(
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 10,
sort: a!sortInfo(
field: "issuerPublishedReportingName",
ascending: true
)
),
selected: null
),
local!excel: a!refreshVariable(
value: if(
isnull(
ri!document
),
{},
readexcelsheet(
excelDocument: ri!document,
sheetNumber: 0,
startRow: 1
).result
),
refreshOnReferencedVarChange: false
),
local!columnOne: a!refreshVariable(
value: a!forEach(
local!excel,
index(
fv!item.values,
1,
null()
)
),
refreshOnReferencedVarChange: false
),
local!excelColumnOne: a!refreshVariable(
value: touniformstring(
local!columnOne
),
refreshOnReferencedVarChange: false
),
local!associatedCompanies1: a!refreshVariable(
value: rule!PROXY_PXY_watchlistProcessExcelData(
excelData: local!excelColumnOne
),
refreshOnReferencedVarChange: false
),
local!associatedCompanies: union(
local!associatedCompanies1,
local!associatedCompanies1
),
local!notPresentList: reject(
fn!isnull,
difference(
local!excelColumnOne,
local!associatedCompanies
)
),

{................}

)

I don't see any obvious errors. And if "ri!document" won't be changing dynamically, then none of the "refreshOnReferencedVarChange: false" calls should even be needed, since (as far as I can see) there isn't any other way that those would try to refresh.

As a personal best practice I always set up a utility rule in my environment to handle deduplication of a given array, i.e. rule!GLBL_removeArrayDuplicates() which just does union(ri!array, ri!array) inside, so that in my interface I don't have to declare a dummy variable (local!associatedCompanies1) just to union it to itself in the "Real" variable -- instead the query itself could be wrapped in the above helper rule.

Yeah seems fine to me. I'd still suggest using union(local!associatedCompanies1, touniformstring({})). If you're really worried about performance that should make a minor difference. Otherwise, I'd suggest looking into the supporting rule that generates the results for local!associatedCompanies1 to see if there are any improvements you could make there.

Awesome suggestion, Peter.  If at all possible, you're more likely to get way better performance out of convincing your data source to eliminate the duplicates for you, rather than asking Appian to do it after you've already ferried all the extra data over.  Returning the smallest amount you can from DB is a standard best practice.

Of course it ultimately depends on how your expression gets the data in the first place.