Performance Analysis and Improvement for CRUD Operations in Relational Databases from Java Programs Using JPA, Hibernate, Spring Data JPA

1. Introduction

Accessing relational databases from Java applications is the application domain. It is essential for any application to persist its data and this thing can be done with Object Relational Mapping (ORM) which is concerned with the mapping of classes to database tables [1].

There are a lot of frameworks and SQL dialects to be used when you start a new application, but this makes the choice harder. [2] Each of them has its own advantages and disadvantages, in the end coming to a trade-off, as it frequently happens in information technology. A developer should be aware of his application's needs to pick the right combination, generally being able to select, as a framework, between JPA, Hibernate [3,4], and Spring Data JPA [5].

This will help developers to choose the option that best fits their Java application. Having no clue about frameworks and just using them on other people's recommendations is not a great way to start an application.

2. Proposed Research Approach

The proposed research approach is to run an average-complexity application with different frameworks and dialects, on a Windows operating system and to test the necessary time to handle different amounts (up to 500 thousand entries) of each CRUD operation. The amount of code and its simplicity will also be taken into consideration when the comparison is made.

3. Problem Background

“In relational databases, the data is organized in tables. Each entry in the table should have a unique identifier called Primary Key (PK). Columns are specific data attributes defined when a table is created. Related tables are connected with Foreign Keys (FK).” [6]

There are four principles to define relational database transactions:

• atomicity – ensures that the transaction operations are all executed or none
• consistency – ensures that only correct data is added to the database
• isolation – ensures that transactions are not affected by other transactions
• durability – ensures that data committed to the database is stored permanently

There are also four categories of commands:

• DDL (data definition language)
• DQL (data query language)
• DML (data manipulation language)
• DCL (data control language)

The experiments will focus on the main operations of an application, DQL (SELECT commands to query the content of the database), and DML (INSERT, UPDATE, and DELETE commands).

Java-based applications apply these four operations on the database with the support of different frameworks like JPA, Hibernate, Spring Data JPA, MyBatis, etc. They are responsible for the object-relational mapping. This means they map a Java model to specific tables from a database.

These frameworks have different approaches to handling the mapping problem. The Java Database Connectivity (JDBC) is one way to work with databases in Java because it offers APIs that allow the user to execute SQL statements.

Object Relational Mapping makes the development effort simpler as JDBC, as:

• it hides the SQL interaction
• it offers development with objects instead of database tables
• there is no need to take care of the database implementation
• there is less code written for the same job
• it is based on the JBDC, in the underlying

Database connections in Java applications are managed through a JDBC driver, specific to the RDBMS. A specific dialect also needs to be set up by the developers when configuring the database connection so that the queries are created in the right language.

The most popular RDBMSs today are:

• MySQL
• Oracle SQL
• Microsoft SQL Server
• PostgreSQL

Selecting one of the dialects mentioned above and combining it with the frameworks that best match the requirements can be a hard decision to make. Some combinations might give faster performance for the CRUD operations but with the price of writing a lot of code.

This research intends to help developers select the correct combination of technologies that suits their application’s needs. For example, an application might not need a fast response from the database so it should focus on keeping the code simpler and easier to maintain. Also, if speed is crucial for the implementation needs, then the architects and developers must consider a trade-off between code complexity and speed. Furthermore, inefficient combinations will also be discovered in the research. This will help developers to avoid bad, unoptimized solutions.

The research would like to assess the behavior of an average-complexity Java application. Joins will be a key element in our application because this is a key operation when working with a relational database.

4. Previous Research

Several studies investigate what SQL dialects or ORM frameworks the developer should choose and why.

Haseeb Yousaf compares, experimenting on the Ubuntu operating system, several ORM frameworks: Hibernate, OpenJPA, and EclipseLink. He performed “five queries on a table: read by ID (PK), read by three different type attributes and one combined read based on two attributes. The results show that Hibernate is the fastest of those three frameworks from 10000 records up to 160000 records, while OpenJPA is the slowest. EclipseLink is getting closer to Hibernate as the number of records increases.” [7]

