SP 개발 진행시 주의사항

SP 개발 진행시 주의사항

T-SQL 작성 시 체크리스트

- 최소의 SQL문으로 구성된 집합 기반의 솔루션을 작성

- 커서는 많은 리소스를 필요로 하며 상당한 부하를 발생시키므로 사용하지 않는 것이 최선

- 다양한 솔루션을 개발하고 각 솔루션의 성능, 코드의 명료성, 확장성 등을 비교하여 최적의 솔루션을 찾는대 노력

- * 쿼리 작성 후에는 결과만 확인하지 말고 항상 쿼리 옵티마이저가 어떤 실행 계획을 작성했는지 확인

  * (SET SHOWPLAN_ALL ON , SET SHOWPLAN_TEXT ON, SET STATISTICS PROFILE ON)

- SELECT 문에 * 대신 필요한 컬럼명 명시 , 네트워크 트래픽 증가, 테이블이나 뷰의 구조 변경시 오류 발생 가능성

- INSERT 문에 실제로 값이 입력되는 컬럼들의 이름을 기술 (차후 테이블 수정 시 INSERT 문 수정 문제)

- * 오브젝트를 참조할 때에는 항상 오브젝트 소유자를 지정합니다.

- QUERY에 필터를 적용하여 SQL 서버가 접근하는 물리적인 데이터의 범위를 제한하고 인덱스를 사용할 수 있도록 함

- WHERE절은 SARG(Search ARGument) 가 되도록 작성 

- <>,!=, NOT IN, NOT EXISTS와 같은 비동등 연산자의 사용을 최소화

- * 옵티마이저가 행의 퍼센트를 가정하여 실행 계획을 작성함으로 비효율적인 실행계획이 생기지 않게 상수값 또는 입력 매개 변수를 사용

- SQL 서버가 묵시적으로 컬럼의 데이터 타입을 변환함으로써 성능 저하가 발생하지 않도록, 조건절에서 비교되는 식의 데이터 타입을 일치 (= '')

- * 테이블의 전체 행 수를 확인하고자 하는 경우에는 COUNT(컬럼명)대신 COUNT* 를 사용 * 

- * 빈 테이블 또는 Table 변수나 TEMP TABLE에 INSERT를 한 다음에 행수를 확인 시 COUNT* 대신 @@ROWCOUNT 전역 변수를 사용

- * 테이블에 어떤 조건에 해당되는 행이 존재하는지 확인하는 경우에는 COUNT* 대신 IF EXISTS절을 사용합니다.

- * INSERT, UPDATE, DELETE문에 대하여 적절한 오류 처리 루틴을 작성, @@ERROR, @@ROWCOUNT, 저장프로시저의 Return Status값을 활용

- ORDER BY, GROUP BY , DISTINCT, HAVING 절은 반드시 필요한 경우에만 사용, DISTINCT가 반드시 필요한 경우에는 DISTINCT 대신 EXISTS나 IN의 사용을 고려

- SELECT 문이 반환하는 결과 집합이 중복 행을 포함하지 않는 경우에는 불필요한 정렬이 발생하지 않도록 UNION 대신 UNION ALL을 사용

- UPDATE 문 또는 DELETE문의 FROM 절과 조인을 잘 활용합니다. (하나의 UPDATE, DELETE로 처리)

- * Business Requirements에 위배 되지 않는 범위 내에서 가능한 낮은 Transacion Isolation level을 사용, Drity Read가 문제가 되지 않는 경우에는 SELECT 문에 WITH(READUNCOMMITEED)힌트를 사용

- 트랜잭션은 가능한 짧게 작성하며, 트랜잭션 내에서 오류가 발생하면 롤백은 수행하도록 작성해야 하며, 가능한 빨리 롤백을 처리

- * 조인 연산은 ANSI-표준 구문을 사용, (외부 조인의 경우 ANSI, T-SQL 스타일의 조인 결과가 다름)

- NULL 처리 할 경우 ANSI 표준 구문을 사용 , @var IS NULL, @var IS NOT NULL

- 동일한 컬럼에 대하여 여러 개의 상수값을 비교하는 경우에는 OR 대신 IN 을 사용

- * 우선 순위가 서로 다른 연산자들이 혼재되어 있는 식의 경우에는 코드가 명확하도록 괄호를 사용

- * 가능한 임시 테이블을 사용하지 않는다. 임시 테이블이 필요한 경우에는 Table 변수의 사용을 고려해 볼 수 있습니다.

    * CREATE, ALTER 그리고 SELECT를 하게 되는 경우 해당 저장 프로시저는 매번 재컴파일을 해야되는 오버헤드가 발생

