엑셀업로드를 통한 대량등록 성능 개선의 건

 최근에 담당한 건에 대해 정리해보려고 한다.

---

 기존 프로젝트에는 엑셀업로드를 통해 대량등록하는 기능이 있었는데, 엑셀 한 줄 한 줄씩 처리하다보니 성능이 느렸다. 기존에 요구된 처리량은 많아야 몇백건정도라 그동안 문제가 되지 않았지만 이번에 몇만건을 처리해야 하는 경우가 생기면서 성능을 개선하게 되었다.

---

요구사항

 - 구분코드와 문장으로 유일한 데이터인지 판단한다. 같은 구분코드와 문장의 데이터는 존재할 수 없다.
 - 데이터를 구성하는 요소는 구분코드, 문장, 대체문장인데, 문장과 대체문장은 같을 수 없다.
 - 엑셀내에 중복된 데이터가 존재한다면 가장 최신(가장 아래)의 데이터로 등록되어야 한다.
 - DB에도 존재하는 중복된 데이터는 새로 등록하는 데이터로 업데이트한다.
 - 구분코드 2, 3의 경우, 문장에 소문자만 입력 가능하다. -> 엑셀내에 같은 단어지만 대문자, 소문자로 다를 경우도 같은 데이터로 판단해야 한다. -> 만약 이런 경우, 이 데이터 이전 데이터까지만 처리되도록 한다.
 - 구분코드 1의 경우, 첫 글자가 특수문자일 수 없다.
 - 엑셀에 빈 row가 존재할 경우, 그 row 이전까지만 처리하고 오류를 반환한다.
 - 파라미터 체크를 진행해서 유효하지 않을 경우, 전체 데이터 처리를 진행하지 않고 오류를 반환한다.
 - 빈 엑셀파일을 등록할 경우, 기존에는 등록 완료였지만 등록할 사전이 없다고 나타낸다.
 - 단순등록만 이루어질 수도 있고, API 요청까지 이루어질 수도 있다.

---

