micronaut-projects / micronaut-projects/micronaut-data

Delete all method for entities with @EmbeddedId does not use IN for better performance

Open
#373 7 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

type: improvement
Dominant language
Java
Stars
482
Forks
229
Avg merge
1d 7h
Merged PRs (30d)
32

Description

  • Steps to reproduce provided
  • Stacktrace (if present) provided not present
  • Example that reproduces the problem uploaded to Github
  • Full description of the issue provided (see below)
Steps to Reproduce
  1. Create a micronaut Application with Entity using a composite ID with @EmbeddedId
  2. Create a corresponding JdbcRepository
  3. Add Data to the database table
  4. Invoke deleteAll(Collection<Entity>) on the Repository to delete some selected Entities
Expected Behaviour

Micronaut data generates and executes one single SQL query using the IN clause in case the selected Dialect supports IN clauses with multiple columns (most do).

Actual Behaviour

Micronaut data interates the provided entities and executes one DELETE statement per entity, this rapidly cuts down performance for large collections of entities.

Environment Information
  • Operating System: Windows 10
  • Micronaut Version: 1.3.0.RC1
  • JDK Version: 1.8
Example Application

The master branch demonstrates the issue (execute the application -> logging will show two executed delete statements).

11:19:04.351 [main] DEBUG io.micronaut.data.query - Executing Query: SELECT book_.`hash`,book_.`name`,book_.`author` FROM `Book` book_
11:19:04.367 [main] DEBUG de.ksmwsk.deleteall.BookDeleter - Found 4 books before deletion
11:19:05.389 [main] DEBUG io.micronaut.data.query - Executing Query: DELETE  FROM `Book`  WHERE (hash = ? AND name = ?) <------------------
11:19:05.392 [main] DEBUG io.micronaut.data.query - Executing Query: DELETE  FROM `Book`  WHERE (hash = ? AND name = ?) <------------------
11:19:05.392 [main] DEBUG io.micronaut.data.query - Executing Query: SELECT book_.`hash`,book_.`name`,book_.`author` FROM `Book` book_
11:19:05.393 [main] DEBUG de.ksmwsk.deleteall.BookDeleter - Found 2 books after deletion

The simple-id branch demonstrates the behaviour with simple (non-embedded) ideas which i would also expect from the version with the EmbeddedId

11:05:28.897 [main] DEBUG io.micronaut.data.query - Executing Query: SELECT book_.`hash`,book_.`name`,book_.`author` FROM `Book` book_
11:05:28.912 [main] DEBUG de.ksmwsk.deleteall.BookDeleter - Found 4 books before deletion
11:05:29.933 [main] DEBUG io.micronaut.data.query - Executing Query: DELETE  FROM `Book`  WHERE (hash IN(?,?)) <----------------------------
11:05:29.938 [main] DEBUG io.micronaut.data.query - Executing Query: SELECT book_.`hash`,book_.`name`,book_.`author` FROM `Book` book_
11:05:29.939 [main] DEBUG de.ksmwsk.deleteall.BookDeleter - Found 2 books after deletion

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the linked example application and its master branch, then trace the repository deleteAll(Collection) path for entities using @EmbeddedId. Run the reproduction and inspect the generated SQL for the separate DELETE statements. Done means supported dialects issue one DELETE using a multi-column IN clause while preserving the existing behavior for unsupported cases.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.