Hibernate is much faster when executing a native query.

Question

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?

Answer 1

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.