[SQL] 쿼리문 변경을 통한 성능 향상 경험

1. 현상

  업무중 만난 SQL 성능 향상 관련 이슈임
  master 테이블과 현재 테이블을 비교하여 없는 값은 삭제하고 있는 값은 추가하는 로직

2. 원인 및 해결

  [ 1. 쿼리문 합치기 + 트랜잭션 ]
  DELETE 로직에서 6번의 DELETE 쿼리문을 변수에 문자열로 저장 후 변경이 필요한 횟수만큼 반복하면서 DB에 요청을 하는 로직을 개선했다. 첫번째로 반복문을 한 번 돌 때마다 쿼리를 보내는 것이 아니라 반복문을 도는 동안 동적할당 변수에 계속 추가한 후 반복문이 끝나고 한 번만 쿼리를 보내도록 수정했다. 약 1만번의 쿼리 요청을 보내야하는 상황에서 해당 이슈가 발생했고 이를 한 번만 보내도록 수정한 것이다. 이에 시간은 약 5분에서 4초로 단축되었다.

이 때, 결과적으로 6만번(1만회 반복 * 6번 쿼리 요청)의 DELETE문을 한 번에 요청해야 함으로 무결성 확보를 위해 앞뒤로 트랜잭션을 적용하였다.

[ 변경 전 ]
" DELETE FROM aaa WHERE code = '%u';\n"
" DELETE FROM bbb WHERE code = '%u';\n"
" DELETE FROM ccc WHERE code = '%u';\n"
" DELETE FROM ddd WHERE code = '%u';\n"
" DELETE FROM eee WHERE code = '%u';\n"
" DELETE FROM fff WHERE code = '%u';\n"
-> DB 접근 약 10,000회

[ 변경 후 ]
"BEGIN TRANSACTION;\n"
" DELETE FROM aaa WHERE code = '%u';\n"
" DELETE FROM bbb WHERE code = '%u';\n"
" DELETE FROM ccc WHERE code = '%u';\n"
" DELETE FROM ddd WHERE code = '%u';\n"
" DELETE FROM eee WHERE code = '%u';\n"
" DELETE FROM fff WHERE code = '%u';\n"
... (약 6만 줄)
"END TRANSACTION;\n"
-> DB 접근 1회

  [ 2. 조건 테이블 한 번에 불러오기 ] 
  값을 추가하는 부분에서는 반복문 안에서 현재 코드에 해당하는 값이 master에 있는 지 확인하고 없으면 추가하는 로직을 개선했다. 기존 로직은 현재 테이블의 모든 값을 돌면서 master DB에 값이 있는 지 확인하고 없으면 삽입하였다. 이를 GROUP BY와 HAVING을 활용하여 master 테이블을 먼저 정리한 후 count가 0인 정책에 대해서만 현재 테이블에 값이 있는 지 확인하고 있으면 추가하는 로직으로 변경했다. 이로 인해 DB 조회는 1회만 수행하게 되었고 반복 횟수도 감소했다.

[ 변경 전 ]
" SELECT COUNT(*) "
" from master "
" where code = '%u'; "
-> DB 접근 약 10,000회

[ 변경 후 ]
" SELECT code, COUNT(*) AS count "
" FROM master "
" GROUP BY code "
" HAVING count = 0 "
-> DB 접근 1회