How to make formula more efficient

[moerah]

Hi all 

need help with the formula below to be more efficient, how would I simplify this 

"OnStart"

Concurrent
(ClearCollect(ColPostCode,ShowColumns(Filter(THPCLocality,NumberID>=1&&NumberID<=2000),"Title","field_0","field_2")),
ClearCollect(ColPostCode2,ShowColumns(Filter(THPCLocality,NumberID>=2001&&NumberID<=4000),"Title","field_0","field_2")),
ClearCollect(ColPostCode3,ShowColumns(Filter(THPCLocality,NumberID>=4001&&NumberID<=6000),"Title","field_0","field_2")),
ClearCollect(ColPostCode4,ShowColumns(Filter(THPCLocality,NumberID>=6001&&NumberID<=8000),"Title","field_0","field_2")),
ClearCollect(ColPostCode5,ShowColumns(Filter(THPCLocality,NumberID>=8001&&NumberID<=10000),"Title","field_0","field_2")),
ClearCollect(ColPostCode6,ShowColumns(Filter(THPCLocality,NumberID>=10001&&NumberID<=10477),"Title","field_0","field_2")),

ClearCollect(colCountrecords,ShowColumns(NWAllocation,"EnteredBy")));ClearCollect(colAllPostCode,ColPostCode,ColPostCode2,ColPostCode3,ColPostCode4,ColPostCode5,ColPostCode6);

ClearCollect(colMainData,ShowColumns(NWAllocation,"EighttotwelveMonths","FifteenMonth","NewDueBy","SixtoEightWeeksdueBy","ThreetoFourMonthsDueby","TwoYears","ID"));

ClearCollect(colMainDataCombined,AddColumns
(AddColumns
(AddColumns
(AddColumns
(AddColumns
(AddColumns

(colMainData,"NB Days to go",If(DateDiff(Today(),DateValue(NewDueBy))<=0,"0",Text(DateDiff(Today(),DateValue(NewDueBy))))),

"sixtoeightdaystogo",If(DateDiff(Today(),DateValue(SixtoEightWeeksdueBy))<=0,"0",Text(DateDiff(Today(),DateValue(SixtoEightWeeksdueBy))))),

"threetofourdaystogo",If(DateDiff(Today(),DateValue(ThreetoFourMonthsDueby))<=0,"0",Text(DateDiff(Today(),DateValue(ThreetoFourMonthsDueby))))),

"eighttotweldaystogo",If(DateDiff(Today(),DateValue(EighttotwelveMonths))<=0,"0",Text(DateDiff(Today(),DateValue(EighttotwelveMonths))))),

"fifteenmonthstogo",If(DateDiff(Today(),DateValue(FifteenMonth))<=0,"0",Text(DateDiff(Today(),DateValue(FifteenMonth))))),

"twoyearstogo",If(DateDiff(Today(),DateValue(TwoYears))<=0,"0",Text(DateDiff(Today(),DateValue(TwoYears))))));

 Thank you 

[BCLS776]

This looks like something that could be run in 3-5 seconds in a Power Automate flow - have you considered doing that? You can load 5000 rows at a time there, and depending on your app's needs return a filtered subset of rows for it to use.

Otherwise, I would start by replacing the Today() function with a variable at the start of the OnStart code:

Set(varToday, Today())

Today() looks like it is being evaluated 12 times per row in the main collection. 

Also, consider if you need this collection created in OnStart, or if you can move it to another place in the app. Even an OnVisible property of the screen it is needed on could be a significant app startup improvement.

Hope that helps,

Bryan

[BCLS776]

Also, consider replacing expressions such as:

If(DateDiff(Today(),DateValue(SixtoEightWeeksdueBy))<=0

With:

If(SixtoEightWeeksdueBy <= varToday // and so on

which is less computationally-intensive than your original expression.

Bryan

[moerah]

Thank you @BCLS776 for the reply 

I'm not too familiar with power automate just yet,

records are created based on user input then archived monthly which wont reach 2k records, for now I'm just focusing on max performance for the app. 

appreciate the good practice suggestion 👍

will it be possible to move codes i have from onStart to formula properties? 

[BCLS776]

Unfortunately, named formulas in Power Apps do not yet support Collect() or ClearCollect() - those need to be in behavior properties (one that starts with "On").

I don't know the full purpose of your app, but if this is for some periodic archiving of records again consider using Power Automate. You can use a recurrence trigger on there to run a flow once a day, once a week, or whenever you need to do things like archiving for you with zero user interaction.

Nearly every canvas app solution I build includes some Power Automate flows, because there are things you can do with flows better than you can do them in an app. Keep on growing your knowledge!

Bryan