AppSheet Database Delete Performance

[mikef1990]

Hello Experts,

I have an AppSheet leveraging an AppSheet Database. I have found that when using AppSheet Database compared to using Google Sheets the delete row performance is extremely different. The AppSheet DB is exponentially slower, and it seems to perform deletes operations sequentially a row at a time. I have reviewed the Performance Monitor and I can see it takes just under a second to successfully delete a row. The table is a child to a customer table. It does have 2 virtual columns that have cross table references. But, I would have thought on a delete operation the virtual column computation would not be involved. And I cannot see any reference to that in the Performance Monitor view. In the action I have that does all the clean-ups there is a number of sub actions. The three sub actions that delete records from child tables of customers; all use a Filter to select the rows; and the Delete operation. Could the issue be the Filter as the means to select the rows? I had assumed it would only perform the Filter once, get the row references and then delete all the rows as a single step.

Any advice would be hugely appreciated.

[Steve]

I recommend against using AppSheet Database. It has many flaws.

[Koichi_Tsuji]

yep, dont use AppSheet DB, which is with bunch of bugs and make your apps unstable. My honest recommendation.

[Koichi_Tsuji]

Google says the sync performance is better with ASDB. We tested with our live data. But still Google Sheet is performing better.

https://www.googlecloudcommunity.com/gc/Announcements/App-Sync-Performance-Improvement-Security-Filt...

Sync speed is faster than ASDB, and Google sheet will not pose you any annoying problems comparing with dealing with Google Sheets.

[scott192]

From what I have seen with ASDB, when you delete a record the record is really deleted from the table.  All other records below the deleted record seem to move up to fill the gap.  This behaviour is not the same as in Google Sheets where the row is simply cleared and you can see a visual gap in your rows.  

If your table is set to cascade deletes to child tables as well then the you have to factor in the time to delete the child rows, recalculate the formulas and so on.  1 seconds per deleted row does not seem massive to me though when you factor in all the housekeeping that ASDB does.

There are lots of people who have wondered why when deleting a row from a sheets based table the behaviour is to leave a gap and the known reason is that sometimes people use the RowNumber as the primary key field so deleting and moving every row would not only be computationally intensive, but would kill any parent/child relationships based on RowNumber.

It is very nice to be able to use the 'Cell History' feature in the sheets backend to examine the 'deleted' rows with the possibility of putting them back using copy/paste operations.

One suggestion to improve your delete operations on ASDB would be to mark records for deletion using a column that stores a 'DELETE' trigger or something similar.  You could base any views you want your users to see on a Slice of your data where that column is not set to 'DELETE'.  From the user's perspective, as soon as the record was marked for deletion it would immediatley disappear from any views they have access to even though the record has not been deleted yet.  Then, you could set up a scheduled bot to run every day to actually go through a slice of records that have the DELETE marker and then perform that delete properly.