조건

 - DBMS에 쿼리 길이 제한이 걸릴 수 있기 때문에 500개의 데이터를 한번에 묶어서 처리한다. (본 건에서 사용했던 mysql의 경우 max_allowed_packet이라는 설정값에 따라 쿼리 길이가 다르다고 한다. default는 1Mbyte. 🔗 https://ckbcorp.tistory.com/689)
 - 연동하는 API는 각 구분코드별로 데이터를 묶어서 보내야 한다. -> 엑셀상에서는 구분코드가 섞여서 들어올 수 있는데, 이 데이터들을 다 분류해서 API를 통해 보내야 한다는 의미.

---

설계

 중간에 정말 우여곡절이 많았지만 결과적으로 짠 구조는 다음과 같았다.

 1. 엑셀파일의 전체 데이터를 읽어와서 빈 파일인지 아닌지 체크(빈 파일이면 등록할 사전이 없다고 반환)
 2. 파라미터 체크
  - 구분코드 1의 경우,  첫 글자가 특수문자라면 오류 반환.
  - 문장과 대체문장이 같아도 오류 반환.
  - 구분코드 2, 3의 경우 문장을 소문자로 치환(소문자를 치환한 결과 문장과 대체문장이 같아지면 이를 체크하는 boolean 변수의 값을 변경.)
  - 중간에 빈 줄이 있다면 빈 줄을 체크하는 boolean 변수의 값을 변경하고 데이터는 그만 담는다. -> 빈 row 이전까지만 처리해야 하는 조건!
  - 오류를 반환하는 경우를 제외하고 데이터들을 리스트에 담는다.
 3. (파라미터 체크에 통과했을 때 수행)엑셀파일상의 중복 제거
  - 2에서 결과적으로 만들어진 데이터 리스트를 거꾸로 탐색하며(엑셀파일 제일 아랫줄에서 위로 올라가며 탐색하는 셈) 미리 만들어놓은 구분코드별 리스트에 문장을 넣고, 같은 문장의 데이터가 나타난다면 그 데이터는 무시하도록 한다.
  - 리스트를 끝까지 탐색하면 사용자가 엑셀에 입력한 순서를 지켜야 하므로 리스트를 다시 뒤집는다.(Collections.reverse())
 4. 데이터 삽입 및 업데이트
  - 3의 결과 중복이 사라진 최종 데이터 리스트를 500개씩 잘라 500개의 리스트로 DB에 중복조회를 하고 insert할 리스트와 update할 리스트로 구분한다.
  - insert와 update를 수행
 5. API 요청(API 요청을 선택한 경우)
  - 연동해야 하는 다른 시스템의 API는 규격상 각 구분코드별로 데이터를 묶어서 보내야 했다. 그래서 구분코드를 key, 구분코드별 데이터리스트를 value로 하는 HashMap을 만들어 데이터를 분류했다.
  - 연동해야 하는 시스템이 1개가 아니었기 때문에 우선 for문으로 시스템별로 요청하도록 하고, 내부에서는 구분코드별로 데이터가 담긴 HashMap을 for문을 통해 json으로 만들어 API 요청했다.
  - 여기서 굉장히 헷갈리는 부분이 있었는데, 시스템별로 각 구분코드별로 데이터를 나눠서 보내는데, 이 데이터가 또 500건씩 끊어서 보내는 데이터였던 것. 만약 데이터를 보내다가 실패하는 경우에는 전체 시스템에 데이터가 전달된 것이 아니기 때문에 500건의 모든 데이터를 실패로 내역을 쌓아야 했다.
 6. API결과에 따라 상태코드를 설정하여 요청내역을 insert. 그리고 4에서 insert 혹은 update한 데이터의 상태를 API 요청 완료로 변경.
 7. 데이터에 빈 row가 존재했는지, 구분코드 2, 3의 경우 소문자 치환결과 문장과 대체문장이 같았는지 체크하고 결과값 설정
  - 500개씩의 데이터로 4~6의 과정이 성공한다면 마지막으로 빈 row가 존재했는지, 구분코드 2, 3의 경우 소문자 치환결과 문장과 대체문장이 같았는지 체크하고 결과값은 오류로 설정했다.

---

결과

 본 건을 진행하며 가장 중요한 건 기존 기능을 그대로 구현하되, 성능이 향상되어야 한다는 것이었다.
 결과는 데이터 20000건 기준으로 아래와 같았다.

	개선 전	개선 후
단순등록	약 29분 소요	약 5분 소요
단순등록 & API 요청	약 1시간 27분 소요	약 13분 소요

---

소감

 이 글을 쓰려고 다시 기억을 되짚는데 정말 끝낸 게 용하다. 기존 코드가 그렇게 잘 정리된 상태가 아니었기 때문에 거의 새로 만든 수준인데, 어찌저찌 만들긴 했지만 더 효율적으로 짤 수 있었을텐데 하는 아쉬운 마음이 든다.
 그리고 이번 기회에 왜 코드를 깔끔하게 짜야하는지 절실하게 느꼈다. 내 코드를 유지보수할 누군가를 위해 앞으로 코드를 깔끔하게 짤 수 있도록 여러 사람의 코드를 보고 배워야겠다고 생각했다.
 이 건을 진행하면서 쿼리의 길이에 대해 신경쓴 적이 없었는데 길이 제한이 있을 수도 있다는 것을 깨달았다. 그런데 확실히 500건씩 묶는 조건만 없었다면 속도가 더 빨라졌을거다.
 그리고 이 건이 성능 개선 목적인만큼 나는 정량적인 수치를 보여주고 싶었고, 그렇게 하기 위해 호출되는 컨트롤러 메서드에 로그를 남겨서 얼마나 시간이 걸리는지 확인했다. 그런데 이 과정에서 좀 더 디테일하게 어느 단계에서는 얼마의 시간이 걸리는지 알았다면 더 좋았을 것 같다. 팀장님도 이 부분에 대해서 내게 물어보셨는데, 미처 생각지 못한 부분이었다. 다음에 혹시 비슷한 건이 있다면 꼭 디테일하게 봐보자.
 그리고 정말 마지막으로... 코드를 작성하기 전에 충분한 설계가 미리 이루어져야 한다는 걸 또 한 번 느꼈다. 개발하는데 일주일남짓의 시간이 소요됐는데, 테스트하다가 자꾸 새로운 케이스가 나오고 그래서 수정하고 테스트하는 과정을 반복했다. 미리 충분한 테스트케이스들을 생각해보고 로직을 어떻게 짜면 좋을지 생각해보고 코드를 짜는 게 베스트인 것 같다. 지금은 새로운 프로젝트를 기획하는 단계인데 이번엔 꼭 충분히 요구사항과 테스트케이스를 생각해보고 설계하고 난 뒤 코드를 짜려고 하고 있다.