CODEWITHSUNDEEP
javahibernatehql
HappyEngineer
HappyEngineer

Reputation: 4055

Hibernate is much faster when executing a native query

The following query is supposed to return about 800 objects. The problem is that hibernate actually executes 800 queries to get them all. It appears to execute one query to get the ids and then executes one query for every object to get the specific data about the object. It takes over 60 seconds for this query to return.

List<AUser> result = em.createQuery("FROM AUser where company=:companyId")
.setParameter("companyId",company.getId())
.getResultList();

The native query is much faster.

List<AUser> result = em.createNativeQuery("select a.* FROM AUser a where a.company=:companyId")
.setParameter("companyId",company.getId())
.getResultList();

The above query takes less than one second to return.

Why the difference?

Upvotes: 4

Views: 6460

Answers (2)

Ali Sağlam
Ali Sağlam

Reputation: 1520

This might be n+1 select problem in Hibernate. You shoud follow some best practices to enhance your query performance. There are multiple options like Lazy Loading, Fetch Mode, Fetch Join etc.

http://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html n+1 query issue

Regards...

Upvotes: 2

ChssPly76
ChssPly76

Reputation: 100806

The original issue is caused by property(ies) of AUser being eagerly fetched (confirmed by HappyEngineer in comment).

Answering the follow-up question:

Generally, the best approach is to map associations as lazy:

@ManyToOne(fetch = FetchType.LAZY)
private Company company;

You can then override fetch mode within your query by using join fetch:

select user
  from AUser user left join fetch user.company
 where user.company.id = :companyId

See Associations and Joins chapter in Hibernate documentation for more details.

Upvotes: 6

Related Questions