Making hibernate not include fields from joined tables in select clause

Question

I'm running into a performance problem in my SQL using Hibernate's DetachedCriteria. I have a few many-to-one relationships and when Hibernate generates the SQL it's including all of the fields from the tables that are joined in the FROM. When this happens, it's taking MySQL a long time to run the query (which also has an order by and sub query adding to the issue). For my currently 50k of records ~6sec. When I remove the unnecessary fields in the SELECT to just the domain object I'm concerned about, it runs well under 500ms.

Is there a way I can tell Hibernate not to include the fields from the joins?

I've tried setting the fetch parameter in the mapping files to 'join' and 'select' and it makes no difference in the generated SQL.

I've also tried setting the distinct root entry, but from what I've read, that doesn't work with paging (which I'm also doing).

I could try and write the query as HQL but with the sub query it just makes it more of a headache.

Answer 1

You can set a Projection that contains a list of only the properties that you are interested in.

Here is an example from a past project:

Criteria criteria = getSession().createCriteria(Something.class);
criteria.createCriteria("user", "u");

// only retrieve the following fields: id, state, viewCount, user.username
ProjectionList properties = Projections.projectionList();
properties.add(Projections.property("id"));
properties.add(Projections.property("state"));
properties.add(Projections.property("viewCount"));
properties.add(Projections.property("u.username"));

criteria.setProjection(properties);

Answer 2

Have you tried to set Lazy = true for the classes you don't want to load?

Answer 3

If I add list of my properties to projection, then criteria result can't cast to List of Something.class type!!!!(((( Result is List < Object[] >.

Answer 4

Hi, I have the same question, it sounds strange there's no way to do this. It sounds as a pretty common scenario, use a JOIN to filter, but without loading the entire associated entity(ies).