I don't now why occur N+1 Problem using QueryDSL

Viewed 71

I used Spring Boot and QueryDSL.

When called findAllByWriterGroupByClient method in ClientMemoRepositoryImpl.java, [generated query 1] generated once and [generated query 2] generated several times.

Additionally, when read result of this query as Tuple in ClientMemoServiceImpl.java, [generated query 3] is generated many times.


  • ClientMemoRepositoryImpl.java
@Override
public List<Tuple> findAllByWriterGroupByClient(String searchKeyword, Long writerId, boolean hasAdminRole) {
    QClientMemo qClientMemo1 = new QClientMemo("cm1");
    QClientMemo qClientMemo2 = new QClientMemo("cm2");

    JPAQuery<Tuple> memoDtoJPAQuery = qf.select(
                    JPAExpressions.selectFrom(qClientMemo1)
                            .where(qClientMemo1.clientId.eq(qClientMemo.clientId).and(
                                            qClientMemo1.createdDate.eq(
                                                    JPAExpressions
                                                            .select(qClientMemo2.createdDate.max())
                                                            .from(qClientMemo2)
                                                            .where(qClientMemo2.clientId.eq(qClientMemo.clientId))
                                            )
                                    )
                            ),
                    new CaseBuilder()
                            .when(qClientMemo.createdDate.gt(LocalDateTime.now().minusDays(7)))
                            .then(1)
                            .otherwise(0).sum()

            )
            .from(qClientMemo);

    if ((!hasAdminRole) && writerId != null) {
        memoDtoJPAQuery = memoDtoJPAQuery.where(qClientMemo.writer.id.eq(writerId));
    }

    if (searchKeyword != null)
        memoDtoJPAQuery = memoDtoJPAQuery.where(
                qClientMemo.title.contains(searchKeyword)
                        .or(qClientMemo.content.contains(searchKeyword))
                        .or(qClientMemo.clientId.clientName.contains(searchKeyword))
                        .or(qClientMemo.writer.name.contains(searchKeyword))
        );

    return memoDtoJPAQuery
            .groupBy(qClientMemo.clientId)
            .orderBy(OrderByNull.DEFAULT)
            .fetch();
}
  • generated query 1
select
    (select
        clientmemo1_.id 
    from
        client_memo clientmemo1_ 
    where
        clientmemo1_.client_id=clientmemo0_.client_id 
        and clientmemo1_.created_date=(
            select
                max(clientmemo2_.created_date) 
            from
                client_memo clientmemo2_ 
            where
                clientmemo2_.client_id=clientmemo0_.client_id
        )
    ) as col_0_0_, sum(case 
        when clientmemo0_.created_date>? then ? 
        else 0 
    end) as col_1_0_ 
from
    client_memo clientmemo0_ 
group by
    clientmemo0_.client_id 
order by
    null asc
  • generated query 2
select
    [all fields of client_memo entity] 
from
    client_memo clientmemo0_ 
where
    clientmemo0_.id=?
  • generated query 3
select
    [all fields of client entity]
from
    client client0_ 
where
    client0_.id=?
  • ClientMemoServiceImpl.java
List<Tuple> clientMemos = clientMemoRepository.findAllByWriterGroupByClient(
                    readClientMemoDto.getSearchKeyword(),
                    readClientMemoDto.getUserId(),
                    hasAdminRole
            );

clientMemos.forEach(clientMemo -> {
    Map<String, Object> result = new HashMap<>();

    Integer newCnt = clientMemo.get(1, Integer.class);
    if (newCnt != null) {
        result.put("newMemoNum", newCnt);
    }

    MemoDto memoDto = new MemoDto();
    ClientMemo memo = clientMemo.get(0, ClientMemo.class);
    if (memo != null) {
        memoDto.ofClientMemo(memo);
        result.put("memoDetail", memoDto);
    }

    results.add(result);
});
  • ClientMemo.java
@Entity
@Table(name = "client_memo")
@Getter
@Builder
@AllArgsConstructor
@NoArgsConstructor
@DynamicInsert
public class ClientMemo {

    @JsonIgnore
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "title", nullable = false)
    private String title;

    @Lob
    @Column(name = "content")
    private String content;

    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="client_id")
    private Client clientId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="writer")
    private User writer;

    @Column(name = "created_date")
    private LocalDateTime createdDate;

    @Column(name = "updated_date")
    private LocalDateTime updatedDate;

    @Column(name = "is_admin")
    private boolean isAdmin;

}
  • Client.java
@Entity
@Table(name = "client")
@Getter
@Builder
@AllArgsConstructor
@NoArgsConstructor
@DynamicInsert
public class Client {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "client_name", nullable = false)
    private String clientName;

    @Column(name = "client_phone_num", nullable = false)
    private String clientPhoneNum;

    @Column(name = "service_start_time")
    private LocalDateTime serviceStartTime;

    @Column(name = "service_end_time")
    private LocalDateTime serviceEndTime;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "media_id")
    private Media media;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "person_charge_id")
    private User personCharge;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "normal_memo")
    private ClientMemo normalMemo;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "admin_memo")
    private ClientMemo adminMemo;

    @Column(name = "status", columnDefinition = "varchar(20) default 'UNCONTRACTED'")
    @Enumerated(EnumType.STRING)
    private ClientStatus status;

    @Column(name = "is_deleted", nullable = false)
    private boolean isDeleted;

}

All FetchType of Data Relationship are FetchType.LAZY.


I don't understand why occur this problem and why some people say that better using FetchType.LAZY than FetchType.EAGER.

Do I understand QueryDSL or SpringBoot correctly?

Thanks

1 Answers

You should share with us your Jpa entities.

In my opinion, you should have setted some associations in your entity (with @OneToMany, etc..), probably in Eager Mode (which is the default mode).

When you try to load one instance of your object from the database, Hibernate loads the associations as well. In eager mode, hibernate loads the associations by querying the database (which generates additional sql queries).

If you define your associations in Lazy mode, Hibernate will populate your entity jpa with some proxy objects and will fetch the associations later, only when you access it (so that means the sql query of your association is deffered when you only try to access the association in your code).

Related