Another work compares “the most popular five relational database management systems: MySQL, Oracle, PostgreSQL, SQLite, and Microsoft SQL Server. The first test he does is measuring the installation time for each one of them. Oracle and SQL Server seem to be the slowest ones, while SQLite and MySQL’s installations take less than five minutes. The second test he does is measuring query times for every CRUD operation on a different number of entries. From his work, Oracle is the slowest when it comes to reading information from the database, but pretty good at updating and deleting. MySQL is far from Oracle’s update and delete performance with very big times on these operations, while PostgreSQL proves to be the fastest one. The other two, SQLite and Microsoft SQL Server can be the second option when starting a new application, besides PostgreSQL.” [8]

The comparisons mentioned above are made at the same level of technology, either to test different ORM frameworks or to measure the performances of RDBMSs.

The research we propose covers the suite of all the technologies needed for an application and achieves a comprehensive evaluation, including the combinations between the ORM framework and the SQL database.

Another research using the .NET programming language was conducted at the University of Oradea, where the analysis compared “the execution times and the memory usage of an application, based on three distinct ORM frameworks combined with SQL Server on the database side.” [9]

5. Architecture of the Application

Usually, people tend to choose the technologies they know best. This research aims to encourage them, if the selected combination of components is efficient, or to change the perspective on database communication, by providing actual results.

Each combination will be a separate and independent project as they must not interfere with each other. This way, a safer evaluation can be done. In the future, it can be changed to choose the corresponding framework configuration file and to execute the specific test, because the entities that the solution exposes are used in all the different approaches. Code reuse can be achieved through this way of development, which is a great principle for an application.

All the implementations must follow a standard for the data model because otherwise, the results might become irrelevant. The standard is the Java Persistence API, on top of which all the selected frameworks are mapped.

The solution is based on two main components: the ORM framework and the RDBMS. “The ORM framework is responsible for mapping Java objects on database models. It controls the flow until data reaches the database.” [10]

For testing purposes, the chosen framework was JUnit [11]. This is the most popular testing extension in the Java community. Assertions are the main functionality, as they are used to check the actual results versus the expected results.

To improve the testing, Java Microbenchmark Harness (JMH) [12] was added to the project to check if warming up the Java Virtual Machine will reduce the execution times. It can also be used for extracting code optimization paths or measuring the execution of different methods. “The ORM framework is responsible for mapping Java objects on database models. It controls the flow until data reaches the database.” [4]

The architecture of the testing application is described in Figure 1, demonstrating how the different combinations of the implementation are created:

A combination that will be subject to testing is represented by one component from the left side (ORM framework) plus one on the right side (RDBMS). The color legend is as follows:

• BLUE -> JPA with all RDBMS
• GREEN -> Hibernate with all RDBMS
• ORANGE -> Spring Data JPA with all RDBMS

Each testing scenario is mapped by an arrow. This way, all the possibilities between these technologies are covered and it provides more reliable results. The Java application can be considered the central part because that is the entry point from which the project will start.

The current research extends our previously published papers, looks for optimization possibilities, and investigates the critical regions. Extending the experiments, “the evaluation will be done by implementing a medium-complexity betting application in which the main entities will be: tickets, bets, and matches. These entities will be described in the implementation section.” [4]

6. Implementation of the Solution

Usually, people tend to choose the technologies they know best. This research aims to encourage them to decide if the selected combination of components is efficient or to change the perspective on database communication, by providing actual results.

“The application used for testing focuses on a soccer betting service model. This means that testing is done by creating a different number of tickets, each with its bets and corresponding matches, and simulating their behavior in relationship with the database and the ORM framework. There are three main entities of the project on which the CRUD operations are tested: Ticket, Bet, and Match.” [4]

Figure 2 presents ”the data model with the relationship between entities. One ticket can have multiple bets on it (one-to-many relationship), and one bet is based on one match (one-to-one relationship).” [4]

The tables have several attributes:

• Ticket
  • ticket id -> the unique identifier for a ticket
  • status -> one of the following values: WON, LOST, PENDING
  • sum -> represents the amount placed on the ticket as betting value
• Bet
  • bet id -> the unique identifier for a bet
  • match id -> the identifier of the match the bet is placed on
  • ticket id -> the identifier of the ticket the bet belongs to
  • result choice -> one of the following: 1 (host wins), 2 (visitor wins), X (even)
