▩ Heavy-duty 쿼리 대신 저장 프로시저 사용
- 이 경우 Network Traffic을 감소 시킨다.(전체 쿼리 텍스트 대신 프로시저 이름만 전송)
- 프로시저는 보안을 강화하고 데이터 개체를 숨길 수 있다.
▩ 프로시저 코드 사이즈와 복잡성의 최소화
- 코드 사이즈가 크고 복잡할 수록 캐싱되어 쿼리 플랜을 작성하는데 효과적이지 않다.
- 프로시저가 작을 수록 컴파일 시간은 짧아진다.
▩ SET NOCOUNT ON 삽입
- 반영된 ROWS 메시지 반환을 하지 않아 Network Traffic을 감소시킨다.
▩ FULL NAME(4-partname)을 사용하여 호출
- 실행 원하는 프로시저의 혼란 발생 가능성이 존재한다.
- 프로시저 재사용의 가능성이 좋아서 성능을 향상시킨다.
SELECT fname, lname FROM Employees
... instead of
SELECT fname, lname FROM Northwind.dbo.Employees
▩ 레코드셋의 특정 정수값 리턴리 RETURN문을 사용
- 레코드셋을 만들지 않고 RETURN을 사용하면 성능을 향상시킬 수 있다.
▩ 'sp_'로 시작하는 프로시저 명을 사용 금지(dbo 안 쓴 경우와 동일 현상)
- "Master > full name에서 적시된 DB" 순으로 프로시저를 탐색한다.
- DB 명시 않을 시 Master에서 우선 탐색한다.
- Master에 있는 프로시저명과 다른 DB에 있는 프로시저명이 동일할 땐, Master에 있는 프로시저가
실행된다.
▩ 임시 저장 프로시저 대신 sp_executesql 사용
- 임시 저장 프로시저는 실행 계획 재사용이 불가하다.
▩ execute문 대신 sp_executesql 사용
- sp_executesql은 매개 변수를 지원하기에 execute문보다 가독성이 좋아 쓰랜다..(ㅡㅡ'')
- 자체만으로 봤을 때 sp_executesql은 캐시 재사용이 되어 실행계획 재사용이 된다.
▩ 큰 프로시저(if~등으로 분기되는 구문)의 경우 Sub 프로시저로 분기
- DDL이나 대량의 DML은 재컴파일을 유발할 수 있다.
- if~등으로 분기되는 구문에 따라 실행계획이 달라질 수 있다.
- 분기된 Sub 프로시저 하나가 재컴파일을 해도 다른 Sub 프로시저는 재컴파일 되지 않는다.
▩ 임시 테이블 사용
- 임시 테이블과 DDL 구문은 재컴파일을 유발할 가능성이 크다.
- 실행계획 재사용 기회를 감소시킨다.
- DROP TABLE 임시테이블은 시스템 블로킹(6.5)나 재컴파일을 유발(7.0~)하므로 프로시저내 사용
후 삭제하지 않는다.
- 임시 테이블이 크다면 가능한 인덱스를 적용하는것이 효과적이다.
▩ 필요시 with recompile 구문 사용
- with recompile 사용시 실행계획과 프로시저는 캐싱되지 않고 실행시 매번 재컴파일 된다.
- 매번 잘못된 실행계획이 사용될 때는 with recompile 구문 사용하는게 성능에 도움이 된다.
▩ Coding Foramt의 일관성
- 예약어(SELECT, CREATE...)는 대문자로, 사용자 정의어(table, varible, column name...)는
소문자로 작성한다.
- 절 단위로 내려쓰기, 구문 단위로 들여쓰기를 적절히 사용 한다.
SELECT em.fname,
em.lname,
jb.job_desc
FROM pubs.dbo.Employees em WITH (NOLOCK)
JOIN pubs.dbo.Jobs jb WITH (NOLOCK) ON (em.Job_Id = jb.Job_Id)
ORDER BY em.fname
▩ 컬럼을 반드시 명시
- SELECT절과 INSERT절에서 반드시 컬럼명을 명시한다.
- Network Traffic을 감소시키고, Buffer 활용도가 높아 지며, 에러 발생시 가독성이 좋다.
(au_id, au_lname, au_fname, phone, contract)
VALUES (@au_id, @au_lname, @au_fname, @phone, @contract)
... instead of
INSERT INTO pubs.dbo.authors VALUES (1, v2, v3, v4, v5, v6, v7, v8, v9)
▩ SELECT 기능시 컬럼 Alias 명시
- count(*)나 명시적 값 지정시 Alias를 명시한다.
SELECT Case_Id,
COUNT(*) AS ‘Total Case Instances’
FROM mydb.dbo.Events
GROUP BY Case_Id
... instead of
SELECT Case_Id,
COUNT(*)
FROM mydb.dbo.Events
GROUP BY Case_Id
▩ IF절이나 WHILE절에서 BEGIN~END 구문 명시
- 명령어 한줄이라도 BEGIN~END 구문을 명시한다.
IF (EXISTS (SELECT 1 FROM master.dbo.sysobjects WITH (NOLOCK)
WHERE (type = ‘U’)))
BEGIN
SELECT @lExitValue = 1
END
ELSE
BEGIN
SELECT @lExitValue = 0
END
RETURN @lExitValue
... instead of ...
IF (EXISTS (SELECT 1 FROM master.dbo.sysobjects WITH (NOLOCK)
WHERE (type = ‘U’)))
RETURN 1
RETURN 0
▩ RETURN의 다중 사용 금지
- One-Way-In and One-Way-Out을 처리하여 Flow 단순화를 위해 RETRUN을 한번만 사용한다.
IF (EXISTS (SELECT 1 FROM master.dbo.sysobjects WITH (NOLOCK)
WHERE (type = ‘U’)))
BEGIN
SELECT @lExitValue = 1
END
ELSE
BEGIN
SELECT @lExitValue = 0
END
RETURN @lExitValue
... instead of
IF (EXISTS (SELECT 1 FROM master.dbo.sysobjects WITH (NOLOCK)
WHERE (type = ‘U’)))
RETURN 1
RETURN 0
▩ OR 연산자 대신 IN 연산자 사용
- IN 연산자가 OR 연산자보다 성능이 더 효과적이다.
SELECT Emp_Id
FROM pubs.dbo.Employees
WHERE (Emp_Id IN (1, 2, 5))
... instead of
SELECT Emp_Id
FROM pubs.dbo.Employees
WHERE ((Emp_Id = 1) OR
(Emp_id = 2) OR
(Emp_id = 5))
▩ IN 연산자 대신 BETWEEN 연산자 사용
- 가독성을 좋게 만든다.
SELECT Emp_Id
FROM pubs.dbo.Employees
WHERE (Emp_Id BETWEEN 1 and 10)
... instead of
SELECT Emp_Id
FROM pubs.dbo.Employees
WHERE (Emp_Id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
▩ SET ROWCOUNT 대신 MAX/MIN 사용
- MAX/MIN이 정렬대신 TOP 엔진을 사용하여 비용이 더 저렴하다.
SELECT @lMax = MAX (Emp_id)
FROM pubs.dbo.Employee
... instead of ...
SELECT ROWCOUNT 1
SELECT @lMax = Emp_id
FROM pubs.dbo.Employee
ORDER BY Emp_Id DESC
SELECT ROWCOUNT 0
▩ 서브쿼리의 존재 유무는 COUNT(*) 대신 EXISTS로 사용
- EXISTS는 한 ROW만 찾아도 중지되지만, COUNT는 모든 쿼리를 실행한다.
IF (EXISTS(SELECT 1 FROM pubs.dbo.Employee WHERE Emp_Id = 2))
... instead of ...
IF (SELECT COUNT(*) FROM pubs.dbo.Employee WHERE Emp_Id = 2) > 0
▩ WITH (NOLOCK) 힌트 명시
- WITH (NOLOCK) 힌트는 배타적 잠금(X)과의 공유 잠금을 하지 않는다.
- 'Dirty Read'를 허용한다.
- UPDATE, DELETE문을 포함시에는 사용하지 말아야 한다.
SELECT fname,
lname,
Emp_Id
FROM pubs.dbo.Employee WITH (NOLOCK)
... instead of
SELECT fname,
ProgressBar1.Valuelname,
Emp_Id
FROM pubs.dbo.Employee
▩ GROUP BY, DISTINCT, HAVING, ORDER 사용 자제
- 가능하다면, 위 명령어의 사용을 자제한다.
- 위 명령어들은 worktable을 생성하고 추가적인 처리가 필요하다.
▩ UNION 대신 UNION ALL 사용
- 중복이 허용되거나 주 쿼리와 서브 쿼리 사이에 유일성이 보장되면 UNION ALL을 사용한다.
- UNION ALL은 정렬을 하지 않아 UNION보다 비용이 저렴하다.
▩ 부정형 조건문(NOT IN, NOT EXISTS, <>) 사용 금지
- INDEX는 거의 부정문에 활용되지 않는다.
- 부정형 조건문은 Processing과 IO에서 비용이 큰 Operation을 사용하는 경향이 있다.
▩ 매개 변수 비교의 WHERE절 사용 금지
- 매개 변수의 비교는 IF 문에서 사용한다.
- WHERE절에서 사용시 NO Result일 것이 Empty Result가 나온다.
SELECT SomeColumn
FROM mydb.dbo.SomeTable
WHERE (Column = 5)
... instead of
FROM mydb.dbo.SomeTable
WHERE (Column = 5) AND (@Arg = 14)
▩ 매개 변수의 초기화
- 매개 변수는 사용하기 이전 초기화를 하여 NULL 결과를 피한다.
DECLARE @cPrice money
SELECT @cPrice = 100 -- Default value if no rows exist
SELECT @cPrice = ISNULL (price, 100)
FROM mydb.dbo.Titles
WHERE (title_id = ‘MC3026’)
SELECT ‘Value of I’ = @cPrice -- Will never be NULL
... instead of
DECLARE @cPrice money
SELECT @cPrice = ISNULL (price, 100)
FROM pubs.dbo.Titles
WHERE (title_id = ‘MC3026’)
SELECT ‘Value of cPrice’ = @cPrice -- Might be NULL
▩ ANSI JOIN문 사용
- OUTER JOIN의 경우 ANSI JOIN과 T-SQL JOIN의 결과가 다를 수 있다.
▩ EXEC와 매개 변수 입력
- 다른 프로시저 호출시 반드시 EXEC(UTE)를 사용한다.
- 프로시저 파라미터 사용시 매개 변수명도 입력하여 Interface 변화에 유동적으로 한다.
EXEC sp_help @objname = ‘authors’
... instead of ...
sp_help authors
▩ Application에서 RPC Event 호출
- 실행 계획 재사용 능력 및 성능 향상(RPC Event 사용시)
- 파라미터 Caching/재사용 능력 제공
- SQL Injection을 포함한 보안 강화
▩ Application에서 파라미터 개체 자동 생성
- 추가 Request, I/O등 성능 문제 유발
- sp_procedure_params_rowset 실행
- 서버 라운드 트립 유발
- 시스템 테이블 접근 유발
▩ Application에서 파라미터 암시적 형식 지정
- App/DB에서 암시저거 형변환 유발
- 불필요한 프로시저 캐시 소비
- SARG 위반할 경우 성능 문제 유발
- Parameterized Query에서 특히 주의
▩ 재컴파일 유발 방지
- SET 세션 옵션 변경 금지
- DDL구문 사용 금지
- DDL/DDL 구문 교차 사용 금지
- 임시 테이블 사용 금지
- 'sp_' 시작 금지
- 개체의 2-partname 이상 사용
▩ SARG 유지
- Non-SARG인 경우 프로시저의 성능을 절대 보장하지 않는다.
▩ Parameter Sniffing 문제
- Parameter Sniffing : 프로시저 내 쿼리가 최초 제공되는 파라미터 값으로 컴파일 되는 것.
- 문제 : 최초 생성된 실행계획이 맞지 않아 발생.
- 날짜 범위 검색의 경우 특히 주의한다.
- 입력 매개변수를 프로시저 내부에서 가공하여 값을 변경시키지 않는다.
- 필요시 프로시저를 분기킨다.
BEGIN
IF @date IS NULL
SET @date = dateadd('mm',-3,(SELECT MAX(OrderDATE)
FROM Sales.SalesOrderHeader))
EXEC GetRecentSalesHelper @date
END CREATE PROCEDURE GetRecentSalesHelper (@date datetime)
AS
BEGIN
SELECT *
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID
AND h.OrderDate > @date
END ... instead of CREATE PROCEDURE GetRecentSales (@date datetime)
BEGIN
IF @date IS NULL
SET @date = dateadd('mm',-3,(SELECT MAX(OrderDATE)
FROM Sales.SalesOrderHeader))
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID
AND h.OrderDate > @date
END
▩ 파라미터값이 전달된 내부 변수의 사용
- 컴파일시 내부 변수는 반영되지 않아 그 값을 알 수 없기에 실행 계획에 오류가 발생한다.
- 꼭 필요한 경우 프로시저를 분기 시킨다.
▩ 파라미터에 따른 다른 실행 계획
- 파라미터에 따라 모든 쿼리의 실행 계획을 정확이 판단할 수 없다.
- 파라미터에 따라 프로시저를 분기 시킨다.
▩ 동적 쿼리
- 동적으로 달라지는 쿼리에 따라 실행 계획이 맞이 않게 된다.
- 모든 경우의 쿼리를 적절히 분기시켜 성능 확인을 한다.
▩ 프로시저 이득
- 실행 계획 Caching 을 통한, 성능 이득.
- Network Traffic 최소화.
- 출력 Parameter, Return 값 사용.
- Ownership Chain 을 통한 권한 처리, SQL Injection 차단 등의 보안 기능.
- 업무 논리의 캡슐화, 모듈화.
- SQLXML 3.0 이후 릴리스에서 XML WebService 노출 기능.
- 이 외에도 적용 방법과 구성에 따라 추가 .