1

In my applications users can perform actions on a few thousand aggregate root instances with a single click. The problem is that the UI is blocked for several seconds (~ 3) what feels too slow. So, I'm looking for way to improve the database operation.

The respective entity class looks (simplified) like this:

class InspectionPoint {
  val id: InspectionPointId
  val version: Short
  val description: String
  val maintenanceLevels: Set<MaintenanceLevelId>
}

The application uses JPA/Hibernate for persistence. The current behavior is that all aggregate roots are fetched from the database, updated by the application and then written back to the database with lots of update, delete, and insert statements. The flow is as follows:

  1. Fetch all entities (aggregate roots) from the DB
  2. n * update entity (increment version in this case)
  3. n * delete maintenanceLevels from collection table
  4. n * insert maintenanceLevels into collection table

As you can see, there are lots of database statements.

The question is how to speed it up. Since every aggregate root carries a version attribute for optimistic concurrency control, it wouldn't be possible to just manipulate the collection table. But maybe this flow would work:

Performing updates without loading entities

  1. update all InspectionPoint rows with the given IDs directly in the database (increment version)
  2. insert or delete rows in the collection table for maintenanceLevels, what would require to distinguish both operation in the client, public (HTTP) API, and in the application service.

The main disadvantages are:

  • client, HTTP service, application service needs to be modified
  • domain logic in the entities gets completely bypassed
  • custom SQL is required what requires some work and makes maintenance harder

Although the performance should be pretty good, there are some severe disadvantages, too.

Do you have any other suggestion how to solve aggregate root bulk updates?

2
  • 2
    Impossible for us to improve your DB access and operations. That's something you have to measure and find where the bottleneck is at, There're no global solutions to specific problems. Here the context matters. On the other hand, you have summarized your "concerns" as The problem is that the UI is blocked for several seconds (~ 3) what feels too slow. Then don't block it, make it asynchronous. If concurrency is a problem, enqueue the requests, set limits, make everything happen on the background and let the user to move forward or just wait.
    – Laiv
    Jan 5 at 14:23
  • Another option is streaming the results as they happen so the client is reported with new data continuously and the UI is updated accordingly. The user sees that something is going on. Further improvements could be to stop and resume the ongoing process, dry runs, etc
    – Laiv
    Jan 5 at 14:35
3

The application uses JPA/Hibernate for persistence. The current behavior is that all aggregate roots are fetched from the database, updated by the application and then written back to the database with lots of update, delete, and insert statements.

Doctor, doctor, it hurts when I do this. - Well, don't do that. ;)

ORMs will never figure out the use-case you're doing, use the additional information you know exists, or even use specific database features to the fullest. Therefore they'll always be sub-optimal for anything but trivial cases.

What you describe is a typical problem: fireing N statements when you know 1 would actually suffice.

Cut the "persistence agnostic" nonsense, and just fire the sql statement you need. Don't let yourself be misled by the tools you use, or some arbitrary architecture that doesn't seem to fit your case anyway. Be free! :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.