Deleting Entries is extemely slow

[mcl-sz]

When we want to delete 20 entries, using the select all checkbox, it takes literally 42 seconds (measured with console on the PostExecuteRecordSetAction request) to delete. When you have to delete 2500+ entries for multple forms this takes forever.

These are the stats of our UF-tables:

UFRecords 11000+
UFRecordDataBit 4300+
UFRecordDataDateTime 12000+
UFRecordDataInteger 0
UFRecordDataLongString 14500+
UFRecordDataString 200000+

Where using Umbraco Forms 8.6.0

This is not really an error because it does work but it's extremely slow.

Reproduction

Steps to reproduce

Maybe hard to repoduce because you'll need a lot of entries.

Expected result

Deleting 20 entries should least a few seconds

Actual result

Deleting 20 entries takes 42 seconds

[AndyButland]

Might be worth you looking to see if you can upgrade to the latest version. Mainly as in 8.7 we added various database integrity constraints, including indexes, that weren't there in previous version and should speed these types of operations up.

You'll find some details on the changes made in the docs section here.

[AndyButland]

Am going to close this one now but if you have feedback or continued issues following the update to 8.7 and/or confirming the keys/indexes introduced in that version are in place, please do re-open and let us know.

[mcl-sz]

Hi @AndyButland, we've updated to 8.7.5. Deleting is a little bit faster but still very slow. This would'nt be the biggest problem if there was some busy indicator while deletion is in progress. Instead users click on "Delete" again and get an error.

The new foreign keys which are added to the UFRecordData... tables are all set to "NO ACTION". Isn't it better to set these to "CASCADE", so that related records are deleted when deleting the rows in UFRecords?

[AndyButland]

I agree a loading "spinner" and disabling the button whilst an operation is in progress would be a good idea - I'll add that for the next feature release.

"CASCADE" would have been an option - and if you want, I don't see any reason why you shouldn't change them for your installation if you prefer. I didn't set it by default though just as to be honest that option always makes me nervous!... that you could delete a lot more records than maybe you expected to, and hence I've generally preferred to not use this and be explicit in the code on what records we want to delete.

[mcl-sz]

Thanks for adding the deletion indicator to the next feature releases.

In my opinion, using CASCADE is exactly what a relational database is for. A relational database engine is much better (and faster) able to do this itself than by trying to solve this by using DELETE queries on the related tables to finally get the same result (if at least all DELETE queries were successful).

[warrenbuckley]

The loading indicator PR fix is here https://github.com/umbraco/Forms/pull/551 and due for 8.8.0