How to retrieve an entity which is an attribute for another entity with the less overhead possible?

Question

I have the following entity:

@Entity
public class A{
      ...
      @ManyToOne( optional=false, fetch=FetchType.LAZY )
      @NotFound( action=NotFoundAction.EXCEPTION )
      @JoinColumn( name="ID_CLASSB", nullable=false, insertable=true,  updatable=true )
      private B b;
      ...
      private String anIdentifier;
      ...
      @OneToMany(fetch=FetchType.LAZY)...
      private List<C> manyObjects;
      ...
      @OneToMany(fetch=FetchType.LAZY)...
      private List<D> soMany;
}

As you can see entity B is an attribute in entity A.

B entity has no reference to A entity and is made by simple attributes:

@Entity
public class B{

   @Column(...)
   private String field1;

   @Column(...)
   private int field2;
   ...
}

Now, I'd like to retrieve all B entities from A entities where the "anIdentifier" attribute matches a specific value. My query, for now, would be:

"select a.b from A a where a.anIdentifier='identifier'"

Problem is that this second query is particularly slow...and I guess it is because hibernate still creates the whole entity A (and thus all its attributes) before extracting the entity B.

So, is there another, most efficient, way to accomplish my goal?

Does `B` have a back reference to `A`? I wouldn't expect this query to initialize all `A`s, but you can log the generated SQL statements to check that. — Predrag Maric, Mar 31 '15 at 08:53
and the SQL is? You can't ask about overhead unless you give the reference information of what it does right now, and showing the SQL invoked is the only way — Neil Stockton, Mar 31 '15 at 09:58

score 1 · Answer 1 · edited May 23 '17 at 11:56

This is a simple query and it should work fast. Fact of fetching A or not would not affect it as the relationships would be lazy loaded (nevertheless the A is probably not fetched)

The typical reasons why it is slow is: - no index on foreign key of A to B relationship (ID_CLASSB of table A), - you have a lot of As in DB and no index on anIdentifier

So check their existence in DB first.

Generally, you need to be able to see the generated sql to be able to reason (and test) the performance. You can check this answers how it is done hibernate How to print a query string with parameter values when using Hibernate

Once you have the real query, you test in the DB, for example in MySQL it is the explain command.

How to retrieve an entity which is an attribute for another entity with the less overhead possible?

1 Answers1