[BE] 불필요하게 DB 를 조회하는 로직을 개선한다 part2

[Kimprodp]

🔍 설명

• 서비스 주요 기능 로직 수행에 대해 많은 쿼리가 실행되고 있습니다.

리뷰 쓰기

Hibernate: select rg1_0.id,rg1_0.group_access_code,rg1_0.project_name,rg1_0.review_request_code,rg1_0.reviewee,rg1_0.template_id from review_group rg1_0 where rg1_0.review_request_code=?
Hibernate: select t1_0.id,si1_0.template_id,si1_0.id,si1_0.section_id from template t1_0 left join template_section si1_0 on t1_0.id=si1_0.template_id where t1_0.id=?
Hibernate: select s1_0.id,s1_0.header,s1_0.on_selected_option_id,s1_0.position,s1_0.section_name,s1_0.visible_type,qi1_0.section_id,qi1_0.id,qi1_0.question_id from section s1_0 left join section_question qi1_0 on s1_0.id=qi1_0.section_id where s1_0.id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select oi1_0.id,oi1_0.content,oi1_0.option_group_id,oi1_0.option_type,oi1_0.position from option_item oi1_0 where oi1_0.option_group_id=?
Hibernate: select s1_0.id,s1_0.header,s1_0.on_selected_option_id,s1_0.position,s1_0.section_name,s1_0.visible_type,qi1_0.section_id,qi1_0.id,qi1_0.question_id from section s1_0 left join section_question qi1_0 on s1_0.id=qi1_0.section_id where s1_0.id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select oi1_0.id,oi1_0.content,oi1_0.option_group_id,oi1_0.option_type,oi1_0.position from option_item oi1_0 where oi1_0.option_group_id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select s1_0.id,s1_0.header,s1_0.on_selected_option_id,s1_0.position,s1_0.section_name,s1_0.visible_type,qi1_0.section_id,qi1_0.id,qi1_0.question_id from section s1_0 left join section_question qi1_0 on s1_0.id=qi1_0.section_id where s1_0.id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select oi1_0.id,oi1_0.content,oi1_0.option_group_id,oi1_0.option_type,oi1_0.position from option_item oi1_0 where oi1_0.option_group_id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select s1_0.id,s1_0.header,s1_0.on_selected_option_id,s1_0.position,s1_0.section_name,s1_0.visible_type,qi1_0.section_id,qi1_0.id,qi1_0.question_id from section s1_0 left join section_question qi1_0 on s1_0.id=qi1_0.section_id where s1_0.id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select oi1_0.id,oi1_0.content,oi1_0.option_group_id,oi1_0.option_type,oi1_0.position from option_item oi1_0 where oi1_0.option_group_id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select s1_0.id,s1_0.header,s1_0.on_selected_option_id,s1_0.position,s1_0.section_name,s1_0.visible_type,qi1_0.section_id,qi1_0.id,qi1_0.question_id from section s1_0 left join section_question qi1_0 on s1_0.id=qi1_0.section_id where s1_0.id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select oi1_0.id,oi1_0.content,oi1_0.option_group_id,oi1_0.option_type,oi1_0.position from option_item oi1_0 where oi1_0.option_group_id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select s1_0.id,s1_0.header,s1_0.on_selected_option_id,s1_0.position,s1_0.section_name,s1_0.visible_type,qi1_0.section_id,qi1_0.id,qi1_0.question_id from section s1_0 left join section_question qi1_0 on s1_0.id=qi1_0.section_id where s1_0.id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select oi1_0.id,oi1_0.content,oi1_0.option_group_id,oi1_0.option_type,oi1_0.position from option_item oi1_0 where oi1_0.option_group_id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select s1_0.id,s1_0.header,s1_0.on_selected_option_id,s1_0.position,s1_0.section_name,s1_0.visible_type,qi1_0.section_id,qi1_0.id,qi1_0.question_id from section s1_0 left join section_question qi1_0 on s1_0.id=qi1_0.section_id where s1_0.id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select s1_0.id,s1_0.header,s1_0.on_selected_option_id,s1_0.position,s1_0.section_name,s1_0.visible_type,qi1_0.section_id,qi1_0.id,qi1_0.question_id from section s1_0 left join section_question qi1_0 on s1_0.id=qi1_0.section_id where s1_0.id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id=?

리뷰 저장