- 테이블에 있는 모든 행들을 삭제하는 경우 Truncate Table 명령어를 사용, (실행 구너한과 외래 키 제약 조건 존재 여부, IDENTITY 속성에 대한 내용을 확인 후 삭제)

- 반환되는 행의 수가 많은 경우에는 결과 집합의 크기를 제한합니다. (페이징)

- 성능이 좋다는 것을 검증한 후에 인덱스 힌트를 사용

- T-SQL 문장의 접미사로 세미콜론(;)DMF TKDYD GKQSLEK.

- /* 주석 */, -- 을 사용하여 적절하게 주석을 기술 합니다.

- Ad-hoc Query 대신 저장 프로시저를 사용,  데이터의 보안 및 일관성 유지, 라운드 트립 감소 인한 네트워크 부하 감소, 쿼리 실행 계획의 재사용이 가능

- 사용자 데이터베이스에 생성하는 저장 프로시저의 이름은 'sp_'로 시작하는 이름을 사용하지 않아야 한다.

- 저장 프로시저 호출 시 반드시 소유자를 지정 (EXEC dbo.up_MyProc)

- 저장 프로시저의 입력 매개 변수는 매개 변수의 위치에 맞추어 값만 기술하지 말고 매개 변수의 이름과 값을 함께 기술

- * SET NOCOUNT ON을 기술 함으로써, 클라이언트와 서버 간의 네트워크 라운드 트립을 줄인다.

- 저장 프로시저의 입력 매개 변수에 대하여 디폴트 값 부여, 시작 부분에서 입력 매개 변수의 유효성을 점검함으로써 불필요한 코드 실행을 방지

- * 저장 프로시저의 재 컴파일을 줄이기 위하여, 저장 프로시저의 시작 부분에 모든 DDL들을 기술한 다음에 DML을 기술, 

- * 동적 SQL 문의 사용은 가능한 피하며, 동적 SQL문을 사용해야 하는 경우에는 EXEC 대신 SP_EXECUTESQL을 사용 한다.

*** 프로시저 생성 구문에는 기본정보를 꼭 작성 하여아 한다.

Ex) 작성자:

    작성일:

    목적:

    인자:

    

   - 체크 사항

   - 운영시 주의 사항

   - 개발 가이드에 대한 확인

-- 

SQL Server Count*, Count(컬럼명)의 변환

COUNT * 의 경우 NULL 값도 포함하여 COUNT 하고 컬럼명을 명시해주는 경우는 해당 컬럼의 NULL값은 제외되고 COUNT값을 가지고 온다.

COUNT *, COUNT 컬럼명의 경우 테이블에 CLUSTERED INDEX 가 없고 넌 클러스터 인덱스만 존재하는 경우 COUNT * 은 INDEX SCAN을 COUNT 컬럼명의 경우 TABLE SCAN을 한다.

CLUSTERED INDEX 만 존재 한다면 COUNT * , COUNT 컬럼명은 동일하게 CLUSTERED INDEX SCAN을 한다.

--저장 프로시저 최적화

1. 루틴이 참조하는 테이블의 데이터가 바뀌었다.

  프로시저가 불필요한 재 컴파일이 되지 않게 작성 하여야 한다.

2. 프로시저가 DDL과 DML 동작들을 혼합하여 포함하고 있다.

  Interleaving이 없도록 모든 DDL을 프로시저의 시작 부분에 두어야 한다.

3. 임시 테이블에 대해 어떤 동작들이 수행된다.

  임시 테이블에 대한 모든 참조들은 로컬로 생성된 테입르을 참조하고, 

  호출하는 프로시저나 일괄 처리에서 생성된 임시 테이블을 참조하지 않는다.

  프로시저는 임시 테이블을 참조하는 커서를 선언하지 않는다.

  프로시저는 IF/ELSE나 WHILE과 같이 조건적으로 실행된 문 내에서 임시 테이블을 생성하지 않는다.

* 저장 프로시저의 장점은 좋은 성능, 내부 로직을 숨길 수 있기 때문

여러명의 사람이 개발작업을 수행할 경우 매우 유용.

저장 프로시저를 사용 해야 되는 이유

1.코드의 길이가 짧다.

  네트워크나 SQL 서버의 메모리 효율을 더 높여줄 수 있다.

2. 라운드 트립(Round-trip이 적다)

  클라이언트와 서버와의 요청,응답의 수가 줄어든다

