. (주)씨퀄로 : http://www.sqlroad.com
. SW개발 : http://www.sqlroad.com/080819-2/ (SQLBigEyesHammer V3 Freeware)
. 은행쿼리 : 22GB -> 0.1MB (218,000배 향상) : where : 함수 X
1. 실행계획 (인덱스 사용 여부)
2. 세션옵션 (IO) (테이블 단위 페이지 IO량 확인)
. 실제 실행계획 : 검색, seek (OK)
. 논리적 읽기, lob 논리적 읽기
. lob (Large Object) : text, image, ....
. ANSI 표준 사용하기
. WHERE 절 작성 순서는 성능과 연관?
- 옵티마이저가 변경하기 때문에 순서는 성능과 무관하다.
- inner join 일 경우에 해당
. 쿼리문 작성 시 테이블 이름 선언할 때 전체 이름으로 (예 db.schema.object)
- schema 생략 시 옵티마이저가 ID 찾아 사용
. 날짜 그 날 마지막 시간 20211010 23:59:59.997
. between '20211010' and '20211010 23:59:59.99?'
. >= '20211010' and < '20211011'
. 문자열 비교 시 오른쪽 공백은 고려하지 않는다 (rtrim 안해도 된다)
- @varchar, @char
. MSSQL 다른 DB와 다른 것은 잠금(Lock) 부분이 다르게 적용됨
. 의미 오류 (Semantic Error) : 불필요한 조건
- WHERE PK_Col IS NULL
- WHERE ShipVia > 4 OR ShipVia > 2 (false)
- DISTINCT PK_Col, ....
- LIKE 'aaa'
- EXISTS (SELECT DISTINCT.... FORM ... 무조건 1개만 있으면 작업 끝난다)
- ORDER BY PK_Col
- LIKE '%'
- HAVING (WHERE에 있어야 할 조건 경우)
- select 50 orderid from orders (별칭 붙이는 습관이 있어야 함)
2. SARG (검색인수)
. WHERE, FROM, HAVING : TRUE, FALSE, UNKNOWN
. 검색 대상 및 범위를 제한할 수 있는 식
. 인덱스 사용 및 쿼리 최적화를 위한 필요 조건
. 반대적인 조건 사항 : Non-SARG (비 검색인수)
- 불필요한 데이터 (열,행) 요청
- 열 간 비교 (컬럼 대 컬럼)
- 부정문은 긍정문으로 자동 변환 (범위 검색이 된다)
- ID IN (2,4,6) --> ID = 2 or ID = 4 or ID = 6
- NOT IN (가능한 사용하지 말자)
- 복합 Index 선행 열 ( "=" 조건이 제일 먼저 나오게 해야 한다)
ex. PK = (사업장 + 주문일자 + 주문번호)
사업장 LIKE '%' and 주문일자 = '20211011'
(예)
PK (사업자, 일자, 번호)
WHERE 일자 = ?
사업자 LIKE ?
번호 = ?
사업장 만 Seek, 나머지는 스캔 (인덱스 사용 못 함)
*** 쿼리 스토어 기능 권장 (성능관리) ver 2017 이상
. WHERE 함수 (가급적 사용 X)
. WHERE SUBSTRING(CustomerID, 1, 3) = 'CEN' --> WHERE CustomerID LIKE 'CEN%'
. WHERE CONVERT(varchar(8), OrderDate, 112) = '19960704' --> WHERE OrderDate >= '19960704' AND OrderDate < '19960705'
. WHERE DateDiff(dd, ShippedDate, '19980506') <= 1 --> WHERE ShippedDate >= DATEADD(dd, -1, '19980506')
. MSSQL은 NULL 값 인덱스 값으로 저장된다.
. WHERE ISNULL(OrderDate, '19970702') = '19970702' --> WHERE (OrderDate = '19970702' OR OrderDate IS NULL)
*** ORM 파라미터 : WHERE col = @id 대부분 유니코드('N')로 인식 함. (예) NVARCHAR
. LIKE : 문자열에만 사용, %???% (앞에 % 있으면 안된다)
. 열 간 비교
- select emlid, ship from orders where emlid = ship and ship = 2
- where emlid = case @ship is null then emlid else @ship end
. OR 인덱스 안 걸리면 --> UNION 으로 변경
3. 조건절 상수화 이슈
. WHERE OrderID > @ID
- 상수화 할 수 있어야 옵티마이저가 실행계획을 예측 가능하게 된다.
- 쿼리 컴파일 시점에 @ID 값을 알아야 하기 때문
- 문제되는 조건
. 로컬변수 declare @id int .... where OrderId > @ID
. 사용자 정의 함수 where OrderId > dbo.uf_OrderNo()
. 테이블 변수 성능 문제 (@table --> #Table 로 사용)
- MSSQL 2019 이상 EE "Scalar UDF InLine" 자동 튜닝 지원 (아직 시작 단계. 안정화 진행 중)
- 집합연상 WHERE aaa in (select distinct.....) distinct 할 필요 없다. 중복 자동 제거 됨.
- where a.ordrid = dbo.uf_split('1,2,3,4,..') --> join order as a on a.ordrid = dbo.uf_split('1,2,3,4,..')
- NOLOCK 사용 시 ORDER BY 생략 시 문제 발생할 수 있음 (데이터 정합성 고려)
- ORDER BY 작성 시 유니크한 값이 나오도록 조건을 만들어야 한다.
. select top 5 .... ORDER BY aaa, bbb, ccc....
- count(*) vs count(열) --> null 값이 허용된 경우 count(열)
- count(*) vs exists --> 데이터 존재 여부 경우 exists
- 집계 시 대량의 null 이 있을 경우 is not null 처리
- union --> 자동으로 distinct 처리하는 결과로 처리 (옵티마이저 판단)
- union all --> 전체 데이터 처리
- 채번을 원 쿼리로 : update 채번 set @일련번호 = 일련번호 = 일련번호 + 1 where ....
- OUTPUT inserted.*, deleted.*
4. JOIN
(1) 네스티드 루프 조인
(2) 해쉬 조인
(3) 머지 조인
- 네스티드 루프 조인
. 첫번째 검색 테이블 데이터가 적어야 함. (inner join 인 경우)
. 데이터가 많은 FK에 값 convert 할 경우 --> 첫번째 PK 값을 convert 하는 것이 좋다.
. outer join 시 옵티마이지 처리에 어려울 수 있다. 테이블 순서는 상관없다.
. 나름 기준을 잡아서 테이블 순서를 정하자. (필수 아님)
5. Subquery 이해
. 파생 테이블 : 외곽 테이블의 key 값이 자동으로 안쪽 파생 테이블 필터링 조건에 적용 된다.
. case select... (X) --> select case (O)
. null 값이 없도록 : where customerID not in (select customerID from orders where customerID is not null)
. CTE
- 성능 튜닝 어렵다
. 쿼리 힌트
. 잠금 튜닝
- 조회 전용 쿼리 시 잠금 차단 회피
. table WITH (NOLOCK) : 수정 중인 데이터 값을 가지고 온다. (권장 안함 X. 수정후 데이터를 넘긴다)
. Read Committed Snapshot 사용 권장 O (내부적으로 데이터를 스냅샷 하여 그 데이터를 넘겨준다)
- TempDB 사용할 시 때문에 해당 TempDB 관리 필요.
6. Cursor : 되도록 운영을 위한 사용 하시고 서비스용은 최대한 사용하지 말 것
- 사용한다면
. LOCAL
. FAST_FORWARD or STATIC READONLY FORWARD_ONLY
7. 함수
- InLine Table-Values Function (권장)
