in query가 15개에서 끊기는 이유
218
작성한 질문수 3
안녕하세요 선생님. 좋은 강의 잘 듣고 있습니다.
orderV3_page() 함수를 생성해보고 query를 돌려보다가 문득 궁금증이 생겨 질문 드립니다.
################################시스템 로그 시작###################################
select
order0_.order_id as order_id1_6_0_,
member1_.member_id as member_i1_4_1_,
delivery2_.delivery_id as delivery1_2_2_,
order0_.delivery_id as delivery4_6_0_,
order0_.member_id as member_i5_6_0_,
order0_.order_date as order_da2_6_0_,
order0_.status as status3_6_0_,
member1_.city as city2_4_1_,
member1_.street as street3_4_1_,
member1_.zipcode as zipcode4_4_1_,
member1_.name as name5_4_1_,
delivery2_.city as city2_2_2_,
delivery2_.street as street3_2_2_,
delivery2_.zipcode as zipcode4_2_2_,
delivery2_.status as status5_2_2_
from
orders order0_
inner join
member member1_
on order0_.member_id=member1_.member_id
inner join
delivery delivery2_
on order0_.delivery_id=delivery2_.delivery_id limit ?
2020-03-22 18:52:21.584 INFO 22089 --- [nio-8080-exec-2] p6spy : #1584870741584 | took 1ms | statement | connection 14| url jdbc:h2:tcp://localhost/~/jpashop
select order0_.order_id as order_id1_6_0_, member1_.member_id as member_i1_4_1_, delivery2_.delivery_id as delivery1_2_2_, order0_.delivery_id as delivery4_6_0_, order0_.member_id as member_i5_6_0_, order0_.order_date as order_da2_6_0_, order0_.status as status3_6_0_, member1_.city as city2_4_1_, member1_.street as street3_4_1_, member1_.zipcode as zipcode4_4_1_, member1_.name as name5_4_1_, delivery2_.city as city2_2_2_, delivery2_.street as street3_2_2_, delivery2_.zipcode as zipcode4_2_2_, delivery2_.status as status5_2_2_ from orders order0_ inner join member member1_ on order0_.member_id=member1_.member_id inner join delivery delivery2_ on order0_.delivery_id=delivery2_.delivery_id limit ?
select order0_.order_id as order_id1_6_0_, member1_.member_id as member_i1_4_1_, delivery2_.delivery_id as delivery1_2_2_, order0_.delivery_id as delivery4_6_0_, order0_.member_id as member_i5_6_0_, order0_.order_date as order_da2_6_0_, order0_.status as status3_6_0_, member1_.city as city2_4_1_, member1_.street as street3_4_1_, member1_.zipcode as zipcode4_4_1_, member1_.name as name5_4_1_, delivery2_.city as city2_2_2_, delivery2_.street as street3_2_2_, delivery2_.zipcode as zipcode4_2_2_, delivery2_.status as status5_2_2_ from orders order0_ inner join member member1_ on order0_.member_id=member1_.member_id inner join delivery delivery2_ on order0_.delivery_id=delivery2_.delivery_id limit 100;
2020-03-22 18:52:21.619 DEBUG 22089 --- [nio-8080-exec-2] org.hibernate.SQL :
select
orderitems0_.order_id as order_id5_5_1_,
orderitems0_.order_item_id as order_it1_5_1_,
orderitems0_.order_item_id as order_it1_5_0_,
orderitems0_.count as count2_5_0_,
orderitems0_.item_id as item_id4_5_0_,
orderitems0_.order_id as order_id5_5_0_,
orderitems0_.order_price as order_pr3_5_0_
from
order_item orderitems0_
where
orderitems0_.order_id in (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
2020-03-22 18:52:21.621 INFO 22089 --- [nio-8080-exec-2] p6spy : #1584870741621 | took 0ms | statement | connection 14| url jdbc:h2:tcp://localhost/~/jpashop
select orderitems0_.order_id as order_id5_5_1_, orderitems0_.order_item_id as order_it1_5_1_, orderitems0_.order_item_id as order_it1_5_0_, orderitems0_.count as count2_5_0_, orderitems0_.item_id as item_id4_5_0_, orderitems0_.order_id as order_id5_5_0_, orderitems0_.order_price as order_pr3_5_0_ from order_item orderitems0_ where orderitems0_.order_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
select orderitems0_.order_id as order_id5_5_1_, orderitems0_.order_item_id as order_it1_5_1_, orderitems0_.order_item_id as order_it1_5_0_, orderitems0_.count as count2_5_0_, orderitems0_.item_id as item_id4_5_0_, orderitems0_.order_id as order_id5_5_0_, orderitems0_.order_price as order_pr3_5_0_ from order_item orderitems0_ where orderitems0_.order_id in (4, 11, 18, 25, 32, 39, 46, 53, 60, 67);
2020-03-22 18:52:21.641 DEBUG 22089 --- [nio-8080-exec-2] org.hibernate.SQL :
select
item0_.item_id as item_id2_3_0_,
item0_.name as name3_3_0_,
item0_.price as price4_3_0_,
item0_.stock_quantity as stock_qu5_3_0_,
item0_.author as author6_3_0_,
item0_.isbn as isbn7_3_0_,
item0_.actor as actor8_3_0_,
item0_.director as director9_3_0_,
item0_.artist as artist10_3_0_,
item0_.etc as etc11_3_0_,
item0_.dtype as dtype1_3_0_
from
item item0_
where
item0_.item_id in (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
2020-03-22 18:52:21.643 INFO 22089 --- [nio-8080-exec-2] p6spy : #1584870741643 | took 0ms | statement | connection 14| url jdbc:h2:tcp://localhost/~/jpashop
select item0_.item_id as item_id2_3_0_, item0_.name as name3_3_0_, item0_.price as price4_3_0_, item0_.stock_quantity as stock_qu5_3_0_, item0_.author as author6_3_0_, item0_.isbn as isbn7_3_0_, item0_.actor as actor8_3_0_, item0_.director as director9_3_0_, item0_.artist as artist10_3_0_, item0_.etc as etc11_3_0_, item0_.dtype as dtype1_3_0_ from item item0_ where item0_.item_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
select item0_.item_id as item_id2_3_0_, item0_.name as name3_3_0_, item0_.price as price4_3_0_, item0_.stock_quantity as stock_qu5_3_0_, item0_.author as author6_3_0_, item0_.isbn as isbn7_3_0_, item0_.actor as actor8_3_0_, item0_.director as director9_3_0_, item0_.artist as artist10_3_0_, item0_.etc as etc11_3_0_, item0_.dtype as dtype1_3_0_ from item item0_ where item0_.item_id in (2, 3, 9, 10, 16, 17, 23, 24, 30, 31, 37, 38, 44, 45, 51);
2020-03-22 18:52:21.654 DEBUG 22089 --- [nio-8080-exec-2] org.hibernate.SQL :
select
item0_.item_id as item_id2_3_0_,
item0_.name as name3_3_0_,
item0_.price as price4_3_0_,
item0_.stock_quantity as stock_qu5_3_0_,
item0_.author as author6_3_0_,
item0_.isbn as isbn7_3_0_,
item0_.actor as actor8_3_0_,
item0_.director as director9_3_0_,
item0_.artist as artist10_3_0_,
item0_.etc as etc11_3_0_,
item0_.dtype as dtype1_3_0_
from
item item0_
where
item0_.item_id in (
?, ?, ?, ?, ?
)
2020-03-22 18:52:21.656 INFO 22089 --- [nio-8080-exec-2] p6spy : #1584870741656 | took 0ms | statement | connection 14| url jdbc:h2:tcp://localhost/~/jpashop
select item0_.item_id as item_id2_3_0_, item0_.name as name3_3_0_, item0_.price as price4_3_0_, item0_.stock_quantity as stock_qu5_3_0_, item0_.author as author6_3_0_, item0_.isbn as isbn7_3_0_, item0_.actor as actor8_3_0_, item0_.director as director9_3_0_, item0_.artist as artist10_3_0_, item0_.etc as etc11_3_0_, item0_.dtype as dtype1_3_0_ from item item0_ where item0_.item_id in (?, ?, ?, ?, ?)
select item0_.item_id as item_id2_3_0_, item0_.name as name3_3_0_, item0_.price as price4_3_0_, item0_.stock_quantity as stock_qu5_3_0_, item0_.author as author6_3_0_, item0_.isbn as isbn7_3_0_, item0_.actor as actor8_3_0_, item0_.director as director9_3_0_, item0_.artist as artist10_3_0_, item0_.etc as etc11_3_0_, item0_.dtype as dtype1_3_0_ from item item0_ where item0_.item_id in (52, 58, 59, 65, 66);
위의 로그를 보시면 item에 in query가 15개에서 끊기고, 나머지 5개에 대하여 다시 쿼리를 날려주는데, 원래 이렇게 끊기는게 정상일까요?
################################시스템 로그 끝###################################
여기서 궁금한 점은 실제 쿼리에서는 한번에 20개에 대해서 in query를 날려주는데 (3)
1) p6spy log 찍힐때만 15개 단위로 짤라져서 찍히는건지,
- 이렇게 생각한 이유는
라는 옵션을 찾아서 입니다.
2) 아니면 실제로 h2 db에도 쿼리가 2번 쪼개져서 나가는지 궁금합니다.
- 실제로 h2가 전달받는 쿼리를 보고 싶은데 확인할 방법을 찾지 못하였습니다.
3) 아니면 HikariCP 커넥션 풀이 10개가 max라고 알고있는데 이런 커넥션 pool 갯수랑 상관이 있을까요?
- 이렇게 생각한 이유는
에서 connection 갯수가 15라고 확인을 했어서 입니다.
**결론: 왜 item 쿼리가 16번째에서 쪼개져서 한번 더 보내질까요?
답변 1
0
안녕하세요. 정상원님^^
주문 조회 V3.1: 엔티티를 DTO로 변환 - 페이징과 한계 돌파편을 보시면
hibernate.default_batch_fetch_size 옵션에 대해 이야기하는 내용이 있습니다.
제 생각에 이 옵션에 15라는 값이 설정되어 있는 것 같아요. 아니면 @BatchSize에 15라고 설정되어 있을꺼에요.
풀이나 이런쪽은 문제가 아닐꺼에요. 왜냐하면 남겨주신 로그중에 다음로그를 보면 ?가 15개 나오는데요. 이 로그는 하이버네이트가 남겨주는 로그거든요^^ 이미 풀까지 가기전에 여기서 15개로 결정이 되어버린 것이지요.
2020-03-22 18:52:21.641 DEBUG 22089 --- [nio-8080-exec-2] org.hibernate.SQL :
select
item0_.item_id as item_id2_3_0_,
item0_.name as name3_3_0_,
item0_.price as price4_3_0_,
item0_.stock_quantity as stock_qu5_3_0_,
item0_.author as author6_3_0_,
item0_.isbn as isbn7_3_0_,
item0_.actor as actor8_3_0_,
item0_.director as director9_3_0_,
item0_.artist as artist10_3_0_,
item0_.etc as etc11_3_0_,
item0_.dtype as dtype1_3_0_
from
item item0_
where
item0_.item_id in (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
주문 조회 V3.1: 엔티티를 DTO로 변환 - 페이징과 한계 돌파편을 다시 한번 보시면 궁금한 내용을 이제 명확하게 이해하실 수 있을꺼에요^^
강의 관련 외 질문입니다.
0
65
2
SpringBoot4 + Hibernate7 모듈 등록 방법 공유
0
86
1
BeanCreationException
0
86
3
Update 후 UpdateMemberResponse 매핑할 때
0
46
1
트랜잭션을 사용 안 할 때 커넥션은 언제 가져오나요?
0
98
2
페이징 + 검색조건 관련해서 질문드립니다.
0
70
1
Query Dsl Q파일 질문입니다.
0
81
1
루트 쿼리라는것은
0
58
1
메서드를 분리하는 기준
0
62
1
findAllWithMemberDelivery 메서드 질문드립니다.
0
108
3
연관관계 매핑을 안 쓸 경우, 사용해야 하는 전략
0
83
2
fetch join과 영속화와 OSIV의 관계
0
84
2
Distinct 사용 전 결과에 대한 의문
0
113
2
레포지토리 계층에서의 트랜잭션에 대한 의문
0
56
1
영속성 컨텍스트 생명주기의 신기한 부분이 있습니다.
0
78
2
dto 필드 속 엔티티 여부
0
58
1
뷰템플릿 사용 시
0
76
2
Result 클래스 관련 질문
0
56
1
@PostConstruct 프록시 관련 질문드립니다
0
85
1
DTO 대신 Form 사용은 안되나요?
0
133
1
OSIV ON 상태일 때
0
95
1
fetch join VS fetch join 페이징 궁금증
0
179
2
양방향 연관관계 알아보는 법?
0
104
1
16강 17강 간단 정리 이게 맞을까요 ?
0
165
2