3. 저장 프로시저는 컴파일 된다.

 쿼리 플랜(Query Plan) 혹은 실행 계획(Execution Plan) 생성.

 저장 프로시저가 컴파일 되어서 실행 계획이 캐싱 된다는 것은 저장 프로시저를 이용하게 되는 가장 첫 번재 이유

4. 보안의 장점

  테이블이 Access를 우회 할수 있다 (INDEX,UPDATE,DELETE를 제한하고 프로시저를 통해서만 접근)

5. 저장 프로시저로 비지니스 로직을 변경할 수 있다.

6. 하나의 모듈 단위로 작성된 저장 프로시저의 경우, 다른 프로시저들에서 재사용이 가능

7. 각 기능 단위별로 최소한으로 분리시키는 것이 코드의 재사용성을 높일 수 있게 된다.

** 성능에 유익한 습관

* SET NOCOUNT ON 사용

- QUERY문 수행으로 영향 받는 행의 개수 확인이 필요하지 않는 경우

- 이 옵션 ON이어도 @@ROWCOUNT는 사용 가능

* Temporary table 대신 table 변수를 사용

* SET ROWNCOUNT 보다 TOP을 사용

* GOTO 문을 사용하지 말 것.

  - 코드 판독성 저하, 무한 루핑의 위험성

* 변수에 값 지정하기

  - SET, SELECT SET 권장

* 변수 선언 길이, 데이터 타입 일치  

* UPDATE문에서 지역 변수에 값 지정하기

  - 컬럼의 값을 갱신하고 동시에 읽고자 할 때, 여러 사용자가 동시에 작업함으로

    인해 발생 할 수 있는 concurrency 이슈를 예방 가능

    

* 결과 행의 개수 제한하기

  SET ROWCOUNT N , TOP N , 커서  TOP 사용을 권장

  

Stored Procedure 의 장점 

(실행 계획) Execution Plan의 재 사용

Business rule과 policy의 encapsulation

Application 모듈화

Application간 로직 공유

Ojbect들에 대한 보다 안전한 액세스

Network bandwidth 절약

시스템 시작 시 자동 수행 가능

-----------------------------------------------------------------

stored Procedure 작성 시 권장 사항

Comment header 작성 요망

  - 작성자, 작성일, 용도, 변경 이력 등

  - CREATE PROC 문 바로 앞 또는 바로 뒤

SP 소스 시작 부분에 옵션 설정

  - SET NOCOUNT ON

  - SET LOCK_TIMEOUT 60000

  - SET XACT_ABORT ON

Parameter에 대해 디폴트 값 지정

  - NULL 또는 상수(예:'%')

Parameter의 값이 valid한지 확인

  - SP 시작 부분에서 확인

Parameter/변수와, 관련 컬럼의 데이터 타입 일치

SP내에서 변수의 사용 최소화

Parameter를 이름으로 참조

  -SP의 Parameter 순서와 달라도 무방

  - EXEC dbo.sp_who 'sa' (x)

  - EXEC dbo.sp_show @loginame='sa' (O)

SP내에서 Transaction 처리 점검

  - SP 시작, 종료 시점의 @@TRANCOUNT 비교 문제가 있으면 ROLLBACK

----------------------------------------------------------------------

CREATE PROC 수행 시 권장 사항

ANSI_NULLS, QUOTED_IDENTIFIER 옵션 설정

  - CREATE PROC 수행 전에 해당 세션에서

  - SP 생성 시점에 정해지는 옵션들

  - SP 내에서 지정해도 무용지물

  - Sysobjects - status

  

  

----------------------------------------------------------------------

SP 호출 시 권장 사항

  - 전체 이름을 지정

    - 성능 개선

    - 불명효성 (Ambiguity) 제거

    - SP의 owner가 아닌 사용자가 호출하는 경우 complie lock 감소

    ex) EXEC myProc -> EXEC pubs.dbo.myProc

    

    

----------------------------------------------------------------------

Cursor와 Result-Set 방식

Result-Set 방식

  - 보다 잘 수행되며

  - 읽기 쉽고

  - 유지 보수가 용이하며

  - 관계형 모델과 보다 잘 조화됨

----------------------------------------------------------------------

Trigger 관련 특수 테이블

- 특수 테이블 2개

  - inserted: INSERT, UPDATE

  - deleted : DELETE, UPDATE

- 임시 메모리 상주 테이블

- 테이블에서 직접 데이터 변경 불가

