QueryDSL로 성능 향상시키기 -1 scheduler 의미가 없었다.

[allnight5]

항상 DB에 가서 모든 컬럼을 들고와서 하는 작업이었으나 쿼리 DSL로 필요한 컬럼만 가져와서 하는 작업을 수행할 예정

예전 소스코드

public void ratingReview() {
        List<GymBoard> gymBoards = gymBoardRepository.findAll();
        if(gymBoards.isEmpty()){
            return;
        }
        List<GymReview> gymReview = gymReviewRepository.findAll();
        if(gymReview.isEmpty()){
            return;
        }
        HashMap<Long, Long> rating = new HashMap<>();
        HashMap<Long, Long> rating_count = new HashMap<>();
        //맵 2개 쓸바에. 다른게 좋으려나?
        for(GymReview gymReview1 : gymReview){
            Long key = gymReview1.getGymId();
            Long value = gymReview1.getRating();
            rating.put(key ,rating.getOrDefault(key,0L)+value);
            rating_count.put(key ,rating_count.getOrDefault(key,0L)+1);
        }
        for (GymBoard gymBoard : gymBoards) {
            if(rating.get(gymBoard.getId()) == null){
                continue;
            }
            Long total = rating.get(gymBoard.getId());
            Long div = rating_count.get(gymBoard.getId());
            gymBoard.ratingUpdate(total/div);
        }
    }

일반 쿼리 DSL로 맵을 사용하지 않고 카운트해서 해결하는 코드

    public void dslRatingReview() {

        QGymBoard qGymBoard = QGymBoard.gymBoard;
        QGymReview qGymReview = QGymReview.gymReview;

        List<Tuple> result = new JPAQueryFactory(entityManager)
                .from(qGymBoard)
                .leftJoin(qGymReview)
                .on(qGymBoard.id.eq(qGymReview.gymId))
                .groupBy(qGymBoard.id)
                .select(qGymBoard.id, qGymReview.rating.count(),
                        qGymReview.rating.sum())
                .fetch();

        for (Tuple tuple : result) {
            Long gymId = tuple.get(qGymBoard.id);
            Long ratingCount = tuple.get(qGymReview.rating.count());
            Long ratingSum = tuple.get(qGymReview.rating.sum());

            if (ratingCount == null || ratingSum == null || ratingCount == 0) {
                continue;
            }

            long ratingAvg = (long) (ratingSum / (double) ratingCount);
            assert gymId != null;
            Optional<GymBoard> optionalGymBoard = gymBoardRepository.findById(gymId);
            optionalGymBoard.ifPresent(gymBoard -> gymBoard.ratingUpdate(ratingAvg));
        }
    }

현재 쿼리를 날리면 이런식으로 모든 컬럼을 조회해서 가져옴

Hibernate:
select
g1_0.gym_id,
count(g2_0.rating),
sum(g2_0.rating)
from
gym_board g1_0
left join
gym_review g2_0
on g1_0.gym_id=g2_0.gym_id
group by
g1_0.gym_id
Hibernate:
select
g1_0.gym_id,
g1_0.amenities,
g1_0.amenities_detail,
g1_0.content,
g1_0.convenient_facilities,
g1_0.created_date,
g1_0.created_date_string,
g1_0.discount,
g1_0.gym_name,
g1_0.image_url,
g1_0.modified_date,
g1_0.open_time,
g1_0.owner_number,
g1_0.phone_number,
g1_0.price,
g1_0.rating,
g1_0.region,
g1_0.title,
g1_0.username
from
gym_board g1_0
where
g1_0.gym_id=?
Hibernate:
select
g1_0.gym_id,
g1_0.amenities,
g1_0.amenities_detail,
g1_0.content,
g1_0.convenient_facilities,
g1_0.created_date,
g1_0.created_date_string,
g1_0.discount,
g1_0.gym_name,
g1_0.image_url,
g1_0.modified_date,
g1_0.open_time,
g1_0.owner_number,
g1_0.phone_number,
g1_0.price,
g1_0.rating,
g1_0.region,
g1_0.title,
g1_0.username
from
gym_board g1_0
where
g1_0.gym_id=?

1차 수정코드

    public void dslRatingReview() {

        QGymBoard qGymBoard = QGymBoard.gymBoard;
        QGymReview qGymReview = QGymReview.gymReview;

        List<Tuple> result = new JPAQueryFactory(entityManager)
                .select(qGymBoard.id, qGymReview.rating.count(), qGymReview.rating.sum())
                .from(qGymBoard)
                .leftJoin(qGymReview).on(qGymBoard.id.eq(qGymReview.gymId))
                .groupBy(qGymBoard.id)
                .fetch();


        for (Tuple tuple : result) {
            Long gymId = tuple.get(qGymBoard.id);
            Long ratingCount = tuple.get(qGymReview.rating.count());
            Long ratingSum = tuple.get(qGymReview.rating.sum());

            if (ratingCount == null || ratingSum == null || ratingCount == 0) {
                continue;
            }

            long ratingAvg = (long) (ratingSum / (double) ratingCount);
            assert gymId != null;
            Optional<GymBoard> optionalGymBoard = gymBoardRepository.findById(gymId);
            optionalGymBoard.ifPresent(gymBoard -> gymBoard.ratingUpdate(ratingAvg));
        }
    }

[wonizizi89]

오 좋습니다 >.<ㅎㅎ 성능개선 !