• Match
  • name -> a string like “Real Madrid – Barcelona”
  • score -> the final score of the match
  • host team win odd -> the odd for the host’s win
  • visitor team win odd -> the odd for the visitor’s win
  • draw odd -> the odd in case the match is even
  • match date -> the date when the match will be played
  • match id -> the unique identifier for a match

The setup of the machine where experiments were run is the same as for the previously published work [5]:

• CPU: Intel i7 – 6700HQ @ 2.6 GHz
• RAM: 8 GB
• Operating System: Windows 10 Pro 64-bit

To review how the previous experiments were done:

“The tests on the development of a betting service were designed to measure the length of each CRUD action for a different number of tickets, each carrying one bet on one match. The number of the tickets were: 1000, 2000, 5000, 10000, 20000, 50000, 100000, 200000, and 500000.

To maintain the activities running on all three database tables, the update operation modified the ticket's status, the bet's outcome selection, and the name of the match. This increased the application's complexity.

These tests were done on each combination of ORM framework and relational database system, resulting in twelve distinct situations (3 ORM frameworks and 4 RDBMSs), in the first phase.” [4]

We extended the experiments, and the second phase introduced now involved Java Microbenchmark Harness (JMH), which was responsible for handling the warm-up phase of the Java Virtual Machine. It was set up to only run one iteration for a warm-up and one for measurement, due to the extended running time for larger entry numbers, and both iterations had their results interpreted.

Executing the tests with the same available resources and in the same conditions was another important point that was taken care of in the evaluation.

7. Evaluation without JMH

7.1. Java Persistence API

We’ll summarize the results of the previous research, as conducted and published in [3,4,5]. Table 1, Table 2, Table 3 and Table 4 and Figure 3, Figure 4, Figure 5 and Figure 6 provide the results of the execution times without JMH, using Java Persistence API as a framework, and different RDBMSs.

7.2. Hibernate

Table 5, Table 6, Table 7 and Table 8 and Figure 7, Figure 8, Figure 9 and Figure 10 provide the results of the execution times without JMH, using Hibernate as a framework, and different RDBMSs.

7.3. Spring Data JPA

Table 9, Table 10, Table 11 and Table 12 and Figure 11, Figure 12, Figure 13 and Figure 14 provide the results of the execution times without JMH, using Spring Data JPA as a framework, and different RDBMSs.

8. Evaluation with JMH

The second step was to introduce Java Microbenchmark Harness (JMH) in the measurements. The benchmark was set to run with one warm-up iteration, followed by one measurement iteration, after warm-up. The execution times will be presented for both iterations, as it could offer an interesting interpretation of the results.

8.1. Java Persistence API, Warm-up Iteration

Table 13, Table 14, Table 15 and Table 16 and Figure 15, Figure 16, Figure 17 and Figure 18 provide the results of the execution times of the warm-up iteration, with JMH, using Java Persistence API as a framework, and different RDBMSs.

8.2. Hibernate, Warm-up Iteration

Table 17, Table 18, Table 19 and Table 20, and Figure 19, Figure 20, Figure 21 and Figure 22 provide the results of the execution times of the warm-up iteration, with JMH, using Hibernate as a framework, and different RDBMSs.

8.3. Spring Data JPA, Warm-up Iteration

Table 21, Table 22 and Table 23 and Figure 23, Figure 24, Figure 25 and Figure 26 provide the results of the execution times of the warm-up iteration, with JMH, using Spring Data JPA as a framework, and different RDBMSs.

8.4. Java Persistence API after Warm-up

Table 25, Table 26, Table 27 and Table 28 and Figure 27, Figure 28, Figure 29 and Figure 30 provide the results of the execution times after the warm-up iteration, with JMH, using Java Persistence API as a framework, and different RDBMSs.

8.5. Hibernate after Warm-up

Table 29, Table 30, Table 31 and Table 32 and Figure 31, Figure 32, Figure 33 and Figure 34 provide the results of the execution times after the warm-up iteration, with JMH, using Hibernate as a framework, and different RDBMSs.

8.6. Spring Data JPA after Warm-up

Table 33, Table 34, Table 35 and Table 36 and Figure 35, Figure 36, Figure 37 and Figure 38 provide the results of the execution times after the warm-up iteration, with JMH, using Spring Data JPA as a framework, and different RDBMSs.