Hibernate: select rg1_0.id,rg1_0.group_access_code,rg1_0.project_name,rg1_0.review_request_code,rg1_0.reviewee,rg1_0.template_id from review_group rg1_0 where rg1_0.review_request_code=?
Hibernate: select t1_0.id,si1_0.template_id,si1_0.id,si1_0.section_id from template t1_0 left join template_section si1_0 on t1_0.id=si1_0.template_id where t1_0.id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id in (?,?,?,?,?,?,?)
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select oi1_0.id,oi1_0.content,oi1_0.option_group_id,oi1_0.option_type,oi1_0.position from option_item oi1_0 where oi1_0.option_group_id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select oi1_0.id,oi1_0.content,oi1_0.option_group_id,oi1_0.option_type,oi1_0.position from option_item oi1_0 where oi1_0.option_group_id=?
Hibernate: select og1_0.id,og1_0.max_selection_count,og1_0.min_selection_count,og1_0.question_id from option_group og1_0 where og1_0.question_id=?
Hibernate: select oi1_0.id,oi1_0.content,oi1_0.option_group_id,oi1_0.option_type,oi1_0.position from option_item oi1_0 where oi1_0.option_group_id=?
Hibernate: SELECT q.id FROM question q
JOIN section_question sq
ON q.id = sq.question_id
JOIN template_section ts
ON sq.section_id = ts.section_id
WHERE ts.template_id = ?

Hibernate: SELECT s.* FROM section s
JOIN template_section ts
ON s.id = ts.section_id
WHERE ts.template_id = ?
ORDER BY s.position ASC

Hibernate: select qi1_0.section_id,qi1_0.id,qi1_0.question_id from section_question qi1_0 where qi1_0.section_id=?
Hibernate: select qi1_0.section_id,qi1_0.id,qi1_0.question_id from section_question qi1_0 where qi1_0.section_id=?
Hibernate: select qi1_0.section_id,qi1_0.id,qi1_0.question_id from section_question qi1_0 where qi1_0.section_id=?
Hibernate: select qi1_0.section_id,qi1_0.id,qi1_0.question_id from section_question qi1_0 where qi1_0.section_id=?
Hibernate: select qi1_0.section_id,qi1_0.id,qi1_0.question_id from section_question qi1_0 where qi1_0.section_id=?
Hibernate: select qi1_0.section_id,qi1_0.id,qi1_0.question_id from section_question qi1_0 where qi1_0.section_id=?
Hibernate: select qi1_0.section_id,qi1_0.id,qi1_0.question_id from section_question qi1_0 where qi1_0.section_id=?
Hibernate: select qi1_0.section_id,qi1_0.id,qi1_0.question_id from section_question qi1_0 where qi1_0.section_id=?
Hibernate: select q1_0.id,q1_0.content,q1_0.guideline,q1_0.position,q1_0.question_type,q1_0.required from question q1_0 where q1_0.id in (?,?,?,?,?,?,?)
Hibernate: insert into review (created_at,review_group_id,template_id,id) values (?,?,?,default)
Hibernate: insert into checkbox_answer (review_id,question_id,id) values (?,?,default)
Hibernate: insert into checkbox_answer_selected_option (checkbox_answer_id,selected_option_id,id) values (?,?,default)
Hibernate: insert into checkbox_answer_selected_option (checkbox_answer_id,selected_option_id,id) values (?,?,default)
Hibernate: insert into checkbox_answer (review_id,question_id,id) values (?,?,default)
Hibernate: insert into checkbox_answer_selected_option (checkbox_answer_id,selected_option_id,id) values (?,?,default)
Hibernate: insert into checkbox_answer (review_id,question_id,id) values (?,?,default)
Hibernate: insert into checkbox_answer_selected_option (checkbox_answer_id,selected_option_id,id) values (?,?,default)
Hibernate: insert into text_answer (review_id,content,question_id,id) values (?,?,?,default)
Hibernate: insert into text_answer (review_id,content,question_id,id) values (?,?,?,default)
Hibernate: insert into text_answer (review_id,content,question_id,id) values (?,?,?,default)

• DB 접근 횟수를 줄이는 방안을 적용해야 합니다.

• 쿼리 확인 결과 템플릿, 섹션, 질문 등과 같은 거의 정적인 데이터의 조회 쿼리가 많이 실행되고 있습니다.

• 로직 개선으로 쿼리 횟수를 줄이는 방안을 모색할 수 있으나, 본질적으로 템플릿, 섹션, 질문 등과 같이 어플리케이션 실행 시 초기화되는 데이터는 사용자 추가가 불가능 합니다.

• 따라서, 어플리케이션 메모리에 이러한 데이터들을 불러와 저장하고 필요에 따라 메모리에서 조회하는 방식으로 개선하려 합니다.

• 연관성을 끊어서 id를 기준으로 객체를 찾아오는 쿼리가 많은데 이 방식을 통해서 이런 단점을 해소할 수 있을 것 같아요.

• 추가로, 즉시 로딩 사용으로 인해 불필요한 쿼리(join, n+1)가 있어서 확인 후 Lazy 변경이 필요합니다.

🔥 할 일

1. DataInitializer를 통해 저장되는 데이터를 메모리에 저장
2. 필요에 따라 DB가 아닌 메모리에서 호출하여 사용
3. 데이터 양이 많지 않기 때문에 메모리에 올려두고 사용해도 무방
4. 동시성을 고려하여 구현
5. 즉시 로딩으로 인하여 발생되는 불필요한 쿼리 제거

⏰ 예상 시간

• 3d

🐴 할 말

• 메모리 적재 방식이 적합한지 확인해주세요.
  해 해