Hibernate JPA is generating select query everytime when findOne(String id) is called with same parameter value

Question

Hibernate findOne(String id) is taking to much time (more than 300ms) in our case. This is not consistent as well. For some request, it is returning the result in less than 100ms.

So we want to improve the performance of findOne where it should not take more than 100ms of time. Since the table has more than 100k records, we have indexing in place for Entities involved in select query.

We are calling this findOne method for each client request. Only the value in where clause will change and not the columns or entities involved in this.

So basically, it would be great if Hibernate could create/generate the select query once and reuse it for all the findOne calls on the repository.

Can someone help me with this?

Answer 1

There are a few optimizations you can consider here.

1. HTTP Session
2. Second Level Cache
3. Using @NamedQuery where possible
4. Don't use string-based primary keys.

HTTP Session Cache

You could always store the object you fetch with the #findOne method in the HTTP Session when the client authenticates and the value does not exist. This will eliminate the queries on all subsequent authenticated requests. The only concern you must take is if the object in question is modified by the current user, you will want to replace the value in the session with the updated copy.

Lots of web applications use this mechanism. If you've ever logged into your credit card or bank's online account system and called them to change something, you've probably noticed that you must log-out and back in for those changes to be visible. That's the same concept.

Second Level Cache

This solution works great for even for non-HTTP based applications which use Hibernate. In this case, you have a datastore that is on the application side which you configure to store query results for infrequently changed information. Rather than take the database and network cost on each query to something that exists in the cache, Hibernate will hydrate from the cache first if the entries have not yet expired.

The nice thing about the 2LC is that you can configure per-query if its to be cached and you can also configure varying timeouts and other configuration options per-entity type.

@NamedQuery

Sometimes complex queries that you execute come at a cost of the JPQL/HQL being reparsed. If you find this being a problem, you might want to look into seeing whether the query can be moved to a static @NamedQuery instead.

The benefit of a @NamedQuery is that the parsing, validation, and building of the underlying parse tree is done once at application boostrap. This means at runtime, Hibernate simply fetches that definition, grabs the pre-generated SQL, applies parameter bindings and hands that to the database. Short of parameter type validation, the overhead is super minimal.

No String based Keys

Depending on your database platform, String-based primary keys can perform much worse than their numeric counter parts, particularly if your query is involved in joins. If your string field is configured to support unicode (e.g. NVARCHAR or NCHAR columns), you're compounding the problem.

Using a String for a @NaturalId is perfectly fine because you'd typically just be applying a where predicate against that field in which you could apply various indices as your queries dictate.

But I'd advise if your query uses a String-based primary key, move that to a @NaturalId and use a surrogate numeric-based key for the primary key. You'll notice your table joins will improve in performance significantly as the comparison and hash lookup between them is much faster.