Query with a lot of fields and joins

Question

I have to map a legacy database with tables that have a lot of fields and relations. For example (simplified code),

@Entity
@Table(name = "VISIT")
public class VisitEntity {
   @Id
   private Integer Id;

   @ManyToOne
   @JoinColumn(name = "CD_COMPANY")
   private CompanyEntity company;

   @ManyToOne
   @JoinColumn(name = "CD_EMPLOYEE")
   private EmployeeEntity employee;

   @ManyToOne
   @JoinColumn(name = "CD_VISIT_TYPE")
   private VisitTypeEntity visitType;

   // A lot more fields and relations
}

All other entityes used in the VisitEntity also have a lot of fields and more relations. For example, the EmployeeEntity is mapped as follows:

@Entity
@Table(name = "EMPLOYEE")
public class EmployeeEntity {
   @Id
   private Integer Id;

   @Column(name = "EMP_NAME")
   private String empName;

   @ManyToOne
   @JoinColumn(name = "CD_DEPARTMENT")
   private DepartmentEntity department;

   @ManyToOne
   @JoinColumn(name = "CD_CITY")
   private CityEntity city;

   @ManyToOne
   @JoinColumn(name = "CD_CATEGORY")
   private CategoryEntity category;

   // A lot more fields and relations
}

With this classes, a simple find() in the VisitEntity will generate a query with more than 400 columns and more than 40 joins. I'm affraid that, in the future, this will represent a performance problem. I see two ways of reducing this number (assuming that when I use a VisitEntity I only need the field Id and name from the EmployeeEntity:

1. Insted of using the find method of the entityManager use a TypedQuery referencing only a subset of the filed of the relation tables. For example:

SELECT v, e.id, e.empName, <more subsets from other tables> FROM VisitEntity v LEFT JOIN EmplooyEEEntity e WHERE v.id = :id

Even using query with a smaller list of fields, I think the JPA will have to create all the main entities.

2. Use auxiliary entities. For example instead of unsing an EmployeeEntity I'll create a VisitEmployeeEntity with the fields Id and Name only. This way, the find() method will query only the 2 fields of the EMPLOYEE table.

I appreciate more the second approach, even if it represent that I have to create more small classes.

Since I'm also new to the ORM world, what is your experience/opinon in this matter?

Answer 1

Even when creating a fresh application and deciding to use an ORM, it can be very tricky, especially if you're not familiar with how the ORM works. An ORM can make it seem easier to deal with the database, but that's really only true with a simple schema. When things get more complex, you not only have to understand the database but also the ORM layer.

In your example code, you might not know it but @ManyToOne relationships have a default FetchType of EAGER. This means that when loading a single VisitEntity, the ORM layer will fire out several more queries to fetch those eager relations and the resulting entity graph will be huge.

The problem with fetch types is that you can create a JPQL query to eagerly fetch a lazy relationship, but not the opposite. This means that the first thing you should do is to make sure you declare your @ManyToOne relationships as lazy. This at least prevents the entity graph explosion due to the default eager relationships.

There are also a lot of other tricks you can use, such as constructor queries to make it more efficient to fetch the data you need, even if you've just directly mapped entities to the schema (which might not be useful from a code point of view).

You've got a lot of work ahead of you (especially if you're new to ORM), and for some entities/graphs it might be better to use a different approach than for others. No simple solutions, as usual.

Even using query with a smaller list of fields, I think the JPA will have to create all the main entities.

Entities won't be created unless they're in the select clause (or eagerly fetched to be included in the entity graph). If you select e.empName, that doesn't mean an EmployeeEntity will be created.

Answer 2

The ORMs are a helpful tool to abstract a lot o things related to the update, insert, select and delete informations from a database.

I think they are popular because most of the applications does not have any strong requisites of performance. Mostly because most of them are simple and/or the network and database specifications are generous enough to accommodate this "extra" informations from the entity when making a update/select/etc.

But, this ORMs are very, very permissive. They can assume some default behavior that are very bad for the application performance. So:

With this classes, a simple find() in the VisitEntity will generate a query with more than 400 columns and more than 40 joins

This happens because the @OneToOne and @ManyToOne are EAGER by default on the JPA specification. Any team that have some experience using JPA will threat this always with LAZY. As Hibernate documentation recommends:

The Hibernate recommendation is to statically mark all associations lazy and to use dynamic fetching strategies for eagerness. This is unfortunately at odds with the JPA specification which defines that all one-to-one and many-to-one associations should be eagerly fetched by default. Hibernate, as a JPA provider, honors that default.

This simple modification can prevent a lot of performance problems on the future.

The second approach is one of of the strategies to boost the select performance. This is particularly nice when you need to extract the best performance from a query that are very important in your application. Otherwise, I think it's very painful create a lot of classes to represent the different selects that I would like to do.