Need fast delete method for large sets of entities

Support
#1

Update - while digging deeper, I also stumbled upon this Save in batch not working - Support - Jmix. This might be the root cause.

My data model is large with many relations. It is really part of the business, not much we can do about that.

To improve performance for loading we have Fetching Data :: Jmix Documentation. For saving we have Using DataManager :: Jmix Documentation.

I need something similar for deleting large sets of entities. The current JmixDataRepositoryImpl.deleteAll() internally retrieve every entity one by one before deleting. My set has 27705 entities.

I tried multiple variants of deteAll() but they all internally use the same code.

The code that triggers the individual selects is the merge in JpaDataStore.deleteAll(SaveContext context)

image

#2

Hello,
If these are large deletions, and performance is important, I would use database functionalities, there are several ways:

  • make stored procedures, make a service that calls them, make sure only the users who know what they are doing can have access
  • make a service for the deletion, use entity manager, and execute good old SQL at the database, same security considerations
  • do you need soft delete? adjust your solution to act the same way, which is to put deleteDate datetime and deletedBy user into the relevant records

Kind regards,
Mladen

1 Like
#3

Hello Tom,

Deleting entities one by one through ORM is required to maintain persistence context consistency and make Jmix and JPA features work:

  • Cascading
  • Events
  • Soft Deletion
  • Security
  • Caches
  • Audit
  • etc.

Thus, standard delete operations cannot perform faster.
In terms of performance the best solution is the one suggested by @mbucan

Regards,
Dmitry

1 Like