[김정삼의 SQL튜닝] 대량목록조회성능이슈

| 개요 |

☞ 대량 목록 조회 관련한 성능 이슈는 해결이 쉽지 않은 고질적인 성능 이슈 입니다.

 대량 목록 조회란? 

• 예를 들어, 기업 고객의 통신 서비스 계약 목록, 당일의 미처리 민원 접수건, 특정 시군구의 복지급여수급자 목록을 조회하는 경우, 그 결과 건수가, 적은 경우 수십건에 불과하지만, 많은 경우 수만건이 될 수 있습니다. 

• 다양한 예외가 존재하기는 하지만, 일반적으로 SQL 응답시간은 결과 건수에 비례하므로, 결과 건수가 많으면 응답시간도 늦고 사용자가 성능에 불만족을 느낄 가능성이 높습니다.

• 이런 류의 대량 목록 조회는 60초 가량의 타입아웃 시간이 적용되는 온라인 처리 AP 기능으로 구현되기도 하지만, 타임아웃 시간을 넉넉하게 설정한 리포트 서비스('On-demand 배치' 또는 '온라인 배치' 라도고 함)로 구현되기도 합니다.

• 대량 목록 조회는 비교적 많은 양의 조회 결과, 정렬 요건, 전체 건수 조회 요건, 선택적인 변수 조회 요건 등이 복합되어, 이슈 해결이 쉽지 않습니다.

 부분범위 처리 

• 비록 결과 건수가 많더라도 '부분범위 처리' 기능이 제대로 작동한다면 오히려 사용자는 결과 목록을 신속하게 조회할 수 있습니다. 

• 그런데, C/S 환경에서는 '부분범위 처리' 기능이 기본적으로 작동되지만, 웹 같은 Multi-tier 환경에서는 그렇지 않습니다.

☞ 대량목록조회 성능 이슈는 AP 및 SQL 튜닝을 통해서 많은 부분 해결 가능하지만, 

    사용자 요건을 일부 변경해야 할 수도 있습니다.

 대량 목록 조회 시 성능 이슈가 발생하는 이유 

• 웹 환경에서는 자원 효율화 측면에서 유리한 ‘connectless’ 환경(connection pool 사용)을 사용함으로써 기본적으로 '부분범위 처리' 기능이 작동하지 않습니다.

• 목록 조회는 정렬 요건을 포함하는 것이 일반적인데, 이 경우 부분범위 처리가 어렵습니다.

• 특히 대량 목록 조회 요건인 경우, 페이지 네비게이션 기능과 함께 전체 건수 조회 쿼리도 실행해야 할 수 있는데, 이 경우 목록 조회 보다 전체 건수 조회 시간이 더 많이 소요될 수 있습니다. 

• 대개 여러 입력 가능한 변수를 선택적으로 입력할 수 있게 화면을 구성하면서, 목록 조회 쿼리는 하나를 작성하는데, 이 경우 옵티마이저가 모든 입력 케이스를 고려하여 실행계획을 최적화할 수 없습니다.

 성능 개선 방안 

• 부분범위 처리를 모방할 수 있도록 페이징 처리 코드 구현 및 화면 설계

• 선택적인 조회 조건 처리 성능 이슈 해소를 위한 쿼리 재 작성 및 인덱스 재설계

• 테이블 데이터 정렬을 통한 넓은 범위 액세스 성능 개선

• 성능 개선을 위해 사용자 요건을 일부 바꿀수 있는지 확인

-  전체 결과 건수 조회 요건 제외 가능 여부 확인

-  정렬 요건 변경 가능 여부 확인

-  필수 조건절 추가 가능 여부 확인

-  실시간 조회 요건 여부 확인(실시간 조회 요건이 아니면, 집계 테이블 사용 가능)

-  조회 범위를 좁힐 수 있는지 확인(예를 들어, 시군구 단위 -> 읍면동 단위)

| 페이징 처리와 부분범위 처리 |

☞ Toad, Orange, SQL Developer와 같은 SQL 개발 도구는 C/S 환경(2-Tier)에서 

    작동되나, 프로그램의 실 수행환경은 웹 환경(Multi-tier)입니다.

☞ 웹 환경(Multi-tier)에서도 쿼리문에 페이징 처리 로직을 추가하면, C/S 환경과 

    유사한 부분범위 처리가 가능합니다.

| 정렬과 부분범위 처리 |

☞ 페이징 처리 로직이 사용되더라도 ORDER BY 절이 있으면, 부분범위 처리 

    가능 범위가 한정됩니다.

☞ 전체 건수를 보여 줘야 한다면 항상 50만건 이상을 모두 읽어야 합니다. 

    목록을 보여주기 위한 처리보다 더 많은 시간이 소요됩니다.

| 페이징 처리와 읽기 일관성 |

☞ 참고로, 웹 환경에서 페이지 단위로 나누어 전체 목록을 완성하는 경우, 

    읽기 일관성이 보장되지 않음을 인지해야 합니다. 

예를 들어, t1 시점에 1/m 페이지 20건의 목록을 추출한 후 t1 시점과 t2 시점 사이에 그 20건의 목록 중 1건이 삭제된다면, t1 시점 2/m 페이지 첫 번째 로우가 t2 시점에 2/m 페이지 목록 추출 시 포함되지 않습니다.

참고로, C/S 환경에서의 부분범위 처리는 최초 조회 시점 기준의 목록을 클라이언트에 순차적으로 넘겨 주므로 읽기 일관성이 보장됩니다.