학습하는 분들께 도움이 되고, 더 좋은 답변을 드릴 수 있도록 질문전에 다음을 꼭 확인해주세요.
안녕하세요 영한님 jpa 강의 정말 재미있게 잘 듣고 있습니다. 이번에 페이징 한계돌파 강의를 들으면서 실습을 하던 중 16:10쯤에 저의 개발환경에서 발생한 쿼리가 영한님의 개발환경에서 발생한 쿼리와 다른 것 같아서 질문드립니다. where __row__ >= 2 and __row__ < 102; 쿼리로 페이징이 적용되어 결과는 원하는 대로 나왔지만, 예상과는 조금 다르게 아래와 같은 서브 쿼리가 나갔습니다. 데이터베이스 방언 문제는 아닌 것 같고 jpa 버전 문제일까요?
2022-05-04 01:14:49.015 DEBUG 21484 --- [nio-8081-exec-6] org.hibernate.SQL : with query as (select inner_query.*, row_number() over ( order by current_timestamp) as __row__ from ( 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_.order_status as order_st3_6_0_, member1_.city as city2_4_1_, member1_.street as street3_4_1_, member1_.zip_code as zip_code4_4_1_, member1_.name as name5_4_1_, delivery2_.city as city2_2_2_, delivery2_.street as street3_2_2_, delivery2_.zip_code as zip_code4_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 ) inner_query ) select order_id1_6_0_, member_i1_4_1_, delivery1_2_2_, delivery4_6_0_, member_i5_6_0_, order_da2_6_0_, order_st3_6_0_, city2_4_1_, street3_4_1_, zip_code4_4_1_, name5_4_1_, city2_2_2_, street3_2_2_, zip_code4_2_2_, status5_2_2_ from query where __row__ >= ? and __row__ < ? 2022-05-04 01:14:49.035 INFO 21484 --- [nio-8081-exec-6] p6spy : #1651594489035 | took 3ms | statement | connection 8| url jdbc:h2:tcp://localhost/~/jpashopping with query as (select inner_query.*, row_number() over (order by current_timestamp) as __row__ from ( 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_.order_status as order_st3_6_0_, member1_.city as city2_4_1_, member1_.street as street3_4_1_, member1_.zip_code as zip_code4_4_1_, member1_.name as name5_4_1_, delivery2_.city as city2_2_2_, delivery2_.street as street3_2_2_, delivery2_.zip_code as zip_code4_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 ) inner_query ) select order_id1_6_0_, member_i1_4_1_, delivery1_2_2_, delivery4_6_0_, member_i5_6_0_, order_da2_6_0_, order_st3_6_0_, city2_4_1_, street3_4_1_, zip_code4_4_1_, name5_4_1_, city2_2_2_, street3_2_2_, zip_code4_2_2_, status5_2_2_ from query where __row__ >= ? and __row__ < ? with query as (select inner_query.*, row_number() over (order by current_timestamp) as __row__ from ( 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_.order_status as order_st3_6_0_, member1_.city as city2_4_1_, member1_.street as street3_4_1_, member1_.zip_code as zip_code4_4_1_, member1_.name as name5_4_1_, delivery2_.city as city2_2_2_, delivery2_.street as street3_2_2_, delivery2_.zip_code as zip_code4_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 ) inner_query ) select order_id1_6_0_, member_i1_4_1_, delivery1_2_2_, delivery4_6_0_, member_i5_6_0_, order_da2_6_0_, order_st3_6_0_, city2_4_1_, street3_4_1_, zip_code4_4_1_, name5_4_1_, city2_2_2_, street3_2_2_, zip_code4_2_2_, status5_2_2_ from query where __row__ >= 2 and __row__ < 102; 2022-05-04 01:14:49.038 DEBUG 21484 --- [nio-8081-exec-6] org.hibernate.SQL : select orderitems0_.order_id as order_id5_5_0_, orderitems0_.order_item_id as order_it1_5_0_, orderitems0_.order_item_id as order_it1_5_1_, orderitems0_.count as count2_5_1_, orderitems0_.item_id as item_id4_5_1_, orderitems0_.order_id as order_id5_5_1_, orderitems0_.order_price as order_pr3_5_1_ from order_item orderitems0_ where orderitems0_.order_id=? 2022-05-04 01:14:49.040 INFO 21484 --- [nio-8081-exec-6] p6spy : #1651594489040 | took 0ms | statement | connection 8| url jdbc:h2:tcp://localhost/~/jpashopping select orderitems0_.order_id as order_id5_5_0_, orderitems0_.order_item_id as order_it1_5_0_, orderitems0_.order_item_id as order_it1_5_1_, orderitems0_.count as count2_5_1_, orderitems0_.item_id as item_id4_5_1_, orderitems0_.order_id as order_id5_5_1_, orderitems0_.order_price as order_pr3_5_1_ from order_item orderitems0_ where orderitems0_.order_id=? select orderitems0_.order_id as order_id5_5_0_, orderitems0_.order_item_id as order_it1_5_0_, orderitems0_.order_item_id as order_it1_5_1_, orderitems0_.count as count2_5_1_, orderitems0_.item_id as item_id4_5_1_, orderitems0_.order_id as order_id5_5_1_, orderitems0_.order_price as order_pr3_5_1_ from order_item orderitems0_ where orderitems0_.order_id=11; 2022-05-04 01:14:49.044 DEBUG 21484 --- [nio-8081-exec-6] 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_.artist as artist6_3_0_, item0_.etc as etc7_3_0_, item0_.author as author8_3_0_, item0_.isbn as isbn9_3_0_, item0_.actor as actor10_3_0_, item0_.director as directo11_3_0_, item0_.dtype as dtype1_3_0_ from item item0_ where item0_.item_id=? 2022-05-04 01:14:49.046 INFO 21484 --- [nio-8081-exec-6] p6spy : #1651594489046 | took 0ms | statement | connection 8| url jdbc:h2:tcp://localhost/~/jpashopping 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_.artist as artist6_3_0_, item0_.etc as etc7_3_0_, item0_.author as author8_3_0_, item0_.isbn as isbn9_3_0_, item0_.actor as actor10_3_0_, item0_.director as directo11_3_0_, item0_.dtype as dtype1_3_0_ from item item0_ where item0_.item_id=? 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_.artist as artist6_3_0_, item0_.etc as etc7_3_0_, item0_.author as author8_3_0_, item0_.isbn as isbn9_3_0_, item0_.actor as actor10_3_0_, item0_.director as directo11_3_0_, item0_.dtype as dtype1_3_0_ from item item0_ where item0_.item_id=9; 2022-05-04 01:14:49.048 DEBUG 21484 --- [nio-8081-exec-6] 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_.artist as artist6_3_0_, item0_.etc as etc7_3_0_, item0_.author as author8_3_0_, item0_.isbn as isbn9_3_0_, item0_.actor as actor10_3_0_, item0_.director as directo11_3_0_, item0_.dtype as dtype1_3_0_ from item item0_ where item0_.item_id=? 2022-05-04 01:14:49.050 INFO 21484 --- [nio-8081-exec-6] p6spy : #1651594489050 | took 0ms | statement | connection 8| url jdbc:h2:tcp://localhost/~/jpashopping 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_.artist as artist6_3_0_, item0_.etc as etc7_3_0_, item0_.author as author8_3_0_, item0_.isbn as isbn9_3_0_, item0_.actor as actor10_3_0_, item0_.director as directo11_3_0_, item0_.dtype as dtype1_3_0_ from item item0_ where item0_.item_id=? 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_.artist as artist6_3_0_, item0_.etc as etc7_3_0_, item0_.author as author8_3_0_, item0_.isbn as isbn9_3_0_, item0_.actor as actor10_3_0_, item0_.director as directo11_3_0_, item0_.dtype as dtype1_3_0_ from item item0_ where item0_.item_id=10;
또한 api가 아닌 mvc 패턴으로 컬렉션을 find할 때에는 dto가 아닌 엔티티를 그대로 repository에서 찾아서 view 화면에 넘기는데, 이 때는 쿼리 성능 최적화를 어떻게 할 수 있을까요? (타임리프로 order, orderItem의 결과를 보여준다고 가정할 때)
답변 1