Trigger 작성 시 권장 사항

  - 한번에 다중행 변경 여부 확인

    - Trigger 관련 특수 테이블 데이터를 변수에 저장하면 다중 행 중 마지막 행만 저장 됨

  - 테이블의 행 변경 여부 확인

    - @@ROWCOUNT 값 확인

  - 실제로 데이터가 변경되었는지 확인

    - UPDATE(), COLUMNS_UPDATED() 함수 사용

  - TRIGGER내에서 사용자 입력 또는 사용자 이벤트 기다리지 말 것

  - TRIGGER내의 로직을 최소화

  - PRINT, SELECT 문 사용하지 말것

  - Constraint로 구현 가능한 경우 Trigger 사용 말 것

  

----------------------------------------------------------------------

** 시스템 저장 프로시저

* SP_HELP TABLE명

  - DB의 모든 오브젝트의 형식과 그 정보를 출력하는 프로시저

* SP_DATBASES

  - SQL-SERVER의 데이터베이스를 나열한다. (DATABASE_NAME, DATABASE_SIZE(KB))

* SP_FKEYS TABLE명

  - 이 프로시저를 실행하면 논리적 외래 키 정보를 반영한다.

* SP_HELPTEXT SP명

  - SAMPLE VIEW, STORED PROCEDURE등의 소스를 보고 싶을 때 사용 한다.

* SP_DEPENDS

  - 어떤 특정 테이블이나 뷰 또는 저장 프로시저와 연관된 혹은 종속된 객체를 알고 싶을때 사용

* SP_LOCK

  - 잠금 정보를 알아내는 프로시죠

* SP_WHO

  - 현재 데이터베이스 서버에 접속된 애플리케이션 및 사용자들을 찾안래 때 유용한 도구

----------------------------------------------------------------------

** 프로시저가 (자동) 재 컴파일 되는 경우

- ALTER TABLE/ ALTER VIEW 와 같이 대상 개체의 구조가 변경된 경우

- UPDATE STATISTICS 혹은 자동으로라도 개체의 통계 정보가 변경된 경우

- DROP INDEX로 실행 계획에서 참조된 인덱스가 삭제된 경우

- 대량의 키 값 변동이 발생한 경우

- SET 옵션(CONCAT_NULL_YIELDS ANSI_PADDING, DATEFORMAT, ANSI_NULLS ..)의 변경이 발생한 경우

- SP_RECOMPILE, WITH RECOMPILE 옵션으로 수행된 경우

- SP_EXECUTESQL 명령으로 수행된 경우

- 소유자 명을 생략한 프로시저 호출의 경우

  -- 실행 계획을 찾고자 하지만 동일한 프로시저명으로 인해 작업이 실패 한다. 저장 프로시저가

      최초로 실행되는지 인식하고 컴파일을 시도하려고 컴파일 락을 걸고 시작하지만 곧 재 컴파일이 

      필요없다고 인식하여 작업을 중지 한다. (문제 컴파일 락)

  -- 해결: EXEC dbo.some_sp -> EXEC Pubs.dbo.some_sp

- 프로시저 내에서 임시 테이블을 사용하는 경우

  -- TempDb에 생성되는 것으로 영속적인 데이터베이스 개체가 아니라 세션 동안만 운영되는 것.

  -- 동일한 프로시저에서 생성한 임시 테이블에 접근하는 경우에는 프로시저는 재 컴파일 되지 않는다.

  -- 중첩 프로시저의 경우 재 컴파일 작업한다. (다른 SP Call)

  

-----------------------------------------------------------------------------------

  -----------------------------------------------------------------------------------

  

* 오류처리

  반드시 오류 여부를 확인하는 코드를 작성한다.

  절대 잡을 수 있는 오류를 놓치지 않는다.

  쿼리문장 수행마다 @@ERROR를 확인하여 에러처리 필요 

  @@ERROR은 반드시 LOCAL변수(@V_ERR)에 저장하여 처리

  RAISERROR 사용자 메세지는 50001 이상이어야 함.

  심각도는 18까지 지정가능하며, 19 ~ 25까지는 sa만이 구성 가능하고, 'WITH LOG' 옵션 추가 필요 

  20이상인 경우는 심각한 오류로 client app가 종료됨.

---- Ex-------------

SELECT @V_ERR = @@ERROR 

IF(@V_ERR<>0)

BEGIN   

RAISERROR(' 검색에 실패했습니다.', 16,1)  

SET TRANSACTION ISOLATION LEVEL READ COMMITTED  

RETURN @V_ERR  

END

-----------------------

실행 계획을 재사용하여 문제가 발생되는 경우, WITH RECOMPILE Option으로 생성

  입력 변수 대역폭의 변화가 심한 경우

  데이터의 분포가 불규칙한 경우