9. Bottlenecks Investigation

A quick comparison between this research and the previous one presented in [2] revealed some common findings:

• Spring Data JPA has a big overhead, the slowest of all frameworks for batch operations
• Hibernate and JPA solutions go side by side, with almost overlapping graphs
• Spring Data JPA requires the fewest lines of code

The execution times in milliseconds for 50k entries for both solutions for each framework and operation on MySQL database are presented below in Table 37.

For JPA and Hibernate the execution times are quite linear for each CRUD operation except reading, which is reasonable considering that the current research works with three entities and the previous one works with a single entity.

Reading seems to be slower on a single database table, but this could be due to a newer more powerful generation of Intel CPU that has been used in the current research.

Both these approaches revealed a big overhead in the execution times for Spring Data JPA for each operation and RDBMS tested. This triggered an interest in investigating the time discrepancies between the mentioned framework and the other ones (Hibernate and JPA). The overhead for Spring Data JPA is even bigger for the previous research, despite having just one entity, which makes the research on the bottlenecks more interesting.

9.1. Spring Data JPA Bottlenecks Investigation

The experiments demonstrate that Spring Data JPA comes with the least amount of code written, but also with a big overhead in the execution times, in comparison with JPA and Hibernate.

To locate the bottlenecks, an analysis of the framework methods execution times had to be done. This was achieved by using YourKit Java Profiler 2022.9, which according to its description, “utilizes many JVM and OS features to obtain information about methods and times with the minimum overhead. CPU profiles can be visualized as call trees or lists of hot spots. CPU flame graph is an efficient way to visualize application performance, which allows to find performance bottlenecks quickly and easily.” [13]

Previous research indicated that more than half of performance bottlenecks originate in the data access layer. [14] The current analysis intended to locate more accurately the bottlenecks and to retrieve the top five hot spots for each RDBMS for runs with the number of entries starting from 50k and comparing them with the top five hot spots for a 50k run with Hibernate.

MySQL – Hibernate hot spots for 50k entries:

• com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate()
• org.hibernate.internal.SessionImpl.persist(Object)
• com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(String, int)
• com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(String)
• java.util.Properties.load(InputSteam)

MySQL – Spring Data JPA hot spots for 50k entries:

• com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate()
• org.hibernate.internal.SessionImpl.persist(Object)
• com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery()
• com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(String)
• com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(String, int)

MySQL – Spring Data JPA hot spots for 100k, 200k, and 500k entries:

• com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate()
• com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery()
• com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(String)
• com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(String, int)
• org.hibernate.internal.SessionImpl.persist(Object)

Oracle – Hibernate hot spots for 50k entries:

• oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate()
• org.hibernate.internal.SessionImpl.persist(Object)
• oracle.jdbc.driver.PhysicalConnection.prepareStatement(String, String[])
• oracle.jdbc.driver.PhysicalConnection.prepareStatement(String)
• java.util.Properties.load(InputSteam)

Oracle – Spring Data JPA hot spots for 50k entries:

• oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate()
• org.hibernate.internal.SessionImpl.persist(Object)
• oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery()
• oracle.jdbc.driver.PhysicalConnection.prepareStatement(String, String[])
• oracle.jdbc.driver.PhysicalConnection.prepareStatement(String)

Oracle – Spring Data JPA hot spots for 100k, 200k, and 500k entries:

• oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate()
• oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery()
• oracle.jdbc.driver.PhysicalConnection.prepareStatement(String)
• oracle.jdbc.driver.PhysicalConnection.prepareStatement(String, String[])
• org.hibernate.internal.SessionImpl.persist(Object)

PostgreSQL – Hibernate hot spots for 50k entries:

• org.postgresql.jdbc.PgPreparedStatement.executeUpdate()
• org.hibernate.internal.SessionImpl.persist(Object)
• org.postgresql.jdbc.PgConnection.prepareStatement(String, int)
• org.postgresql.jdbc.PgConnection.prepareStatement(String)
• java.util.Properties.load(InputSteam)

PostgreSQL – Spring Data JPA hot spots for 50k entries:

• org.postgresql.jdbc.PgPreparedStatement.executeUpdate()
• org.postgresql.jdbc.PgPreparedStatement.executeQuery()
• org.hibernate.internal.SessionImpl.persist(Object)
• org.postgresql.jdbc.PgConnection.prepareStatement(String, int)
• org.postgresql.jdbc.PgConnection.prepareStatement(String)

Spring Data JPA hot spots for 100k, 200k and 500k entries:

• org.postgresql.jdbc.PgPreparedStatement.executeUpdate()
• org.postgresql.jdbc.PgPreparedStatement.executeQuery()
• org.postgresql.jdbc.PgConnection.prepareStatement(String, int)
• org.postgresql.jdbc.PgConnection.prepareStatement(String)
• org.hibernate.internal.SessionImpl.persist(Object)

SQLServer – Hibernate hot spots for 50k entries:

• com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate()
• org.hibernate.internal.SessionImpl.persist(Object)
• com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(String, int)
• com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(String)
• java.util.Properties.load(InputSteam)

SQLServer – Spring Data JPA hot spots for 50k entries:

• com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate()
• com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery()
• org.hibernate.internal.SessionImpl.persist(Object)
• com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(String, int)
• com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(String)

SQLServer – Spring Data JPA hot spots for 100k, 200k, and 500k entries:

• com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate()
• com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery()
• com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(String, int)
• com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(String)
• org.hibernate.internal.SessionImpl.persist(Object)

The main thing that can be noticed from the results above is that Spring Data JPA makes really time-consuming calls on the executeQuery() method, which is missing in the Hibernate implementations. That happens because when Spring Data JPA deletes an entry, at first it looks for the entry and then makes the deletion. The deletions are done one by one with the deleteAll(Iterable<Ticket>). Since the delete operation is the most time-consuming one, the executeQuery() method becomes one of the most active methods.

Another interesting fact is that most of the time is spent inside drivers’ methods for each RDBMS tested and not in a framework method.

9.2. Reducing Spring Data JPA Bottlenecks

The first attempt to reduce the bottlenecks was to override the void delete(Ticket) method from the created SimpleJpaRepository<Ticket, ID>. To do this, we created an interface that extends CrudRepository<Ticket, ID>:

@NoRepositoryBean

public interface CustomCrudRepository<ID> extends CrudRepository<Ticket, ID> {

 void delete(Ticket entity);

}

 

An implementation of this interface was needed to override the method and skip the queries given to the database:

public class CustomJpaRepository<ID> extends SimpleJpaRepository<Ticket, ID>

        implements CustomCrudRepository<ID> {

 private EntityManager entityManager;

 

 public CustomJpaRepository(EntityManager entityManager) {

  super(Ticket.class, entityManager);

  this.entityManager = entityManager;

 }

 

 @Override

 public void delete(Ticket entity) {

  this.entityManager.remove(this.entityManager.contains(entity) ?

        entity : this.entityManager.merge(entity));

 }

}

 

After creating the repository bean and injecting the EntityManager dependency into it, the profiling was performed again. There was an improvement of about 10% in the execution times, but it was still not close to the other two ORM frameworks tested.

There were also interesting pop-up messages while the investigation with YourKit finished for Spring Data JPA runs: “Potential deadlock: Frozen threads found -> frozen for at least 19m 10s”. This happened for a run with PostgreSQL and Spring Data JPA with 100k entries, but also for runs with other RDBMSs. The record was established by a PostgreSQL run with Spring Data JPA with 500k entries: 1d 0h 47m 18s, which explains why the biggest scenario runs for so long.

The problem is not a deadlock, but a long starvation, as the program finishes its run successfully. Because of that, another attempt to reduce the bottlenecks was to replace the transaction management done by Spring Data JPA.

Removing the @EnableTransactionManagement annotation was the first step in the process. Then, the EntityManagerFactory used for the JPA implementation was used to replace the factory used by Spring Data JPA.

The CustomCrudRepository<ID> interface was changed to:

@NoRepositoryBean

public interface CustomCrudRepository<ID> extends CrudRepository<Ticket, ID> {

 void delete(Ticket entity);

 Iterable<Ticket> findAll();

 void customSaveAll(List<Ticket> ticketList);

