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

[ckosmowski]

• 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

• https://github.com/ckosmowski/micronaut-data-deleteall

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

[graemerocher]

the issue is you can't do an IN across 2 columns (as the case with embedded ID) unless I am missing something. Related post with possible solutions https://stackoverflow.com/questions/1136380/sql-where-in-clause-multiple-columns

[ckosmowski]

I'm pretty sure you can. At least ANSI SQL Syntax contains corresponding syntax to express this. I think it is not supported by 100% of all databases but the most common ones seem to have support.

http://sqlfiddle.com/#!9/e69090

select * FROM books
where (id, name) IN (
  ('1','Test1'),
  ('4','Test4')
)

[graemerocher]

Will see what can be done

[ksawerykarwacki]

Any plans to implement that? Currently the only way to achieve fast delete is to use native queries manually.

[dstepanov]

I'm planning to improve expandable queries for the next release, I might be able to improve this as well

[ckosmowski]

Any Updates?