Batch Update 2편

고민을 너무 많이 했나봅니다. 글이 너무 길어져서 지난 글에 이어 2편으로 다시 돌아왔습니다. 지난 글에서는 메모리와 JPA에 대한 고민을 위주로 글을 썼었더라면 이번 편에서는 데이터베이스와 관련된 고민들을 나열해보았습니다. 

고민1 : 쿼리 횟수에 대한 고민

고민중입니다..

다음으로 고민했던 부분은 쿼리의 횟수였습니다. 분명 지난번 rewriteBatchedStatements 설정을 통해 쿼리가 나가는 횟수를 여러개에서 한번으로 줄였는데 무슨 또 쿼리 횟수를 고민하냐라고 하실수 있습니다. 하지만 그것과는 별개로 다른 문제가 있습니다. 1000개의 쿼리를 한번에 보냄으로써 여러 번 네트워크를 타거나 쿼리를 보내기 위한 과정을 줄일 수 있었지만 1000개의 쿼리를 한번에 보냈다 해서 데이터베이스에서 쿼리를 한번 실행시키는게 아니라는게 저의 의견입니다. 

예를 들자면 아래의 쿼리는 결국 id가 112324과 112325를 찾기 위해 각각 실행될 것이라는 겁니다. 

UPDATE login_token SET expired_dt = '2022-05-05' WHERE id = 112324;UPDATE login_token SET expired_dt = '2022-05-05' WHERE id = 112325;

MySQL에서 primary key에 해당하는 컬럼을 검색한다면 어떤 과정이 일어나는지 이해하고 있는 분이라면 더욱 이해가 빠를텐데 잘 모르시는 분이라면 이곳에서 간단하게 이해를 하고 오면 좋을 것 같습니다. 아무튼 이런 검색과정을 몇 천번씩 해야한다는건 굉장히 비효율적일 것입니다.

계속 반복을 하는거죠..

range 검색을 이용하여 필요한 행들을 모두 가져온 이후라면 더욱 빠를것 같다는 생각을 하였고 CASE-WHEN 구문을 생각해냈죠!!

UPDATE login_token SET expired_dt = CASE
    WHEN id = 112324 THEN '2022-05-05'
    WHEN id = 112325 THEN '2022-05-05'
END WHERE id BETWEEN 112324 AND 112325;

실제로 어떤 것이 더 빠른지 확인하기 위해 아래의 테스트 코드를 돌려보았습니다.

1) CASE-WHEN 적용 전 코드는 지난 글의 JPA 버리기 버전의 코드를 그대로 사용하였습니다. 고로 3분 20분초입니다.

    fun `CASE-WHEN 적용 전`() {
        val rowMapper = RowMapper { rs, _ ->
            LoginToken(
                id = rs.getLong("id"), // 8
                createdAt = LocalDateTime.parse(rs.getString("created_at"), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) // 26
            )
        }
        val chunkSize = 1000
        (1L..1_000_000L).chunked(chunkSize) { subList ->
            val selectLoginToken = "select * from login_token where id between ${subList.first()} and ${subList.last() + 1}"
            val selected = jdbcTemplate.query(selectLoginToken, rowMapper)

            jdbcTemplate.batchUpdate(
                "UPDATE login_token SET expired_dt = ? WHERE id = ?",
                object : BatchPreparedStatementSetter {
                    override fun setValues(ps: PreparedStatement, i: Int) {
                        ps.setString(1, selected[i].createdAt!!.plusDays(7).toLocalDate().toString())
                        ps.setLong(2, selected[i].id)
                    }

                    override fun getBatchSize(): Int {
                        return chunkSize
                    }
                }
            )
        }
    }

2) CASE-WHEN 적용 코드입니다.

    fun `CASE-WHEN 적용 후`() {
        val rowMapper = RowMapper { rs, _ ->
            LoginToken(
                id = rs.getLong("id"), // 8
                createdAt = LocalDateTime.parse(rs.getString("created_at"), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) // 26
            )
        }
        val chunkSize = 1000
        (1L..1_000_000L).chunked(chunkSize) { subList ->
            val selectLoginToken = "select * from login_token where id between ${subList.first()} and ${subList.last() + 1}"
            val selected = jdbcTemplate.query(selectLoginToken, rowMapper).onEach {
                it.expiredDt = it.createdAt!!.plusDays(7).toLocalDate()
            }


            val buffer = StringBuffer()
            buffer.append("update login_token SET expired_dt = CASE\n")
            selected.forEach { buffer.append("when id = ${it.id} then '${it.expiredDt.toString()}'") }
            buffer.append("END WHERE id BETWEEN ${selected.first().id} and ${selected.last().id}")

            jdbcTemplate.update(buffer.toString())
        }
    }

결과는 31초가 소요되었습니다. 어마어마하게 향상된 모습을 보이네요. 이정도로 만족하고 싶지 않습니다. 다음글에서 이 성능을 좀 더 끌어보도록 하겠습니다. 읽어주셔서 감사합니다