JPA Querying Performance Tips
July 31, 2017
Hello readers. It's been quite a while!
In this post, I will share some tips that will help your application perform fast while using JPA, and Hibernate as a JPA provider. JPA can cause quite an overhead if it's not used correctly from the start of the project. In some companies, it has actually gotten such a bad reputation to the point that a lot of operations has to be done using pure JDBC.
To make sure that querying using JPA performs well, basically it boils down to:
- Eager and Lazy Loading;
- Page the data;
- Consider not using 'Select * FROM';
- Don't query in a loop;
- Don't be afraid to go Native.
Eager and Lazy Loading
Eager loading happens for @ManyToOne annotated attributes by default, and when declared explicitly with FetchType.EAGER. What this means is that every time an entity is retrieved, another select is issued to the database for every eager attribute.
For that reason, I always anotate @OneToMany and @ManyToOne attributes with FetchType.LAZY. Whenever I need to load the lazy attribute, I use the "fetch" keyword, and in some cases a seperate Query can be issued as well. For example:
Page the data
In 99% of the cases where you need to query a big table, you don't have to retrieve all the data. That said, unless you are querying a small table that you know won't increase exponentially in size, always paginate your query using the following syntax:
Consider not using 'Select * FROM'
If you querying a table that has more than 30 attributes, this syntax can slow down the database. There's also a huge slow down when one of those attributes is a BLOB or CLOB types. I've seen a page that loaded a BLOB attribute unnecessarily taking 30s to load, to less than 1s after changing the query to select specific attributes.
In these cases it is preferred that you create a Value Object that will hold specific values from the table.
Don't query in a loop
Usually it is better to fetch all the data you need right from the start, using join fetch for example. But that's not always possible. But whatever you do, don't query in a loop.
Try as much as you can to make a query that will fetch all the data (paged if there's too much registers). Just be careful of using the IN to fetch de dependencies. Generally IN will slow down the database if used with 100+ registers.
There are some techniques that are faster than using IN, such as:
- Using a Temporary table;
- Using XMLTable (if the database supports it).
Don't be afraid to go Native
The database can do a lot of work for you. So use it in your favor. If a process can be optimized to fetch/insert all the data in one or more SQL queries, that would be too slow if done individually in Java code, don't be afraid to use Native Queries.
For example, a process done in Java like this:
- Iterate over user input;
- For each item, update 10000+ registers and insert the data;
Could be a lot faster if optimized and done like this:
- Create a trigger on the table that will be responsible for updating the 10000+ registers;
- Create a temporary table and insert all the input registers in it;
- Call a command that does a 'INSERT SELECT' from the temporary table to the definitive one;
- Since only one command was issued, the trigger will be activated only once, making the operation most likely faster;
As stated in this article, JPA can be used without performance bottlenecks, if the correct use of it is enforced. I hope the information here presented was useful! See you next time!