CODEWITHSUNDEEP
mysqlsqlperformancehibernatejpa
NSF
NSF

Reputation: 2549

Hibernate performance tuning for multiple similar select queries

The problem I have is that I have lots of similar queries in a batch:

select id, a, b, c from records where a = 1 and b = 2
select id, a, b, c from records where a = 5 and b = 3
select id, a, b, c from records where a = 2 and b = 5
select id, a, b, c from records where a = 3 and b = 4
...

a batch normally consists of 1000 queries, which takes about 0.5s to finish, with a and b indexed. Auto commit is on and all connections are managed in bonecp connection pool. Hibernate is used to map each result into an object instance.

My questions are:
1. Is it gonna make a difference if I combine them in a single query, even all db connections are live and the service doesn't need to wait for the connection to be established?
2. If yes, what's the best practice to do that in hibernate?
3. If not, is it gonna make a difference if I execute all the queries concurrently (instead of one by one in the current case), though the connection pool only allows a certain number of queries to be executed at the same time?

Upvotes: 0

Views: 581

Answers (1)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153690

Hibernate offers some strategies for optimizing fetch retrievals when loading associated entities (BATCH, JOIN, SUBSELECT) but for HQL/JPQL you can at best generate the query you'd have done it with a native query.

  1. You need to disable auto-commit. That's not playing well with Hibernate write-behind transactions and it hinders performance too.

  2. Since you are using MySQL you could try to create one IN query like this:

    select id, a, b, c 
from records 
where (a, b) IN ((1, 2), (5, 3), (2, 5), (3, 4))

  3. If you are not interested in fetching entities (for updating/removing them) and you only need a projection (id, a, b, c), then a native query is a better choice than a HQL one (assuming the MySQLDialect even supports this syntax).

  4. I wouldn't execute those concurrently. The CP lock contention is going to be your next bottleneck and for index-only-scans a single IN query will perform better than many small ones.

Upvotes: 1

Related Questions