개요
QueryDSL에서는 1:n 컬렉션(Collection) 연관관계에 페이징 쿼리 (limit, offset)에 대해 쿼리 생성시 offset, limit 쿼리를 제외한 후 메모리에 모든 데이터들을 조회한 뒤 limit, offset 파라미터를 이용해 in memory로 페이징을 처리하여 데이터를 반환하여줌. 이것이 성능에 크나큰 영향을 끼치므로 될 수 있으면 발생하지 않도록 주의하여 사용해야함. 이와 관련된 옵션이 있던 것으로 기억하니 필요시 찾아볼것. ( 애초에 일어나는걸 방지하기 위해 false로 값을 세팅한다던가 )
@Slf4j
@Repository
public class CustomCustomerRepositoryImpl extends Querydsl4RepositorySupport implements CustomCustomerRepository {
...
@Override
public List<CustomerDto> getCustomers(CustomersSearchCondition condition, Pageable pageable) {
JPAQuery<CoCustEntity> query =
selectFrom(coCustEntity).distinct()
.leftJoin(coCustEntity.usrs, usrEntity).fetchJoin()
.leftJoin(usrEntity.coSalesofic, coSalesoficEntity).fetchJoin()
.where(
integerEq(condition.getCustomerCode(), coCustEntity.custCd),
stringContains(condition.getCustomerName(), coCustEntity.custNm),
stringContains(condition.getUserName(), usrEntity.memNm),
stringContains(condition.getUserId(), usrEntity.memId),
integerEq(condition.getRegionCode(), coCustEntity.regnCd)
);
if (pageable.getSort().isEmpty()) {
query.orderBy(coCustEntity.custCd.desc());
} else {
applySorting(pageable.getSort(), query, CustomerDto.class);
}
List<CoCustEntity> results = query
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
return results.stream().map(coCust -> {
List<UsrEntity> usrs = coCust.getUsrs();
CustomerDto returnEntity;
if (!usrs.isEmpty()) {
returnEntity = new CustomerDto(coCust, usrs.get(0));
} else {
returnEntity = new CustomerDto(coCust);
}
return returnEntity;
}).collect(Collectors.toList());
}
}
실행되는 SQL
select distinct
cocustenti0_.cust_cd as cust_cd1_4_0_,
usrs1_.mem_no as mem_no1_29_1_,
cosalesofi2_.salesofic_cd as salesofi1_5_2_,
cocustenti0_.addr as addr2_4_0_,
cocustenti0_.bas_mng_grp_cd as bas_mng_3_4_0_,
cocustenti0_.chg_regn_cont as chg_regn4_4_0_,
cocustenti0_.co_div_cd as co_div_c5_4_0_,
cocustenti0_.cust_memo_cont as cust_mem6_4_0_,
cocustenti0_.cust_nm as cust_nm7_4_0_,
cocustenti0_.reg_dtm as reg_dtm8_4_0_,
cocustenti0_.reg_mem_no as reg_mem_9_4_0_,
cocustenti0_.regn_cd as regn_cd10_4_0_,
cocustenti0_.tel_no as tel_no11_4_0_,
cocustenti0_.use_yn as use_yn12_4_0_,
usrs1_.acnt_actv_yn as acnt_act2_29_1_,
usrs1_.acnt_block_yn as acnt_blo3_29_1_,
usrs1_.cust_cd as cust_cd13_29_1_,
usrs1_.salesofic_cd as salesof14_29_1_,
usrs1_.latest_login_dtm as latest_l4_29_1_,
usrs1_.login_fail_cnt as login_fa5_29_1_,
usrs1_.mem_email as mem_emai6_29_1_,
usrs1_.mem_id as mem_id7_29_1_,
usrs1_.mem_nm as mem_nm8_29_1_,
usrs1_.mem_pwd as mem_pwd9_29_1_,
usrs1_.mngr_memo_cont as mngr_me10_29_1_,
usrs1_.reg_dtm as reg_dtm11_29_1_,
usrs1_.tel_no as tel_no12_29_1_,
usrs1_.cust_cd as cust_cd13_29_0__,
usrs1_.mem_no as mem_no1_29_0__,
cosalesofi2_.addr as addr2_5_2_,
cosalesofi2_.chg_regn_cont as chg_regn3_5_2_,
cosalesofi2_.reg_dtm as reg_dtm4_5_2_,
cosalesofi2_.reg_mem_no as reg_mem_5_5_2_,
cosalesofi2_.regn_cd as regn_cd6_5_2_,
cosalesofi2_.salo_nm as salo_nm7_5_2_,
cosalesofi2_.tel_no as tel_no8_5_2_,
cosalesofi2_.use_yn as use_yn9_5_2_
from co_cust cocustenti0_
left outer join usr usrs1_ on cocustenti0_.cust_cd=usrs1_.cust_cd
left outer join co_salesofic cosalesofi2_ on usrs1_.salesofic_cd=cosalesofi2_.salesofic_cd
아래와 같은 경고문구를 확인할 수 있음. 첫번재페이지결과값/최대결과값 처리가 메모리에서 실행될 것이라고 경고하는 것이라 추측됨.
o.h.h.internal.ast.QueryTranslatorImpl :389 : HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!
java code
@Slf4j
@Repository
public class CustomCustomerRepositoryImpl extends Querydsl4RepositorySupport implements CustomCustomerRepository {
...
@Override
public List<CustomerDto> getCustomers(CustomersSearchCondition condition, Pageable pageable) {
JPAQuery<CoCustEntity> query =
selectFrom(coCustEntity)
.where(
integerEq(condition.getCustomerCode(), coCustEntity.custCd),
stringContains(condition.getCustomerName(), coCustEntity.custNm),
usrsMemNmLike(condition.getUserName()),
usrsMemIdLike(condition.getUserId()),
integerEq(condition.getRegionCode(), coCustEntity.regnCd)
);
if (pageable.getSort().isEmpty()) {
query.orderBy(coCustEntity.custCd.desc());
} else {
applySorting(pageable.getSort(), query, CustomerDto.class);
}
List<CoCustEntity> results = query
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
return results.stream().map(coCust -> {
List<UsrEntity> usrs = coCust.getUsrs();
CustomerDto returnEntity;
if (!usrs.isEmpty()) {
returnEntity = new CustomerDto(coCust, usrs.get(0));
} else {
returnEntity = new CustomerDto(coCust);
}
return returnEntity;
}).collect(Collectors.toList());
}
...
private BooleanExpression usrsMemNmLike(String userName) {
return hasText(userName) ? coCustEntity.usrs.any().memNm.like(userName) : null;
}
private BooleanExpression usrsMemIdLike(String userId) {
return hasText(userId) ? coCustEntity.usrs.any().memId.like(userId) : null;
}
}