쿼리 개선을 통한 성능 최적화
최근에 회사의 레거시 코드에서 여러 번의 조인을 사용하여 성능 저하가 발생하는 문제를 개선하기 위한 작업을 진행했습니다. 이 글에서는 기존 쿼리의 문제점과 개선 방안을 구체적으로 설명하겠습니다.
기존 쿼리의 문제점
기존 쿼리는 BANK_ACCOUNT_RECORD 테이블을 여러 번 조인하여 필요한 데이터를 가져오는 구조였습니다. 이로 인해 SQL 실행 시 성능 저하가 발생하였고, 특히 다음과 같은 문제가 있었습니다:
private fun balanceSubquery(account: QBankRecordEntity, accountConditionType: String) =
JPAExpressions.select(account.balance.`as`("balance"))
.from(account)
.where(account.hospitalId.eq(vBusinessConnectionEntity.hospitalId))
.where(account.transType.eq(accountConditionType))
.orderBy(account.transDate.desc(), account.transTime.desc())
.limit(1)
private fun dateSubquery(account: QBankRecordEntity, accountConditionType: String) =
JPAExpressions.select(account.transDate.`as`("transDate"))
.from(account)
.from(account)
.where(account.hospitalId.eq(vBusinessConnectionEntity.hospitalId))
.where(account.transType.eq(accountConditionType))
.orderBy(account.transDate.desc(), account.transTime.desc())
.limit(1)
private fun createBaseQuery(type: String, year: String, searchParam: SearchParam): JPAQuery<*> {
val unMatchedAccount = QBankRecordEntity("unMatchedAccount")
val matchedAccount = QBankRecordEntity("matchedAccount")
val proofAccount = QBankRecordEntity("proofAccount")
val excludedAccount = QBankRecordEntity("excludedAccount")
val balanceAccount = QBankRecordEntity("balanceAccount")
val accountConditionType = when (type.uppercase()) {
"PURCHASE" -> "OUT"
"SALES" -> "IN"
else -> throw IllegalArgumentException("type must be PURCHASE or SALES")
}
return factory
.from(vBusinessConnectionEntity)
.leftJoin(vMember).on(
vBusinessConnectionEntity.hospitalId.eq(vMember.companyId),
vMember.isOwner.eq(1)
)
.leftJoin(unMatchedAccount).on(
vBusinessConnectionEntity.hospitalId.eq(unMatchedAccount.hospitalId),
unMatchedAccount.excluded.eq(false),
unMatchedAccount.proofMatch.isNull,
unMatchedAccount.matchGroup.isNull,
unMatchedAccount.transType.eq(accountConditionType),
unMatchedAccount.transDate.startsWith(year)
)
.leftJoin(matchedAccount).on(
vBusinessConnectionEntity.hospitalId.eq(matchedAccount.hospitalId),
matchedAccount.excluded.eq(false),
matchedAccount.proofMatch.isNull,
matchedAccount.matchGroup.isNotNull,
matchedAccount.transType.eq(accountConditionType),
matchedAccount.transDate.startsWith(year)
)
.leftJoin(proofAccount).on(
vBusinessConnectionEntity.hospitalId.eq(proofAccount.hospitalId),
proofAccount.excluded.eq(false),
proofAccount.proofMatch.isNotNull,
proofAccount.matchGroup.isNull,
proofAccount.transType.eq(accountConditionType),
proofAccount.transDate.startsWith(year)
)
.leftJoin(excludedAccount).on(
vBusinessConnectionEntity.hospitalId.eq(excludedAccount.hospitalId),
excludedAccount.excluded.eq(true),
excludedAccount.proofMatch.isNull,
excludedAccount.matchGroup.isNull,
excludedAccount.transType.eq(accountConditionType),
excludedAccount.transDate.startsWith(year)
)
.leftJoin(balanceAccount)
.where(searchParam.hospitalName?.let { vBusinessConnectionEntity.hospitalName.contains(it) })
}
override fun getAccountList(
pageable: Pageable,
type: String,
year: String,
searchParam: SearchParam
): Page<AccountRes> {
val balanceAccount = QBankRecordEntity("balanceAccount")
val unMatchedAccount = QBankRecordEntity("unMatchedAccount")
val matchedAccount = QBankRecordEntity("matchedAccount")
val proofAccount = QBankRecordEntity("proofAccount")
val excludedAccount = QBankRecordEntity("excludedAccount")
val accountConditionType = when (type.uppercase()) {
"PURCHASE" -> "OUT"
"SALES" -> "IN"
else -> throw IllegalArgumentException("type must be PURCHASE or SALES")
}
val baseQuery = createBaseQuery(type, year, searchParam).select(
vBusinessConnectionEntity.hospitalId.`as`("hospitalId"),
vBusinessConnectionEntity.bizEid.`as`("hospitalEid"),
vBusinessConnectionEntity.hospitalName.`as`("hospitalName"),
vMember.accountName.`as`("directorName"),
balanceSubquery(balanceAccount, accountConditionType),
dateSubquery(balanceAccount, accountConditionType),
unMatchedAccount.inAmount.sum().add(unMatchedAccount.outAmount.sum()).`as`("unmatchedAmount"),
unMatchedAccount.count().`as`("unmatchedCount"),
matchedAccount.inAmount.sum().add(matchedAccount.outAmount.sum()).`as`("matchedAmount"),
matchedAccount.count().`as`("matchedCount"),
proofAccount.inAmount.sum().add(proofAccount.outAmount.sum()).`as`("proofAmount"),
proofAccount.count().`as`("proofCount"),
excludedAccount.inAmount.sum().add(excludedAccount.outAmount.sum()).`as`("excludedAmount"),
excludedAccount.count().`as`("excludedCount")
).groupBy(vBusinessConnectionEntity.hospitalId)
val list = applyPagination(pageable, baseQuery, vBusinessConnectionEntity, unMatchedAccount).fetch().toDto()
val count =
createBaseQuery(type, year, searchParam).select(vBusinessConnectionEntity.hospitalId.countDistinct())
.fetchFirst() ?: 0L
return fetchPage(list, pageable) { count }
}실제 실행되는 sql
select
v1_0.HOSPITAL_ID,
v1_0.BIZ_EID,
v1_0.HOSPITAL_NAME,
v2_0.ACCOUNT_NAME,
(SELECT b5_0.BALANCE
FROM BANK_ACCOUNT_RECORD b5_0
WHERE b5_0.HOSPITAL_ID = v1_0.HOSPITAL_ID
AND b5_0.TRANS_TYPE = 'IN'
ORDER BY b5_0.TRANS_DATE DESC, b5_0.TRANS_TIME DESC
LIMIT 1),
(SELECT b5_0.TRANS_DATE
FROM BANK_ACCOUNT_RECORD b5_0
WHERE b5_0.HOSPITAL_ID = v1_0.HOSPITAL_ID
AND b5_0.TRANS_TYPE = 'IN'
ORDER BY b5_0.TRANS_DATE DESC, b5_0.TRANS_TIME DESC
LIMIT 1),
(sum(b1_0.IN_AMOUNT)+sum(b1_0.OUT_AMOUNT)),
count(b1_0.id),
(sum(b2_0.IN_AMOUNT)+sum(b2_0.OUT_AMOUNT)),
count(b2_0.id),
(sum(b3_0.IN_AMOUNT)+sum(b3_0.OUT_AMOUNT)),
count(b3_0.id),
(sum(b4_0.IN_AMOUNT)+sum(b4_0.OUT_AMOUNT)),
count(b4_0.id)
from
V_BUSINESS_CONNECTION v1_0
left join
V_MEMBER v2_0
on
v1_0.HOSPITAL_ID=v2_0.COMPANY_ID
and v2_0.IS_OWNER='1'
left join
BANK_ACCOUNT_RECORD b1_0
on
v1_0.HOSPITAL_ID=b1_0.HOSPITAL_ID
and b1_0.EXCLUDED='0'
and b1_0.PROOF_RECORD_ID is null
and b1_0.MATCH_GROUP_ID is null
and b1_0.TRANS_TYPE='IN'
and b1_0.TRANS_DATE like '2024%' escape '!'
left join
BANK_ACCOUNT_RECORD b2_0
on
v1_0.HOSPITAL_ID=b2_0.HOSPITAL_ID
and b2_0.EXCLUDED='0'
and b2_0.PROOF_RECORD_ID is null
and b2_0.MATCH_GROUP_ID is not null
and b2_0.TRANS_TYPE='IN'
and b2_0.TRANS_DATE like '2024%' escape '!'
left join
BANK_ACCOUNT_RECORD b3_0
on
v1_0.HOSPITAL_ID=b3_0.HOSPITAL_ID
and b3_0.EXCLUDED='0'
and b3_0.PROOF_RECORD_ID is not null
and b3_0.MATCH_GROUP_ID is null
and b3_0.TRANS_TYPE='IN'
and b3_0.TRANS_DATE like '2024%' escape '!'
left join
BANK_ACCOUNT_RECORD b4_0
on
v1_0.HOSPITAL_ID=b4_0.HOSPITAL_ID
and b4_0.EXCLUDED='1'
and b4_0.PROOF_RECORD_ID is null
and b4_0.MATCH_GROUP_ID is null
and b4_0.TRANS_TYPE='IN'
and b4_0.TRANS_DATE like '2024%' escape '!'
group by
v1_0.HOSPITAL_ID
order by
v1_0.HOSPITAL_NAME asc;개선 방안
새로운 쿼리는 중복 조인을 제거하고, CASE 문을 효율적으로 사용하여 성능을 개선하였습니다. 아래는 개선된 코드와 SQL 쿼리입니다.
개선된 Kotlin 코드
override fun getAccountList(
pageable: Pageable,
type: String,
year: String,
searchParam: SearchParam
): Page<AccountRes> {
val account = QBankRecordEntity("account")
val accountConditionType = when (type.uppercase()) {
"PURCHASE" -> "OUT"
"SALES" -> "IN"
else -> throw IllegalArgumentException("type must be PURCHASE or SALES")
}
val baseQuery = createBaseQuery(type, year, searchParam).select(
vBusinessConnectionEntity.hospitalId.`as`("hospitalId"),
vBusinessConnectionEntity.bizEid.`as`("hospitalEid"),
vBusinessConnectionEntity.hospitalName.`as`("hospitalName"),
vMember.accountName.`as`("directorName"),
// BALANCE와 TRANS_DATE 서브쿼리 사용
JPAExpressions.select(account.balance.`as`("balance"))
.from(account)
.where(account.hospitalId.eq(vBusinessConnectionEntity.hospitalId))
.where(account.transType.eq(accountConditionType))
.orderBy(account.transDate.desc(), account.transTime.desc())
.limit(1),
JPAExpressions.select(account.transDate.`as`("transDate"))
.from(account)
.where(account.hospitalId.eq(vBusinessConnectionEntity.hospitalId))
.where(account.transType.eq(accountConditionType))
.orderBy(account.transDate.desc(), account.transTime.desc())
.limit(1),
// CaseBuilder 사용
CaseBuilder()
.`when`(
account.excluded.eq(false)
.and(account.proofMatch.isNull)
.and(account.matchGroup.isNull))
.then(account.inAmount.add(account.outAmount))
.otherwise(0).sum()
.`as`("unmatchedAmount"),
CaseBuilder()
.`when`(
account.excluded.eq(false)
.and(account.proofMatch.isNull)
.and(account.matchGroup.isNull)
)
.then(1) // 조건을 만족하면 1을 반환
.otherwise(0) // 조건을 만족하지 않으면 0을 반환
.sum() // 1의 합계를 구함
.`as`("unmatchedCount"),
CaseBuilder()
.`when`(account.excluded.eq(false)
.and(account.proofMatch.isNull)
.and(account.matchGroup.isNotNull))
.then(account.inAmount.add(account.outAmount))
.otherwise(0).sum()
.`as`("matchedAmount"),
CaseBuilder()
.`when`(account.excluded.eq(false)
.and(account.proofMatch.isNull)
.and(account.matchGroup.isNotNull))
.then(1)
.otherwise(0)
.sum()
.`as`("matchedCount"),
CaseBuilder()
.`when`(account.excluded.eq(false)
.and(account.proofMatch.isNotNull)
.and(account.matchGroup.isNull))
.then(account.inAmount.add(account.outAmount))
.otherwise(0).sum()
.`as`("proofAmount"),
CaseBuilder()
.`when`(account.excluded.eq(false)
.and(account.proofMatch.isNotNull)
.and(account.matchGroup.isNull))
.then(1)
.otherwise(0)
.sum()
.`as`("proofCount"),
CaseBuilder()
.`when`(account.excluded.eq(true)
.and(account.proofMatch.isNull)
.and(account.matchGroup.isNull))
.then(account.inAmount.add(account.outAmount))
.otherwise(0).sum()
.`as`("excludedAmount"),
CaseBuilder()
.`when`(account.excluded.eq(true)
.and(account.proofMatch.isNull)
.and(account.matchGroup.isNull))
.then(1)
.otherwise(0)
.sum()
.`as`("excludedCount"),
).groupBy(vBusinessConnectionEntity.hospitalId)
val list = applyPagination(pageable, baseQuery, vBusinessConnectionEntity, account).fetch().toDto()
val count = createBaseQuery(type, year, searchParam).select(vBusinessConnectionEntity.hospitalId.countDistinct())
.fetchFirst() ?: 0L
return fetchPage(list, pageable) { count }
}실제 실행되는 sql
SELECT
V1_0.HOSPITAL_ID,
V1_0.BIZ_EID,
V1_0.HOSPITAL_NAME,
V2_0.ACCOUNT_NAME,
(SELECT
B2_0.BALANCE
FROM
BANK_ACCOUNT_RECORD B2_0
WHERE
B2_0.HOSPITAL_ID = V1_0.HOSPITAL_ID
AND B2_0.TRANS_TYPE = CAST(? AS VARCHAR(255))
GROUP BY
B2_0.HOSPITAL_ID
ORDER BY
B2_0.TRANS_DATE DESC,
B2_0.TRANS_TIME DESC),
(SELECT
B3_0.TRANS_DATE
FROM
BANK_ACCOUNT_RECORD B3_0
WHERE
B3_0.HOSPITAL_ID = V1_0.HOSPITAL_ID
AND B3_0.TRANS_TYPE = CAST(? AS VARCHAR(255))
GROUP BY
B3_0.HOSPITAL_ID
ORDER BY
B3_0.TRANS_DATE DESC,
B3_0.TRANS_TIME DESC),
SUM(CASE
WHEN (B1_0.EXCLUDED = ?
AND B1_0.PROOF_RECORD_ID IS NULL
AND B1_0.MATCH_GROUP_ID IS NULL) THEN (B1_0.IN_AMOUNT + B1_0.OUT_AMOUNT)
ELSE 0
END),
SUM(CASE
WHEN (B1_0.EXCLUDED = ?
AND B1_0.PROOF_RECORD_ID IS NULL
AND B1_0.MATCH_GROUP_ID IS NULL) THEN ?
ELSE 0
END),
SUM(CASE
WHEN (B1_0.EXCLUDED = ?
AND B1_0.PROOF_RECORD_ID IS NULL
AND B1_0.MATCH_GROUP_ID IS NOT NULL) THEN (B1_0.IN_AMOUNT + B1_0.OUT_AMOUNT)
ELSE 0
END),
SUM(CASE
WHEN (B1_0.EXCLUDED = ?
AND B1_0.PROOF_RECORD_ID IS NULL
AND B1_0.MATCH_GROUP_ID IS NOT NULL) THEN ?
ELSE 0
END),
SUM(CASE
WHEN (B1_0.EXCLUDED = ?
AND B1_0.PROOF_RECORD_ID IS NOT NULL
AND B1_0.MATCH_GROUP_ID IS NULL) THEN (B1_0.IN_AMOUNT + B1_0.OUT_AMOUNT)
ELSE 0
END),
SUM(CASE
WHEN (B1_0.EXCLUDED = ?
AND B1_0.PROOF_RECORD_ID IS NOT NULL
AND B1_0.MATCH_GROUP_ID IS NULL) THEN ?
ELSE 0
END),
SUM(CASE
WHEN (B1_0.EXCLUDED = ?
AND B1_0.PROOF_RECORD_ID IS NULL
AND B1_0.MATCH_GROUP_ID IS NULL) THEN (B1_0.IN_AMOUNT + B1_0.OUT_AMOUNT)
ELSE 0
END),
SUM(CASE
WHEN (B1_0.EXCLUDED = ?
AND B1_0.PROOF_RECORD_ID IS NULL
AND B1_0.MATCH_GROUP_ID IS NULL) THEN ?
ELSE 0
END)
FROM
V_BUSINESS_CONNECTION V1_0
LEFT JOIN
V_MEMBER V2_0
ON V1_0.HOSPITAL_ID = V2_0.COMPANY_ID
AND V2_0.IS_OWNER = ?
LEFT JOIN
BANK_ACCOUNT_RECORD B1_0
ON V1_0.HOSPITAL_ID = B1_0.HOSPITAL_ID
AND B1_0.TRANS_TYPE = ?
AND B1_0.TRANS_DATE LIKE ? ESCAPE '!'
GROUP BY
V1_0.HOSPITAL_ID
ORDER BY
V1_0.HOSPITAL_NAME ASC위의 개선된 쿼리는 중복된 조인을 제거하여 사용을 성능을 크게 향상시켰습니다. 또한 CASE 문을 통해 조건부 집계를 보다 효율적으로 수행할 수 있게 되었습니다. 이러한 개선을 통해 데이터베이스의 응답 속도가 향상되었으며, 시스템의 전체적인 성능을 개선할 수 있었습니다.
개발