0

I'm having a database performance concern.

Let's suppose my data model looks like follows.

UserTable.java

@Id
@Column(name = "USER_ID", nullable = false)
private Long userId;

// other user fields

@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
private Set<ProfilePhotoTable> photos = new HashSet<>();

@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
private Set<AlbumPhotoTable> photos = new HashSet<>();

PhotoTable.java

@Id
@Column(name = "PHOTO_ID", nullable = false)
private Long photoId;

// other photo fields

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "USER_ID")
private UserTable user;

ProfilePhotoTable.java

@Entity
@Table(name = "PROFILEPHOTO")
@PrimaryKeyJoinColumn(name = "PROFILEPHOTO_ID", referencedColumnName = "PHOTO_ID")
public class ProfilePhotoTable extends PhotoTable

AlbumPhotoTable.java

@Entity
@Table(name = "ALBUMPHOTO")
@PrimaryKeyJoinColumn(name = "ALBUMPHOTO_ID", referencedColumnName = "PHOTO_ID")
public class ProfilePhotoTable extends PhotoTable

Now let's say I want to write a query that would fetch all the photos for the user - all profile photos and all album photos.

However, I don't want the photos to be fetched every time that I'm requesting the User information from the database, and this is why I specified the fetch = FetchType.LAZY on the photo fields.

Basically, I have a doubt between the two approaches.

  1. Two independent queries, the first one fetching the UserTable from the database by the ID, and the second fetching the photos, something like SELECT * FROM Photo WHERE userId = :userId.

  2. One single query that would join fetch the user with the corresponding photos. However, I'm not quite sure how this query would go, since the photos are separated in the Album and Profile photos. I found in this article that something like

    comments = entityManager.createQuery(

    "select pc " + "from PostComment pc " + "join fetch pc.post " + "where pc.review = :review", PostComment.class) .setParameter("review", review) .getResultList();

should be used, but I'm not sure how to apply it to my use-case.

The question is, which approach is better in terms of performance, and if 2nd, how the query should be structured?

1
  • ..you can make the user eager (="not lazy") (to achieve what you want (as I understand) ..and "lazyness" makes most sense on the "N:-" side of the association) Commented Jan 24, 2019 at 15:52

2 Answers 2

1

If your are using JPA 2.1 or higher you can use @NamedEntityGraph to control, if you want to fetch your data "EAGER" or "LAZY".

This article explains it very well: https://thoughts-on-java.org/jpa-21-entity-graph-part-1-named-entity/

Sign up to request clarification or add additional context in comments.

Comments

0

I think the answer would depend on what fraction of your user queries also query for photos. If the answer is a small fraction, then it would make sense to keep it lazy so that you don't have to pull them all the time. I would anyway not store photos in a traditional relational database type of setup. I would put them in some kind of object store and only keep ids back with RDBMS.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.