For one of my presentations, I wanted to have some numbers regarding the efficiency of projections.
If you don’t know what projections are, read this previously written article.
I basically wanted to compare the performance of entity based fetching against projection fetching using Spring Data JPA projections with 1000 rows in the DB.
The model
I’ve used the example database from MySQL, called Sakila. I modeled the Film table with all the properties and set every association to lazy.
The projection was built up on 3 attributes: id, title, rating.
How I measured?
I’ve created a two step measurement.
- warmup
- actual execution
Additionally I’ve disabled the JIT compiler to prevent it from removing the repository call. I’ve tested it with MySQL 5.7 and the application was running with 1G max heap size.
Note that the numbers are not that important as they are relevant for only my setup. What’s important instead is the proportion of the execution times.
The measurement
The warmup step consisted of 5 executions of the fetching and the real measurement was 1000 executions. As I mentioned earlier I had 1000 rows in the DB for this entity.
On average, the entity fetching was ~311ms and the Spring Data JPA projection fetching was ~745ms meaning that the projection fetching was way slower (more than 2x). This was a very surprising result as I expected the projections to be faster.
I decided to log out the generated SQLs and the projection looked fine, so there should be something in Spring which was invisible so I’ve created a Criteria API projection as well with the same attributes as for the Spring Data JPA projection.
The result confirmed my feelings. There is something within Spring Data which causing the difference. The average execution time of this was ~48ms which means that using Criteria API/JPQL for projections might be 15(!) times faster than Spring Data JPA. This is a huge difference when you are heavily building the application on projections.
Finding the issue
As the queries looked the same, I opened VisualVM and profiled the application. What I saw is that the DefaultConversionService class was instantiated multiple times and it ate up 90% of the time. The problematic instantiation was in the ProjectingConverter and in the ProjectingMethodInterceptor classes.
I reached out to the Lead of the Spring Data project, Oliver Gierke on Twitter and shortly it turned out that the issue is real. I quickly created a ticket for it and Oliver instantly implemented the required changes. The improved projection support will be there from the following Spring Data versions: 1.12.12 (Hopper SR12), 1.13.5 (Ingalls SR5), 2.0 RC1 (Kay).
Despite the fact that this issue has been solved, Spring Data projections are still slower than the Criteria API/JPQL brothers. With the updated code, fetching 1000 projections is executed in ~159ms which is a great improvement compared to the original ~745ms execution time.
Conclusion
Projection is a great tool to improve the performance of an application as they could be 6x faster than entity queries. Using projections with Spring Data JPA means less coding, no need for writing query but it also means that it will be slower especially if you are not using the improved version of it.
If performance is critical for a certain part of your application, use JPQL/Criteria API projections over Spring Data JPA ones as they have considerably better performance.
In case of feedback/questions, feel free to reach me out in the comments or on Twitter.
Thx for findinf this issue and speeding up our apps 🙂
Glad I could help! 🙂