 List<Ticket> customUpdateAll(List<Ticket> ticketList);

 void customDeleteAll(Iterable<Ticket> entities);

}

 

And the CustomJpaRepository<ID> implementation to:

public class CustomJpaRepository<ID> extends SimpleJpaRepository<Ticket, ID>

        implements CustomCrudRepository<ID> {

 private EntityManager entityManager;

 

 public CustomJpaRepository(EntityManager entityManager) {

  super(Ticket.class, entityManager);

  this.entityManager = entityManager;

 }

 

 @Override

 public void delete(Ticket entity) {

  this.entityManager.remove(this.entityManager.contains(entity) ?

        entity : this.entityManager.merge(entity));

 }

 

 @Override

 public void customDeleteAll(Iterable<Ticket> entities) {

  entityManager.getTransaction().begin();

  entities.forEach(this::delete);

  entityManager.getTransaction().commit();

 }

 

 public void customSave(Ticket entity) {

  this.entityManager.persist(entity);

 }

 

 public void customSaveAll(List<Ticket> ticketList) {

  entityManager.getTransaction().begin();

  ticketList.forEach(this::customSave);

  entityManager.getTransaction().commit();

 }

 

 public List<Ticket> customUpdateAll(List<Ticket> ticketList) {

  entityManager.getTransaction().begin();

  List<Ticket> result = ticketList.stream().

    map(this::customUpdate).collect(Collectors.toList());

  entityManager.getTransaction().commit();

  return result;

 }

 

 public Ticket customUpdate(Ticket entity) {

  return this.entityManager.merge(entity);

 }

 

 @Override

 public List<Ticket> findAll() {

  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

  CriteriaQuery<Ticket> criteriaQuery = criteriaBuilder.createQuery(Ticket.class);

  Root<Ticket> rootEntry = criteriaQuery.from(Ticket.class);

   CriteriaQuery<Ticket> all = criteriaQuery.select(rootEntry);

  TypedQuery<Ticket> allQuery = entityManager.createQuery(all);

  return allQuery.getResultList();

 }

}

 

The logic for all CRUD operations did not change too much, they were overridden to use custom transaction management through the EntityManagerFactory. After these changes took place, the execution times for 50k entries for each CRUD operation for each RDBMS tested with Spring Data JPA were remeasured and the results are presented in Table 38 and Table 39.

Comparing the new results with the Hibernate ones proves a significant decrease in time for Spring Data JPA when the transaction management is changed.

It is to emphasize that the purpose of changing Spring Data JPA’s transaction management is experimental, to investigate how the classic EntityManager behaves in Spring’s environment. It does not intend to solve the bottlenecks, further research on transaction management needs to be done.

10. Conclusions

To emphasize the performance differences after introducing the warm-up, we’ll briefly review the conclusions of the experiments that were not using it.

10.1. Conclusions without JMH

10.1.1. Java Persistence API

“The first combination (MySQL – JPA) looks good, with an overall time for the 500k operations test of under 1200 seconds (less than 20 minutes). The CREATE operation is a little bit more time-consuming than the UPDATE one, while READ is really fast, under one second even for the biggest test. DELETE seems to double its execution time when doubling the number of operations, making it quite linear.

For, Oracle, serious differences can be observed. The DELETE operation on 500k entries lasts for almost 2 hours, while the CREATE one, which is also time-consuming compared to the MySQL implementation, is about 10 times quicker. An advantage of this implementation is the UPDATE operation, overpassing the MySQL implementation.”[5]

Getting to the third RDBMS, Microsoft SQL Server has clearly the best execution times for the UPDATE operation. This advantage is covered by a greater disadvantage: the DELETE operation runs for 4.5 hours for 500k entries which is a very bad performance when looking at the MySQL test. READ is also pretty good, while CREATE fits in the average time so far in the tests.

“For a small number of entries (less than 5k), PostgreSQL is the best solution, but if the number of entries is increasing, developers might consider switching the RDBMS. At 500k entries, the deletion is even worse than the one in the previous combination. The advantages of a bigger number of entries can be observed in all other three CRUD operations, setting the record so far. The deletion overhead is so big that it can represent a strong reason not to choose this RDBMS in the described case.” [3]

