Java – hibernate is much faster when executing native queries

The following query should return about 800 objects The problem is that hibernate actually executes 800 queries to get them It seems to execute a query to get the ID, and then execute a query for each object to get specific data about the object This query takes more than 60 seconds to return

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

Native queries are much faster

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

The return time of the above query is less than one second

Why is it different?

Solution

The initial problem was due to the eager acquisition of auser's property (confirmed by happyengineer in the comments)

Answer follow-up questions:

In general, the best way is to map the association to lazy:

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

You can then use join fetch to override the extraction pattern in the query:

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

For more details, see the associations and joins chapter in the hibernate documentation