Why is my re-sorting / re-indexing algorithm so slow and how can I do it better?

[BenBl]

Hey all,

I have an orders table. Each row in the table has a priority that’s manually assigned. I’m trying to allow the user (or later, an action) to reassign priorities. The strategy is:

1. Priorities are assigned by the user and can be arbitrary, though unique numbers (e.g. for the 3 orders O1,O2,O2, the priorities could be O1 P=1, O2 P=2, O3 P=7)
2. Use an action to reassign these priorities in adjacent multiples of 2 (e.g. the former set becomes O1 P=0, O2 P=2, O3, P=4)
3. Now a user can adjust priority by reassigining a value in between two existing priorities (e.g. adjusting O3’s priority to 1 so: O1 P=0, O3, P = 1, O3 P=2,
4. Re-sort according to step 2.

The way I’ve implemented this:

• There’s a single table called Orders. Orders has these columns: row number, Key, Order number, Priority, and adjusted priority and orders has a virtual column: Temp adjusted priority.
• [Temp adjusted priority] has the formula: count(select(orders[priority], [priority] < [_thisrow].[priority]))*2
• Action: Adjust priority for this row: For a record of orders. Set data values of some columns in this row. set [adjusted priority] to [temp adjusted priority]
• Action: adjusted priority for many rows. For a record of orders. Execute an action on a set of rows. Referenced rows: Select(orders[key], [priority] <> "") Referenced action: Set priority for this row.

I’m having problems running adjusted priority for many rows.
It works fine for data sets of 15 orders. It’s bearable for up to a couple hundred. But as soon as you get to 500+, it causes Appsheet to hang and chrome to tell you the tab has become unresponsive. I’ve put this into a totally separate app, so there’s no other actions / workflows / dependent tables getting triggered.

• The virtual column, Temp adjusted priority, is set once and costs, probably, O(N) to do all N orders.
• Adjust priority for this row is simply copying a virtual column value to real column, probably O(1) for each order.
• The filter for adjusted priority for many rows is probably O(N) for all of the orders.

It seems to me like this should be a linear algorithm. Yet, it seems to kill appsheet on relatively small databases.

1. Why is this so brutal?

2. How should I do this?

[Steve]

BenBl:

Why is this so brutal?

This:

BenBl:

[Temp adjusted priority] has the formula: count(select(orders[priority], [priority] < [_thisrow].[priority]))*2

On sync, each row of Orders scans all rows of Orders. O(N**2)

BenBl:

Action: adjusted priority for many rows . For a record of orders. Execute an action on a set of rows. Referenced rows: Select(orders[key], [priority] <> "")

This expression doesn’t do what you think it does. Read more:

https://community.appsheet.com/search?q=@steve%20quirk

[BenBl]

Thanks, Steve. (Also thanks for that bonus tip on the operators!).

When does Appsheet trigger a sync? When I sync the app, the delay is 5 seconds. When I run adjusted priority for many rows, it delays 25 seconds, then the chrome warning comes up about the tab not being responsive. Is it synching on every single row change for an action on multiple rows?

[Steve]

BenBl:

When does Appsheet trigger a sync?

Sync is unfortunately an overloaded term. When you tap the circular sync button in the upper right corner of the screen, the resulting process does three things:

1. Copy local data changes to the server for storage in the corresponding data sources and potentially triggering workflows.

2. Download and install any app configuration updates.

3. Download current data from the server, including recalculated virtual column values.

Any time you see that distinctive “Syncing app” screen, this process is underway.

Part (2) is what really disrupts use of the app. Parts (1) and (3) actually occur automatically behind the scenes even without an explicit sync whenever there are local changes that have not been sent to the server. After you make a data change, you can observe (1) happening as the number in the orange/red in the sync button counts down, once or each update is sent. Step (3) occurs after all individual updates have been sent. You’ll know (3) is completed when all of your virtual column values magically update at once. The time it takes is entirely dependent n the amount of data and (more importantly) the complexity of your virtual column App formula expressions. If the app user males no data changes that would prompt step (1) and (3), the app will automatically attempt step (3) every 30 minutes or so while the app is open.

So the term “sync” may refer to the entire three-step data+app sync process, which is disruptive to the user, or to the steps (1) and (3) data-only sync process, which isn’t disruptive.

In your case, you notice different sync times. Some sync time variation is normal, typically within just a few seconds. The vast difference you’re seeing is probably due to the workflow. In that case, the sheer amount of processing required to complete the workflow adds a great deal of time.