RE: AD Hop Query 사용 금지하고 Stored Procure로 개발하기 ..

SP(Stored Procedure)에 대한 내용만 발췌 했습니다. ( 출처-SQL Server for Developer:개발자를 위한 튜닝 가이드 ) 대부분 상식적으로 알고 있는 내용 입니다. SP 자체는 사용 하는것이 맞으나 우리회사 개발자로서 SP를 사용하는 환경은 그리 좋지않은 않습니다. (create, alter, drop 못합니다.)

참고하시기 바랍니다.

 

수칙6.저장 프로시저를 사용하는가?

저장 프로시저는 복잡한 SQL문을 단순화 시켜주고, 보안 문제를 해결해주며 더 나아가 빠른 성능에 매개변수,출력매개변수,리턴 값을 사용할 수 있습니다.

저장 프로시저의 역할 7가지

1. 데이터 무결성의 시행
2. 복잡한 비즈니스 규칙과 제약의 강화
3. 캡슐형 설계
4. 유지보수
5. 네트워크 트래픽 감소(오고 가는 긴SQL 구문을 축소)
6. 보다 빠른 실행(컴파일을 하지 않습니다)
7. 보안강화

저장 프로시저의 생성과 반복사용 시 발생하는 일

제작

1. 구문분석 2. 표준화 3. 보안 점검(프로시저 생성권한) 4. 저장(syscomments)

첫 번째 실행 시

1. 보안 점검(프로시저 실행 권한) 2. 최적화 3. 컴파일과 이에 따른 실행계획을 캐쉬에 저장 4. 실행

반복해서 실행 시

1. 캐쉬에 실행 계획 있을 때는 그대로 실행 2. 캐쉬에 실행 계획이 없을 때는 첫 번째로 저장 프로시저 실행하는 것과 동일

쿼리는 한번만 실행할 때는 일반 SQL이 훨씬 간단합니다. 그러나 반복적으로 실행되면 저장 프로시저가 월등히 빠르고 편리합니다.

 

 

수칙7.저장 프로시저를 적절하게 리컴 파일 하는가?

데이터가 변화하면(인덱스를 추가하거나 인덱스된 열의 데이터를 변경하는 등의 작업 수행 시) 그에 걸맞게 실행계획도 변화해 갑니다. 그에 대처하기 위해서 다음과 같은 리컴파일 방법를 제공합니다.

저장 프로시저 리컴 파일 모드에는 다음의 3가지가 있습니다.

CREATE PROCEDURE [WITH RECOMPILE] EXECUTE [WITH RECOMPILE] sp_recompile 

CREATE PROCEDURE [WITH RECOMPILE]

는 SQL SERVER가 이 저장 프로시저의 계획을 캐시하지 않기 때문에 이 저장 프로시저가 실행 할 때 마다 다시 컴파일 됩니다(실행 속도가 느려짐).

EXECUTE [WITH RECOMPILE]

는 지금 이순간만 리컴파일 하고 다시 저장 프로시저 실행하면 예전 실행 계획대로 작동하는 것입니다. 제공하는 매개 변수가 불규칙하거나 저장 프로시저를 만든 다음 데이터가 많이 변경되었을 경우 이 옵션을 사용합니다.

sp_recompile

는 저장 프로시저가 다음에 실행될 때 첫 실행처럼 컴파일되고 실행되도록 하는 것입니다.

[문서화되지 않은 DBCC 명령어]

-- pubs 데이터베이스의 모든 저장 프로시저를 재컴파일 해보자 select db_id('pubs') dbcc flushprocindb(5) -- 모든 인덱스를 재구축한다 -- 관리자가 사용할 경우 엄청난 시간이 소요될 수 있습니다 dbcc dbreindexall('pubs') 

 

 

수칙8. 저장 프로시저 작명 시 SP외의 접두어를 사용한다.

시스템 저장 프로시저는 master 데이터베이스내에서 sp_라는 접두어로 시작하는 것이 좋으며 모든 데이터베이스에서 실행될 수 있습니다. 각 사용자 데이터베이스에서는 다른 접두어를 사용하는 것이 보기에도 좋고 알아보기에도 수월합니다.

또한 시스템 저장 프로시저는 어느 데이터베이스에서 수행하건 해당 데이터베이스의 내용을 참조합니다.

[따라하기]

일반sp_ 저장 프로시저를 시스템sp_ 저장 프로시저로 만들어 봅니다.

1. 사용자 정의 저장 프로시저는 master데이터베이스에 존재하더라도 master내용만 참고합니다.

[08-01]

2.그러나 다음과 같이 시스템 저장 프로시저화 한다면

[08-02]

3.부연하자면 모든 데이터베이스에서 사용하는 프로시저의 경우 sp_로 시작하게 작성한후 sp_MS_marksystemobject로 시스템 프로 시저화 작업을 하는게 필요합니다. 이 내용은 엄격하게 구분되서 실행되는 것이 혼란을 줄일 수 있습니다.

 

 

수칙12.실무 사례: 저장 프로시저 관리 방법

* 저장 프로시저 관리방법

[12-01]

---------------------------
-- 객체이름 :
-- 파라미터 :
-- 제작자 :
-- 이메일 :
-- 버젼 :
-- 제작일 :
-- 변경일 :
-- 그외 :
---------------------------

use 데이터베이스명
-- 저장 프로시저는 use 데이터베이스명 문과 같이 써두어야 어디 소속인지 명확히 파악이 가능합니다.
go

-- 소스

create proc dbo.저장 프로시저명
-- 소유자가 명확하게 dbo로 지정 되 있어야 성능 향상이 이뤄집니다. 컴파일 잠금 시간 대폭 감소
as
begin
-- 가장 바깥쪽의 begin end 및 불필요한 begin end문은 과감히 생략합니다.(소스만 길어짐)

end

-- 실행예제

exec 데이터베이스명.dbo.저장 프로시저명
-- 데이터베이스 이름까지 명시해야 오브젝트 참조에서 발생할 수 있는 불 명확성을 줄여줌으로 바람직합니다.

 

 

 - 참고 URL -

 

아.. 왜 SP(Stored Procedure)을 쓰는거냐고

http://zeous.egloos.com/2135418

 

 

---

보낸사람:김성규님
보낸시간:2010년 01월 12일 오전 10:19
받는사람:RnD파트 [CJ인터넷]; redoffman.labmarble@blogger.com
제목:AD Hop Query 사용 금지하고 Stored Procure로 개발하기 ..

 

 

 

AD Hop Query 사용 금지하고 Stored Procure로 개발하기 ..

 

 

개인적으로는 직접적으로 Query를 사용하는 것을 권장합니다.

 

인덱스 문제나 기본적인 운영이슈가 있기는 하지만

 

기본적인 Query 사용을 하지 못한다는 것은 문제가 될 듯 하네요.

 

 

SP의 장점도 있기는 하지만 현재 개발 방식으로 위와 같은 작업을 하는 것은

 

문제가 될듯하네요..

 

 

 

 

 

이슈적인 문제에 대한 토론을 위해서 팀블로그(labmarble@blogger.com)을 개설해 보았습니다.

 

개발관련해서 업무에 직접적인 부분만 아니면 서로의 의견을 공유하는 자리를 만들었으면합니다.

 

이런 저런 업무 관련해서 단편적인 의견을 공유 하도록 합니다.

 

RSS피드 사용을 위해서 일단 권한도 제거 했습니다. atom 피드로 추후 바꿀 생각입니다.

 

 

참고로 참조로 redoffman.labmarble@blogger.com 로 메일 보내시면 자동적으로 블로그에 포스팅 됩니다.