A great overall performance is offered by MySQL. “It has incredibly good execution times, despite having a slower performance than PostgreSQL for creating, reading, and updating entries, but it saves a lot of time on the delete operation (over 70 times faster).” [3]

“SQLServer has the best reading, while Oracle has the worst one, but this cannot be a deciding factor when choosing the technologies needed to build an application. That is because the difference between these two is under 5 seconds, for 500k entries. Oracle has the slowest insertion of all four RDBMSs.” [4]

10.1.2. Hibernate

For small data sets with less than 5,000 entries, PostgreSQL is a good option. Increasing the number of records, a different RDBMS may be a better option. When there are 500,000 entries, deletion performance becomes much slower compared to when there are fewer entries. On the other hand, the advantage of having a larger number of entries is demonstrated by the improvement in three CRUD operations. The substantial decrease in deletion performance may make PostgreSQL unsuitable for this scenario.

10.1.3. Spring Data JPA

“The combination MySQL - Spring Data JPA appears to be the most effective, with a total duration of less than 1800 seconds (less than 30 minutes) for the 500k operations test. The UPDATE process takes somewhat longer than the CREATE operation, but READ is extremely fast, taking less than four seconds even for the most demanding test. DELETE's execution time doubles with each doubling of the number of operations, making it a linear process.” [3]

Spring Data JPA introduces its particular overhead while executing the operations. Although the creation time is the shortest of all Oracle implementations for 200k+ entries, the other three operations are much slower. The deletion process takes over 3 hours, while the UPDATE operation is less performant compared to the MySQL implementation.

The third RDBMS, Microsoft SQL Server, experiences difficulties with the DELETE and UPDATE operations. The deletion of 500k entries takes almost 8 hours, and the update needs about half of this time. The creation is like the MySQL combination.

“With Spring Data JPA, PostgreSQL provides the fastest speeds for generating and reading entries. However, it experiences the same issue as the SQL Server implementation with a significant increase in the update execution time. Regardless of the RDBMS, the deletion of 500k records is a slow process.” [3]

10.1.4. Overall conclusions

The experiments were designed to provide multi-criteria analysis, using different RDBMSs together with different ORM frameworks. This way, software engineers may decide based on the specificity of their projects and on the operations that they forecast to be intensive for their circumstances.

Performance is similar for Hibernate and Java Persistence API, while Spring Data JPA brings a lot of overhead with it, but it also offers an easier solution regarding the code dimension, to access and modify data.

10.2. Conclusions with JMH

10.2.1. Java Persistence API

MySQL provides the best DELETE time for both iterations. The UPDATE operation has the worst execution time in the warm-up, but it gets average after it.

The second RDBMS tested with this framework, Oracle, has the worst READ execution time by far and the warm-up does not change its position at all. The same is available for the CREATE operation. A good point for Oracle is the deletion time, as it is placed in second place, after MySQL.

SQL Server has some visible changes after the warm-up. At first, it has a pretty good time on the CREATE operation for fewer entries than 100k, but it becomes the worst at this operation for 500k entries or more. It gets better after warming up at the READ operation, jumping from the second position to the first one. Something that remains the same is the DELETE operation, the second worst one.

The best RDBMS for creating, reading, and updating without a warm-up is PostgreSQL, which has an issue with the slowest deletion. Creating and updating are the only positions maintained after the warm-up phase.

10.2.2. Hibernate

Regarding the warmup execution times, PostgreSQL has the best timing on the CREATE and UPDATE operations for each number of entries tested, but it is terrible when it comes to deleting more than 100k entries.

Reading is also great with PostgreSQL, but this time for over 50k entries. For less than this number, MySQL seems to be the best choice. The most valuable point for MySQL is the deletion, which is more than 12.5 times faster than the second RDBMS (Oracle), for a 500k entries run.

Oracle looks a bit lazy, especially for the READ operation, almost 7 times slower at the biggest run than MySQL, which is placed in the third position. Its strengths are updating and deleting for big numbers of entries, making it the second-best choice for these operations. Oracle is the worst solution for less than 50k entries, at every operation.

Ranking second for the CREATE and READ operations, SQL Server is a solution to be taken into consideration when developing a new application, but only if it is not planned to make lots of deletions, because it performs almost as badly as PostgreSQL for this operation.

