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?