Imagine having a tool that can automatically detect JPA and Hibernate performance issues.
Hypersistence Optimizer is that tool!
Although there is an SQL Standard, every relational database is ultimately unique, and you need to adjust your data access layer so that you get the most out of the relational database in use.
In this article, we are going to see what you can do to boost up performance when using MySQL with JPA and Hibernate.
Don’t use the AUTO identifier GeneratorType
Every entity needs to have an identifier which uniquely identifies the table record associated with this entity. JPA and Hibernate allow you to automatically generate entity identifiers based on three different strategies:
As I explained in this article, The TABLE identifier strategy does not scale when increasing the number of database connections. More, even for one database connection, the identifier generation response time is 10 times greater than when using IDENTITY or SEQUENCE.
If you are using the AUTO GenerationType:
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
Hibernate 5 is going to fall back to using the TABLE generator, which is bad for performance.
The native generator will pick IDENTITY instead of TABLE.
IDENTITY generator disables JDBC batch inserts
Neither MySQL 5.7 or 8.0 support SEQUENCE objects, you need to use IDENTITY. However, as I explained in this article, the IDENTITY generator prevents Hibernate from using JDBC batch inserts.
JDBC batch updates and deletes are not affected, only the INSERT statements cannot be batched because, by the time the Persistence Context is flushed, the INSERT statements were already executed so that Hibernate knows what entity identifier to assign to the entities that got persisted.
Speed-up integration testing with Docker and tmpfs
MySQL and MariaDB are notoriously slow when having to discard the database schema and recreating it every time a new integration test is about to run. However, you can easily address this issue with the help of Docker and tmpfs.
As I explained in this article, by mapping the data folder in-memory, integration tests are going to run almost as fast as with an in-memory database like H2 or HSQLDB.
Use JSON for non-structured data
Even when you are using a RDBMS, there are many times when you want to store non-structured data:
data coming from the client as JSON, which needs to be parsed and inserted into our system.
image processing results which can be cached to save reprocessing them
When processing large volumes of data, it’s not very efficient to move all this data in and out of the database. It’s much better to do the processing on the database side by calling a Stored Procedure.
SQL streaming makes sense for two-tier applications. If you want to do ResultSet streaming, you have to pay attention to the JDBC Driver as well. On MySQL, to use a database cursor, you have to options:
Although you might that, since Hibernate uses PreparedStatements by default, all statements are executed like this:
In reality, they are executed more like this:
As I explained in this article unless you set the useServerPrepStmts MySQL JDBC Driver property, PreparedStatements are going to be emulated at the JDBC Driver level to save one extra database roundtrip.
There are two very complicated things in programming:
handing Date/Time across multiple Timezones
To address the second problem, it’s better to save all timestamps in UTC timezone. However, prior to the MySQL Connector/J 8.0, you also needed to set the useLegacyDatetimeCode JDBC Driver configuration property to false. Since MySQL Connector/J 8.0, you don’t need to provide this property.
As you can see, there are many things to keep in mind when using MySQL with JPA and Hibernate. Since MySQL is one of the most deployed RDBMS, being used by the vast majority of web applications, it’s very useful to know all these tips and adjust your data access layer to get the most out of it.