The most visible impact the warmup had on the second run is observed when looking at the READ operation. It runs almost instantly for a number of entries smaller than 50k and reaches a maximum of half a second for 500k entries, except for the Oracle RDBMS, but a big improvement can be noticed here also.

Even if the execution times after the warmup iteration are expected to be lower, this is true only for the CREATE operation measured on the Oracle and SQL Server RDBMSs. This operation has better results on MySQL and PostgreSQL too, but not for more than 100k entries.

On the MySQL RDBMS, the UPDATE and DELETE operations have almost the same performance, with or without warmup. The same thing is available for deleting on an SQL Server RDBMS or a PostgreSQL one.

Updating execution times are reduced for less than 50k entries with MySQL, 100k entries with PostgreSQL, and 500k entries with Oracle. The most visible change overall was for Oracle, which reduced the durations for almost every number of entries.

The final results after the second iteration made some slight changes in the comparison of the performance for each operation, by each RDBMS. However, PostgreSQL still has the best timing for creating and updating entries, and also the worst overall timing when it comes to deletions.

SQL Server comes forward with the best reading, over-passing PostgreSQL and MySQL, which falls to the third position after it was first in the warmup phase. Oracle has some serious time reduction for less than 200k entries, but at the largest run, it has almost the same time as before, a bit over 5 seconds.

The best deletion is taken again by MySQL, with an even bigger difference from the warmup comparison: almost 17 times faster than the Oracle performance. But something new for this RDBMS is the poor performance when it comes to updating entries because it is the worst solution of all four.

10.2.3. Spring Data JPA

MySQL has the fastest performance for both UPDATE and DELETE operations, while its other CRUD operations have an average performance like other RDBMS tested using the benchmark.

Oracle has the slowest READ time and this does not change with a warm-up iteration. It also performs poorly in CREATE operations. However, Oracle has the second fastest time for both DELETE and UPDATE.

SQL Server maintains its position after warm-up, unlike in the JPA implementation. It has the fastest READ performance, but the second slowest DELETE and UPDATE, after PostgreSQL. PostgreSQL has the best performance for CREATE operations, but the slowest for DELETE and UPDATE, making it the least ideal choice for this framework. These statistics remain unchanged after the warm-up iteration.

Compared to the other two ORM frameworks, Spring Data JPA brings a big overhead to the table, making it interesting for further investigations at the level of the internal operations that slow down the execution. An advantage of this framework is the reduced size of the code that was to be written for the database interaction, and a trade-off between the development speed and the execution times needs to be considered.

10.2.4. Overall conclusions

Oracle seems to be the worst choice in terms of reading data. It performs the worst using every framework. The same thing is valid for PostgreSQL, but for deleting data. In this case, the overhead is so big that it may be a better idea to change the RDBMS if the DELETE operation is frequently used.

An overall good solution is represented by MySQL, which is the only one that keeps close times on every ORM framework. SQL Server also has good average timing but without any outstanding performance on a specific operation.

After the warm-up phase, the only CRUD operation that had a visible and constant improvement was the READ one. For the rest of the operations, the improvements are generally noticed on a small number of entries (less than 20k or 50k). What may be surprising is that sometimes, usually for a large number of entries (500k), the warm-up phase is useless, as the execution times for the second iteration are even bigger.

After researching what could make Spring Data JPA’s transaction management act so slow, the conclusion was that it does not have only one EntityManager, but it has a SharedEntityManagerCreator which creates more objects of type EntityManager to avoid possible thread safety issues.

The classic EntityManager generated by EntityManagerFactory used in the JPA implementation does not offer thread safety, but in the proposed experiment this is not necessary. Using it makes Spring Data JPA faster in the interaction with the relational database management system.

Switching the transaction management turns Spring Data JPA into a similar solution in terms of performance like JPA or Hibernate. This means that probably the starvation situations mentioned by the YourKit profiler are happening somewhere inside the transaction mechanism proposed by Spring Data JPA.

Designing enterprise applications nowadays is a real challenge and involves a lot of high-level skills and experience. Besides designing [15] and assessing the architecture [16], selecting and applying the software development methodology [17], and testing the functionality [11], performance plays an essential role in modern software.