SW 개발자라면 SQL 쿼리는 뗄레야 뗄 수 없는 부분일것입니다.
쿼리를 어떻게 작성하느냐에 따라 개발 소프트웨어의 성능에도 밀접한 관계가 있으며 그것에 따른 개발 비용도 차이가 있겠지요.
데이터베이스를 관리하고 튜닝을 전문적으로 하는 DBA라는 직군이 IT에서 대우 받는 이유가 그 때문일 것입니다.
개발자들에게 있어 더 나은 제품을 개발 할 수 있는 좋은 쿼리 작성 방법에 대해 알아보려 합니다.
여기서 좋은 쿼리란 필요한 만큼만 리소스(IO,CPU등)를 사용하는 쿼리, 관계형 연산 표준을 지키는 쿼리, 읽고 해석하고 관리하기 좋은 쿼리 등이 되겠습니다.
먼저 아래와 같이 테이블을 조회하여 검색수, 논리적읽기수, 물리적읽기수, 미리읽기수, LOB 논리적읽기수, LOB 물리적읽기수, LOB 미리읽기수 등의 통계시간정보를 조회하여 보면서 쿼리의 성능을 측정할 수 있습니다.
SET STATISTICS IO ON;
SELECT * FROM CUSTOMERS;
기본고려사항은 아래와 같습니다.
- ANSI 표준 문구를 사용합니다. Old Style의 Join은 SQL Server 2014부터 지원하지 않습니다.
- WHERE 절 및 FROM 절 사용 순서.
– WHERE 절은 순서에 상관 없습니다. 그러나 가독성이 좋게 작성 할 필요가 있겠지요.
– FROM 절 INNER JOIN의 순서는 성능에 상관이 있습니다. - SQL Server에서 개체를 식별하는 방법은 스키마.개체명
– 동시사용자가 많은 시스템의 경우 시스템 성능에 영향을 미칠 수 있기 때문에 스키마는 가급적 사용합니다.(예:dbo.Customers)
– 쿼리끝에 세미콜론(;)을 찍어 줍니다. - 문자열 비교 시 후행 공백은 비교하지 않는다.
– 데이터 저장 시 만 공백 처리를 합니다.
– 데이터 조회 시 후행 공백은 확인 필요합니다.
– 비교할 때 RTRIM은 필요 없습니다. - 날짜 시간(DateTime) 상수
– 구분 문자 들어가게 되면 포맷에 영향을 받기때문에 Global 표준 형식을 고려하여 yyyyMMdd 로 사용 해야 합니다.
– 시간의 밀리세컨드는 ‘.997’까지 지원됩니다. ‘.998’ 입력 시 자동으로 ‘.997’으로 처리가 됩니다.(DateTime의 8바이트, 일자 4, 시간4)
– 시간(@시간)만 업데이트 시 : 1900-01-01 @시간으로 처리합니다.참고로 .Net 의 DateTime.MinValue 값은 1900-01-01 입니다.
– COL_DT BETWEEN A AND B , COL_DT >= A AND COL_DT <= B 시간때문에 두 쿼리는 다른 결과를 도출할 수 있습니다. - JOIN절에는 JOIN 조건만, WHERE에는 결과 조건만 사용합니다.
SARG를 분석하여 쿼리를 최적화 하는 방법도 있습니다. 여기서 SARG란 검색 대상 및 범위를 제한 할 수 있는 <컬럼><연산자><수식> 형식으로 인덱스 사용 및 쿼리 최적화를 위한 필요조건입니다.
WHERE, JOIN 등의 검색 조건은 SARG 를 만족해야 하며 아래와 같은 규칙을 지키도록 합니다.
- 불필요한 열 참조 말기.
- 불필요한 부정 조건 쓰지 말기.
- 검색 대상 컬럼 변환 하지 말기.
- 비교 대상 데이터 형식 다르게 하지 말기.
- LIKE 첫문자에 불필요한 Wildcard 문자 쓰지 말기.
- 모호한 검색 조건 쓰지 말기.
- 복잡한 검색 조건 쓰지 말기.
아래와 같은 연산은 인덱스를 사용하지 못하게 하는 형식으로 피해야 할 것 입니다.
부적절한 LIKE 연산 : CustomerID LIKE ‘%AAA%’ ⇒ CustomerID LIKE ‘AAA%’
컬럼에 연산자 적용 : Col * 10 = 10500 ⇒ Col = 10500 / 10
컬럼에 함수 적용 : Convert(varchar,orddate,121) = ‘20170413’
암시적 형변환 : OrderID = 1234 ⇒ OrderID =‘1234’
두 컬럼값 비교 : where CustomerID = OrderID
그 밖에 좋은 쿼리작성을 위해서 아래와 같은 지침을 참고할 수 있습니다.
- VIEW, Function 등의 외부 쿼리에서 SARG를 만족 하도록 설계한다.
- 반드시 필요한 데이터(열, 행)를 필요한 시점에만 요구하고 TOP 연산자를 적절히 활용한다.
- 동일 데이터를 두번 이상 읽지 않는다.
- 불 필요하게 범위 조건이나 LIKE 조건을 사용하지 않는다.
- 불 필요한 연산을 줄인다.
- 함추호출을 최소화한다.
- NOLOCK 힌트나 잠금 세션 옵션을 적절히 사용한다.
IN 절과 BETWEEN 절도 의미와 용도에 따라 선택하여 사용할 수 있습니다.
IN 절
- Equal(=)조건과 OR 연산(합집합) 결합
– 검색 분량에 따라 인덱스 사용기회 감소
– GROUP BY 등은 불필요함.
– Random Access(IO) 동작.
- 비 연속 값 검색 시 사용.
- 검색 대상이 아주 적은 경우 적합.
BETWEEN 절
- Non-Equal 조건과 AND 연산 결합.
– Sequence Access 동작
- 연속값 검색 시.
COUNT 함수를 데이터 존재여부를 체크하는 방법으로 쓰지 말고 EXISTS 함수를 쓰도록 합니다.
- IF (SELECT COUNT(*) FROM Eplan.dbo.[Order Details] WHERE Quantity > 50) > 0 ⇒ IF EXISTS (SELECT * FROM Eplan.dbo.[Order Details] WHERE Quantity > 50)
대량 NULL 값을 가진 열은 불필요한 NULL 데이터를 사전에 필터링하도록 합니다.
- SELECT SUM(FREIGHT) FROM dbo.BigOrders WHERE FREIGHT IS NOT NULL;
JOIN과 WHERE 절의 테이블 지정 순서는 다음을 따르도록 합니다.
JOIN
– 기준 테이블, 선택도 높은(검색 행수가 적은) 테이블.
– INNER JOIN, OUTER JOIN.
WHERE
– 같은 테이블, 별칭 순으로.