쿼리를 작성할 때 최적화해서 만드는 것이 중요합니다.
잠금(Locking), 입출력, 네트워크 트래픽 등을 최소화하고 서버의 공간과 자원을 최적으로 관리 해야 합니다.
이를 위해서 효과적으로 튜닝하는 규칙에 대해 정리 합니다.
1. 커서(Cursor) 사용을 지양해야 합니다.
커서는 일련의 데티러르 순차적으로 엑세스 하는 기능입니다.
커서는 속도 문제 뿐 아니라, 블록(Block) 시킬 수도 있습니다.
시스템의 동시성을 크게 저하시키기 때문에 사용을 피하는 것이 좋습니다.
2. 커서를 피할 수 없다면 임시테이블을 사용
커서를 꼭 사용해야 하는 경우, 임시 테이블을 만들어서 커서작업을 수행하는 것이 좋습니다.
3. 임시테이블을 현명하게 사용
예를 들어서 많은 데이터가 들어있는 테이블과 조인해야 한다면
필요한 데이터만을 추출한 임시테이블을 만들고 해당 테이블과 조인을 하면 성능이 개선될 수 있습니다.
4. 데이터를 미리 준비
커다란 테이블과 조인 작업을 할 보고서나 프로시저가 있다면,
미리 테이블을 조인시키고 테이블들을 하나의 테이블에 영속화 시켜 데이터를 준비하면 좋습니다.
그러면 대규모 조인을 피할 수 있어서 좋습니다.
5. 복합뷰는 최소화
뷰는 쿼리를 사용자들로부터 가리는데는 훌륭한 기능입니다.
하지만, 뷰 안에서 다른 뷰를 중첩해서 사용하다보면 성능 저하가 심각할 수 있습니다.
6. UPDATE 대신 CASE를 사용
데이터 추출을 할 때, 특정값으로 나와야 하는 경우가 있습니다.
이때, UPDATE로 데이터를 변경하는 것보다는 CASE로 추출할 때만 하는 것이 좋습니다.
7. 스칼라 대신 테이블 반환 함수 사용
쿼리의 SELECT 목록에서 스칼라 함수를 사용하는 것보다 쿼리에서 테이블 반환 함수를 사용하고,
CROSS APPLY문을 사용하면 성능이 대폭 개선됩니다.
8. SQL에서 분할(Partition)을 활용
SQL SERVER 엔터프라이즈 버전의 경우, 성능을 가속화 하기 위해 데이터 엔진의 자동 분할 기능을 사용할 수 있습니다.
SQL SERVER에서는 간단한 테이블도 분할로 생성이 되며, 사용자는 더 분할할 수도 있습니다.
테이블 간에 많은 양의 데이터를 옮겨야 할 경우 INSERT와 DELETE문 대시 SWITCH명령을 사용할 수 있습니다.
9. 배치모드로 DELETE와 UPDATE 작업 수행
DELETE와 UPDATE 모두 하나의 트랜잭션으로 실행이 되며,
프로세스 중지하거나 문제가 생기면 시스템을 전체 복원시켜야 한다.
이 작업들은 다른 트랜잭션들을 블록시킬 뿐 아니라 많은 시간이 소요되기도 합니다.
이를 해결하기 위해서 작은 배치 단위로 작업을 하는 것입니다.
문제가 생겨도 소수의 데이터만 복원을 시키면 됩니다.
10. 천천히 작업
시스템이 다운되지 않는 것이 제일 중요합니다.
배치 작업들을 나누고, 천천히 실행을 시켜야 합니다.
11. 저장프로시저 사용을 지향
저장 프로시저는 트래픽을 크게 줄여줍니다.
프로파일러 같은 도구를 사용해서 추적하기는 더 쉽기도 합니다.
이를 이용해서 통계치 확보 및 문제를 빨리 규명할 수 있도록 합니다.
또한, 실행계획을 재사용할 계획이 높기도 합니다.
12. 더블 디핑(Double Dipping:중복 처리)를 지양
저장프로시저를 사용하다보면 더블 디핑으로 이어질 수 있습니다.
대규모 테이블에 별개의 쿼리를 여러개 실행하고,
그것들을 임시 테이블에 넣은 다음에 다시 조인하는 것입니다.
이는 성능에 많이 안좋습니다.
대규모 테이블의 경우, 한번만 쿼리하는 것이 훨씬 좋습니다.
13. 큰 트랜잭션은 작은 트랜잭션 여러개로
단일 트랜잭션에서 여러 개의 테이블을 처리하면,
해당 트랜잭션이 끝날 때까지 모든 테이블을 잠글 수 있습니다.
이는 다수의 블로킹으로 이어지기도 합니다.
이를 해결하기 위해 트랜잭션을 개별적으로 단일 테이블에 대한 작업을 하는 여러개의 트랜잭션으로 분할합니다.
14. 트리거(Trigger) 사용을 자제
트리거는 완료될 때까지 여러개의 테이블을 잠그는 결과를 초래할 수 있습니다.
이런 트리거를 별개의 트랜잭션으로 쪼개면 더 적은 수의 자원을 잠그게 됩니다.
가능하면 트리거를 피하는 것이 좋습니다.
댓글