Performance of identfier generation strategies with MySQL and EclipseLink

Every entity needs to have an identifier which uniquely identifies the table record associated with this entity.
JPA and EclipseLink allow you to automatically generate entity identifiers based on four different strategy types:

  • IDENTITY
  • TABLE
  • SEQUENCE
  • AUTO – should pick an appropriate strategy for the particular database

I decided to write a quick tool to measure the performance of inserting entries to a database using different identifiers generation strategies.

Overall test procedure (assumptions)

DDL auto generation on
Multithreaded yes
Threads 8
Entries to insert per test 10 000
Test repeats 10
Restart after every test type yes
Application server TomEE 7.0.2 Plume
Database MySQL 8.0.3-1.1.2 (Docker)

Test entity

IDENTITY (auto increment) strategy

JavaDoc says: “Indicates that the persistence provider must assign primary keys for the entity using a database identity column.”

Generator configuration

DDL generation output

SQL flow

Results

Total execution time [milis] 429972
Total execution time [s] 429
Average execution time [milis] 42997
Average execution time [s] 42
Database CPU Usage ~20%

TABLE strategy

JavaDoc says: “Indicates that the persistence provider must assign primary keys for the entity using a database identity column.”

Generator configuration

DDL generation output

SQL flow

Results

Total execution time [milis] 2 028 923
Total execution time [s] 2 028
Average execution time [milis] 202 892
Average execution time [s] 202
Database CPU Usage ~10%

SEQUENCE strategy

JavaDoc says: “Indicates that the persistence provider must assign primary keys for the entity using a database sequence.”

EclipseLink with MySQL is going to use the IDENTITY generator.

AUTO strategy

JavaDoc says: “Indicates that the persistence provider should pick an appropriate strategy for the particular database. The AUTO generation strategy may expect a database resource to exist, or it may attempt to create one. A vendor may provide documentation on how to create such resources in the event that it does not support schema generation or cannot create the schema resource at runtime.”

EclipseLink with MySQL is going to fall back to using the TABLE generator.

Conclusion

As a result, TABLE generation strategy is almost 5 times slower than IDENTITY strategy. Therefore even if auto_increment in MySQL has limitations comparing to (for example) sequences in Oracle, there is no significant argument to use TABLE generation strategy instead.

GitHub: https://github.com/PiotrRaszkowski/jpa-id-generator-type-test

 

Scroll to top