When I push one of my action buttons or save ...

[Aaron_Moolb]

When I push one of my action buttons or save a record (either from edit or savings a new record), the process stalls for 10 seconds or so before moving on.

Any ideas?

[tony1]

@Aaron_Moolb Do you mean that you have a change queued up to be synced for 10 seconds (i.e. a little “1” appears near the sync button and then disappears after 10 seconds)? That’s the standard behavior. Changes are queued up and then items are synced from the queue every 10 seconds or so.

[Aaron_Moolb]

@tony No. When I press an action button, it remains highlighted and the whole app freezes for a while prior to the sync being queued up.

[tony1]

@Aaron_Moolb We’re still looking at your app. It involves pressing the Create Time Card action for your first entry, is that okay?

[Aaron_Moolb]

@tony you bet!

[Adam_Stone]

Hi @Aaron_Moolb, I took a closer look at what’s going on here. It turns out it’s actually the column called Last Day Off in the Employees table that’s causing the big delay. When an Attendance record is updated, since it has a Ref to an Employee record, that record’s virtual columns are also updated. Last Day Off has a formula like this

=LOOKUP(

MAX(

SELECT(

IMRS Attendance[_RowNumber],

AND(

…

[Employee]=[_THISROW]

)

)

),

IMRS Attendance,

… )

Since Lookup is basically doing a Select with the first term as a filter condition, using another Select there can have the kind of exponential scaling problem Tony mentioned. It also becomes ambiguous which row [_THISROW] refers to. From the context I think it’s meant to refer to the outermost Employee row, but looking at what the parsed expression produces, I don’t think it’s actually finding that.

That said, I don’t think you need to do a Lookup here. MaxRow() should let you combine the filter and the lookup like so

MaxRow(“IMRS Attendance”, “_RowNumber”, AND(…, [Employee] = [_THISROW]))

[Adam_Stone]

Actually I’m not so sure now if MaxRow will even help, it may just get converted into the same kind of nested structure. If that doesn’t seem to reduce the delay, you could try taking the original lookup and pulling out the MAX(SELECT(…)) part into its own virtual column. That should guarantee that that part just runs once and then the lookup uses that result rather than re-running the inner select for each row.

[Aaron_Moolb]

@Adam_Stone Thank you for doing research to find the issues. I went a head and decided to just make my clock out procedure a little less app heavy. As a result, I have deleted the virtual column culprit and my app is now much faster.

Thanks again!

[tony1]

@Aaron_Moolb It sounds like your action might be doing a lot of work, which is freezing up the app. Can you say more about what your action is doing, how many rows it affects, and how much data you have in your app?

[Aaron_Moolb]

@tony Its actually just changing 1 row, 1 column to a new value. But It is any action I press that this happens to. Its also if I edit an existing record that it takes awhile to save.

[tony1]

@Aaron_Moolb I’d like to try to reproduce the issue on my end. Can you give me the name of your app and some steps to follow to recreate the issue?

[Aaron_Moolb]

@tony IMRS Attendance

IMRSAttendance-290692

[tony1]

@Aaron_Moolb What steps do I need to take to recreate the issue in that app?

[Aaron_Moolb]

@tony edit any of the Attendance records and save it. Or, click on the refresh PTO, or create time card, or PTO report.

I guess you could also create a new attendance record too, just as long as you delete it when you’re done

[tony1]

@Aaron_Moolb It looks like the problem is this expression in your IMRS Attendance table:

MAXROW(IMRS Attendance,_RowNumber,[Employee]=[_THISROW].[Employee])

Your IMRS Attendance table has about 1,000 rows in it. This formula needs to be run for each one of them. In order to run the formula, it needs to do a full pass over the same table to find the maximum row. This means that it ends up being run around 1,000 * 1,000 times = 1,000,000 times.

It’s possible that we could do more to optimize this formula. I’ll talk to my colleagues about it. But for now, you’ll need to either remove the formula or reduce the number of rows in the table.

BTW, this is also causing your long sync times in your app.

[Aaron_Moolb]

@tony Thanks Tony, I look forward to hearing how to optimize this expression.