[MS-SQL]쿼리 최적화를 위한 참고사항

쿼리를 작성할 때 최적화해서 만드는 것이 좋습니다.

쿼리를 튜닝해서 최적화시키고,

더 빨리 나오게 하는 것이 중요합니다.

잠금(Locking), 입출력, 네트워크 트레픽 등을 최소화하겨

서버의 공간과 자원을 최적으로 관리를 해야 합니다.

이를 위해서 효과적으로 튜닝하는 21가 규칙에 대해서

정리를 해보고자 합니다.

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. ORM을 지양

ORM(Object Relational Mapper : 객체 관계형 맵퍼)는

최악의 코드를 만들어 냅니다.

피할 수 있으면 피하는 것이 좋습니다.

피할 수 없다면, 스스로 자체적인 저장 프로시저를 작성하여

ORM이 자체 쿼리를 작성하는 대신

사용자가 작성한 프로시저를 호출하게 해야 합니다.

12. 저장프로시저 사용을 지향

저장 프로시저는 트래픽을 크게 줄여줍니다.

프로파일러 같은 도구를 사용해서 추적하기는 더 쉽기도 합니다.

이를 이용해서 통계치 확보 및 문제를 빨리 규명할 수 있도록 합니다.

또한, 실행꼐획을 재사용할 계획이 높기도 합니다.

그렇기 때문에 사용을 지향하는 것이 좋습니다.

13. 더블 디핑(Double Dipping:중복 처리)를 지양

저장프로시저를 사용하다보면 더블 디핑으로 이어질 수 있습니다.

대규모 테이블에 별개의 쿼리를 여러개 실행하고,

그것들을 임시 테이블에 넣은 다음에 다시 조인하는 것입니다.

이는 성능에 많이 안좋습니다.

대규모 테이블의 경우, 한번만 쿼리하는 것이 훨씬 좋습니다.

14. 큰 트랜잭션은 작은 트랜잭션 여러개로

단일 트랜잭션에서 여러 개의 테이블을 처리하면,

해당 트랜잭션이 끝날 때까지 모든 테이블을 잠글 수 있습니다.

이는 다수의 블로킹으로 이어지기도 합니다.

이를 해결하기 위해

트랜잭션을 개별적으로 단일 테이블에 대한 작업을 하는

여러 개의 트랜잭션으로 분할합니다.

15. 트리거(Trigger) 사용을 자제

트리거는 완료될 때까지 여러 개의 테이블을 잠그는 결과를 초래할 수 있습니다.

이런 트리거를 별개의 트랜잭션으로 쪼개면

더 적은 수의 자원을 잠그게 됩니다.

가능하면 트리거를 피하는 것이 좋습니다.

16. GUID에 대한 클러스터링 지양

테이블 데이터 정렬을 위해

GUID(Globally Unique Identifer : 범용 고유 식별자)를 사용하지 않는 것이 좋습니다.

DATE나 IDENTIFY 같은 값을 증가시켜 데이터를 정렬하는 것이 좋습니다.

17. 테이블에 있는 모든 것을 카운트(COUNT) 하지 말자

테이블에 데이터가 존재하거나 어떤 고객에 대한 데이터가 있는지 확인할 필요가 있다고 했을 때

아래처럼 하는 경우가 많이 있습니다.

SET @CT = (SELECT COUNT(*) FROM dbo.TEST);
If @CT > 0
BEGIN 
END

이를 이렇게 변경할 수 있습니다.

If EXISTS (SELECT 1 FROM dbo.TEST)
BEGIN

END

이렇게 하는 것이 속도 차이는 많이 납니다.

18. 행을 카운트 할 때는 시스템 테이블 (System Table) 사용

데이터가 많은 테이블에서 몇개의 데이터가 있는지 확인해야 할 수 있습니다.

이 때, 전체 테이블을 count로 해서 확인하면 서버에 부하가 갑니다.

이를 시스템 테이블 혹은 시스템 명령어를 이용하면 됩니다.

sp_spaceused 테이블명

위처럼 하면 됩니다.

위 이미지에 보면 rows값이 있습니다.

해당 컬럼 값이 데이터 건수 값입니다.

아래는 count로 해서 뽑은 것입니다.

이렇게 동일하게 나오기 때문에 해당 명령어를 이용하면 더 빠르게 할 수 있습니다.

19. 필요한 열먼 가져오기

SELECT * 로 해서 데이터를 가져오는 것을 지양해야 합니다.

필요 없는 컬럼의 값들도 가져오기 때문에 안좋습니다.

필요한 컬럼명을 지정하면 네트워크 트래픽도 줄이고

서버의 부하도 줄일 수 있습니다.

20. 네거티브 검색을 피하기 위해 쿼리를 재작성

인덱스를 사용할 수 없는 쿼리를 사용해서 데이터를

행 별로 비교할 필요가 있을 수 있습니다.

이 때는 인덱스를 사용할 수 있도록 쿼리를 재작성 하는 것이 좋습니다.

인덱스를 사용하면 성능을 크게 향상시킬 수 있습니다.

21.맹목적인 코드 재사용을 지양

필요한 데이터를 끌어온다는 것을 알고 있기 때문에

다른 누군가의 코드를 복사해서 사용하는 경우가 많이 있습니다.

이 때는 필요한 데이터보다 더 많은 데이터를 가지오는 경우가 많이 있습니다.

코드를 재사용하더라도 필요한 수준으로 줄이는 작업이 필요합니다.

이상으로 MSSQL 및 타 이기종 DB에서도 다 사용을 하거나 참고할 수 있는 쿼리 튜닝 참고사항이었습니다~