.NET 개발자를 위한 저장 프로시저 평가

Christa Carpentiere
Microsoft Corporation

2004년 3월

적용 대상 :
   Microsoft® SQL Server™
   Microsoft Visual Studio® .NET 2003
   Transact-SQL (T-SQL) Language

요약: 이 문서는 주로 .NET 프로그램 언어를 사용하는 개발자를 대상으로 Microsoft SQL Server 저장 프로시저에 대해 설명합니다. 저장 프로시저의 장점과 단점, 저장 프로시저 사용시에 도움이 되는 Visual Studio .NET 2003의 도구 및 초보자에 유용한 Best Practice 를 설명합니다.

목차

시작
저장 프로시저의 사용을 검토해야 할 이유
Visual Studio .NET 도구들
초보자를 위한 정보
마무리

시작

이 문서의 목적은 응용 프로그램에 필요한 Transact-SQL (T-SQL) 캡슐화를 위해 저장 프로시저 사용의 장점과 단점에 대해 설명합니다. 현재 환경에서 저장 프로시저를 사용하는 것이 좋은지에 대해 적절한 정보에 근거한 판단을 할 수 있습니다. 또 이 문서에서는 .NET 응용 프로그램 내 저장 프로시저를 사용하기 위한 도구와 Best Practice도 설명합니다.

저장 프로시저는「비즈니스 로직은 중간층 또는 데이터베이스 내에서만 코딩 해야 한다」, 「인 라인 쿼리를 사용하는 것이 제일이다」, 「반드시 저장 프로시저를 사용해야 한다」등의 다양한 의견이 있지만, 모든 방법에는 장점과 단점이 있습니다. 중요한 것은 사용하는 응용 프로그램과 환경에서 무엇을 중시해야 하는지 검토해야 합니다. 이 문서에서는 저장 프로시저에 대해 정확히 설명하고 T-SQL 코드 캡슐화를 위해 저장 프로시저를 사용하는 것이 좋은 이유를 설명합니다.

저장 프로시저 사용의 검토 이유

여러분은 SqlCommand 객체에 작업하기 위해 T-SQL 구문을 사용한 적이 있었을 것입니다. 다만 T-SQL 구문을 데이터 액세스 코드 이외에 배치하는 것은 검토 하지 않고, 응용 프로그램 안에 복잡한 T-SQL 프로시저를 코딩 합니다. 왜냐면 장기간에 걸쳐 응용 프로그램에 다양한 기능을 이어져 왔습니다. 이러한 경우, 저장 프로시저를 사용하면 T-SQL 구문을 응용 프로그램 이외의 장소에 배치하여 캡슐화할 수 있습니다.

대부분의 개발자는 저장 프로시저에 대해 어느 정도의 지식을 가지고 있을 것입니다. 다만, 저장 프로시저에 관한 지식이 없는 개발자가 보면 저장 프로시저는 한 묶음의 코드로서 데이터와 함께 데이터베이스에 보존되는 T-SQL 구문의 집합입니다. 개발자는 입력 매개 변수를 사용해 저장 프로시저에 런타임 정보를 건네주어, 실행 결과 데이터를 결과 세트 또는 출력 매개 변수로서 받을 수 있습니다. 저장 프로시저는 처음 실행 시 컴파일 됩니다. 이렇게 하여 실행 계획이 생성됩니다. 실행 계획이란 저장 프로시저내의 T-SQL 구문에 의해서 지정된 결과를 얻기 위해, Microsoft SQL Server 에 실행해야 할 순서를 기록한 것입니다. 실행 계획은 다시 이용될 것을 고려하여, 캐시에 보관됩니다. 이렇게 하여 저장 프로시저의 성능이 향상됩니다. 즉SQL Server 에서 코드를 다시 분석할 필요가 없고, 캐시 내의 실행 계획을 참조하면 됩니다. 이 캐시내의 실행 계획은 SQL Server가 재실행되면 이용할 수 없게 되고, 사용 빈도가 낮아서 메모리에서 삭제되어도 이용할 수 없습니다.

성능

SQL Server의 이전 두 개의 버전에서는 실행 계획을 캐시에 보관함으로써, 쿼리 실행 시에 저장 프로시저의 성능을 향상시켰습니다. 다만, SQL Server의 이전 버전부터는 T-SQL 배치가 저장 프로시저 내에 있는지 관계없이, 모든 T-SQL 배치 시에 실행 계획이 캐시에 보관되게 되었습니다. 따라서, 이 기능에 의한 성능 향상 효과는 더 이상 저장 프로시저 고유의 장점이 아닙니다. 정적인 구문은 T-SQL 배치의 발행 빈도가 높고, 실행 계획이 메모리에서 삭제되지 않는 경우, 성능적인 면에서 저장 프로시저와 같은 효과가 있습니다. 변경 할 경우 주석 추가와 같은 사소한 변경도 캐시 실행 계획에 일치하지 않도록 하여, 실행 계획을 재이용할 수 없도록 합니다.

저장 프로시저에는 성능상의 장점이 또 있습니다. 저장 프로시저를 사용하여, 네트워크 교신양을 줄일 수 있습니다. 즉, 네트워크상에서 T-SQL 루틴 전체를 보낼 필요가 없고, EXECUTE stored_proc_name 구문만 보내면 됩니다. T-SQL 루틴이 복잡한 처리인 경우, 상당한 양이 될 수 있습니다. 저장 프로시저가 적절히 설계되고 있는 경우, 클라이언트와 서버의 사이의 교신을 한번의 호출로 가능하게 합니다.

또, 저장 프로시저를 사용하는 것이 실행 계획의 재사용률이 높아져 성능이 향상합니다. 구체적으로는 원격 프로시저 호출 (RPC)을 사용하고, 서버상의 저장 프로시저를 처리합니다. StoredProcedure의 SqlCommand.CommandType를 사용했을 경우, 저장 프로시저는 RPC을 통해 실행됩니다. RPC는 매개 변수를 마샬링하고, 서버 측에 있는 프로시저를 호출합니다. 이 때문에 데이터베이스 엔진에서는 대응하는 실행 계획을 간단하게 검색하여, 갱신된 매개 변수 값을 포함할 수 있습니다.

성능 향상을 목적으로 저장 프로시저의 사용을 검토할 때, 마지막으로 유의해야 할 점은, T-SQL 의 장점을 활용하고 있는지 입니다. 데이터베이스 내의 데이터를 사용해 무엇을 하고 싶은 것인지 생각해보세요.

• 세트 기반의 처리를 사용하고 있는지 또는 T-SQL 에서 충분히 지원되고 있는 다른 처리를 할 수 있는지. 이러한 경우, 저장 프로시저를 사용할 수도 있습니다만, 인 라인 쿼리에서도 같은 처리를 실행할 수 있습니다.
• 행 기반의 처리나 복잡한 문자열 조작을 실행하려고 합니까? 저장 프로시저를 사용하지 않는 T-SQL 에서 실행하는 것은 SQL Server 2005 출시 후 CLR(Common Language Runtime)이 사용 가능하게 된 후 다시 생각하는 편이 좋을 것입니다.

유지 보수성(Maintainability)과 추상화(and Abstraction)

두 번째 검토해야 할 저장 프로시저의 장점은 유지보수적인 측면입니다. 데이터 베이스 스키마와 비즈니스 규칙은 일절 변경되지 않는 것이 이상적이지만, 현실적으로는 변경이 발생합니다. 예를 들어, 새로운 판매 활동을 지원 목적으로 추가된 X, Y, Z 의 각 테이블 데이터를 처리 대상으로 포함하도록 저장 프로시저를 수정하는 것이 응용 프로그램 코드내의 정보를 수정하는 것보다 간단할 것입니다. 저장 프로시저를 수정할 경우, 그 수정 내용은 응용 프로그램에는 Transparent합니다. 즉, 저장 프로시저의 내부 구현을 변경해도 같은 판매 정보를 반환합니다. 일반적으로 저장 프로시저를 변경하는 방법이, 어셈블리 수정, 테스트, 다시 배포 방법보다는 시간과 노력을 절약할 수 있습니다.

또, 구현을 추상화하여 코드를 저장 프로시저 내에서 보관하면, 데이터에 접근할 필요가 있는 응용 프로그램은 항상 같은 방법으로 접근할 수 있습니다. 개발자는 여러 장소에 산재된 같은 코드를 유지 보수하는 노력을 없앨 수 있습니다. 한편 사용자는 일관성이 있는 정보를 얻을 수 있습니다.

저장 프로시저 안에 T-SQL 구문을 삽입했을 경우, 보수성에 관한 한가지 장점이 있습니다. 바로 버전 관리가 용이하다는 것입니다. 즉, 저장 프로시저의 생성이나 수정을 하는 스크립트를 다른 소스 코드 모듈과 같이 버전 관리할 수 있습니다. 예를 들어 Microsoft Visual SourceSafe® 등의 소스 관리 도구를 사용하여 저장 프로시저를 옛날 버전으로 되돌리거나 옛날 버전을 참조하는 것이 간단합니다.

보수성을 향상시키기 위해서 저장 프로시저를 사용할 때 유의할 점이 두 가지 있습니다.

첫 번째는 저장 프로시저를 사용해도 스키마나 비즈니스 규칙을 변경할 필요가 없어지는 것은 아닙니다. 변경량이 너무 많거나, 저장 프로시저의 입력 매개 변수 또는 저장 프로시저에서 반환되는 데이터로 변경해야 하는 경우는 어셈블리 내에서 매개 변수를 추가하거나 GetValue() 호출을 수정할 필요가 있습니다.

두번째 유의점은 저장 프로시저를 사용해 비즈니스 로직을 캡슐화하면, 응용 프로그램의 이식성이 저하되는 것입니다. 즉, 그 응용 프로그램에서는 SQL Server 데이터베이스 밖에 이용할 수 없게 됩니다. 응용 프로그램의 이식성이 중시되는 환경의 경우, RDBMS 에 의존하지 않는 중간층으로 비즈니스 로직을 캡슐화하는 편이 좋은 일도 있습니다.

보안

저장 프로시저의 사용을 검토하는 세 번째 이유는, 저장 프로시저를 사용하여 보안을 강화할 수 있는 것입니다.

정보에의 사용자 액세스를 제어하는 경우, 저장 프로시저에 대해 액세스 허가하도록 설정하여, 특정의 데이터에의 액세스를 제어할 수 있습니다. SQL Server 뷰를 잘 알고 있다면, 저장 프로시저가 SQL Server 의 뷰를 닮았다는 것을 알 수 있습니다. 다만, 저장 프로시저는 사용자로부터의 입력을 받아들여 표시되는 데이터를 동적으로 변경합니다.

저장 프로시저는 코드의 보안을 강화하는데도 도움이 됩니다. 저장 프로시저를 사용하면 특정 종류의 SQL 인젝션 공격 (특히, AND 나 OR 등의 연산자를 사용해 유효한 입력 매개 변수 값에 명령을 추가하는 공격)을 저지할 수 있습니다. 또, 응용 프로그램이 손상 받을 가능성이 있을 때는 비즈니스 규칙의 구현을 숨길 수 있습니다. 이것은 비즈니스 규칙을 지적 재산이라고 생각하는 기업에 있어서 중요합니다.

또, 저장 프로시저를 사용하면 ADO.NET에 준비된 SqlParameter 클래스를 이용할 수 있습니다. 이 클래스는 저장 프로시저의 매개 변수의 데이터 유형을 지정하는 것입니다. SqlParameter 클래스를 사용하면 사용자가 지정한 값의 데이터 유형이 올바른지 간단하게 검증할 수 있으므로, 강화된 보안 보호를 실시할 수 있습니다.

보안을 강화하는 목적으로 저장 프로시저를 사용할 때의 주의점은, 실제의 보안 대책이나 코딩이 미비하면 공격에 대해 무방비 상태가 됩니다. " 저장 프로시저를 사용하면 사실상 모든 SQL 인젝션 공격 (예: 입력 매개 변수에 데이터 조작 언어 (DML: Data Manipulation Language) 를 추가하는 공격을 저지할 수 있다" 로 결정했을 경우도 같은 위험이 있습니다.

또 T-SQL이 응용 프로그램 코드 또는 저장 프로시저 내에 있는 지와 관계없이, 데이터 유형 검사에 매개 변수사용이 안전하다고 말할 수 없습니다. 따라서, 사용자가 지정한 데이터 (특히 텍스트 데이터)를 데이터베이스에 전달하기 전에, 한층 더 추가 검사를 실시해야 합니다.

저장 프로시저 사용의 타당성

대부분의 경우, 저장 프로시저를 사용하는 편이 효과적입니다. 지금까지 설명한 저장 프로시저의 장점을 다음과 같습니다.

• 네트워크 교신량이 줄어 성능이 향상한다.
• 유지 보수 작업을 일원화 할 수 있다.
• 비즈니스 규칙을 추상화 해, 일관성과 보안을 확보할 수 있다.
• 특정 종류의 공격에 의한 피해를 최소한으로 억제할 수 있으므로 보안이 강화된다.
• 실행 계획의 재이용이 촉진된다.

여러분의 환경에 이러한 저장 프로시저의 장점을 살릴 수 있는 경우, 저장 프로시저를 사용하는 것을 추천합니다. 저장 프로시저는 사용자 환경에 있어서의 데이터 처리 방법을 개선하기 위한 뛰어난 툴입니다. 한편, 응용 프로그램의 이식성, T-SQL 적합하지 않은 프로세스, 데이터베이스 스키마의 변경 빈도가 높아 저장 프로시저의 장점을 누릴 수 없는 경우는 다른 방법을 검토하는 편이 좋을 것입니다.

검토해야 할 점이 하나 더 있습니다. T-SQL 에 숙련된 전문가 사내에 어느 정도 있는가 하는 것입니다. T-SQL 에 대해 어느 정도의 지식을 가지고 있는지, 또는 T-SQL을 습득할 의욕이 있는지. T-SQL에 대한 잘 알고 있을 수록, 저장 프로시저의 사용 효과가 높아져, 저장 프로시저의 보수 작업으로 고민하는 것이 적어집니다. 예를 들어 T-SQL 하행 베이스 처리보다 세트 베이스 처리를 중시하고 있습니다. 데이터 세트를 파악할 수 있다고 하는 이유로 커서에 의지하면 성능이 저하합니다. T-SQL 에 대한 지식이 없는 분은 이것을 기회로 T-SQL을 공부해보세요. 지식이 늘어나면 코드의 보관 장소에 관계없이 코드를 개량할 수 있습니다.

따라서, 저장 프로시저가 응용 프로그램에 특별한 효과를 있다고 생각된다면 이 문서를 읽고 진행해 주세요. 저장 프로시저를 간단하게 사용할 수 있는 툴 및 저장 프로시저 작성에 관한 성공 사례에 대해 설명합니다.

Visual Studio .NET 툴

Microsoft Visual Studio .NET는 SQL Server 저장 프로시저 (및 그 외의 데이터베이스 오브젝트)를 표시 및 조작하기 위한 몇 가지 툴이 있습니다. 어떠한 기능이 있는지를 간단하게 보고 갑시다.

저장 프로시저 표시

서버 탐색기에서는 기존의 저장 프로시저, 저장 프로시저에 필요한 매개 변수, 및 저장 프로시저의 구현을 표시할 수 있습니다. SQL Server 이 인스톨 되고 있는 서버에 접속하고 있는 경우," 서버명","SQL Server", "SQL 인스턴스명","Northwind"," 저장 프로시저"를 배포해 "CustOrdersDetail" 를 배포할 수 있습니다. 또 저장 프로시저에 필요한 매개 변수, 및 저장 프로시저가 돌려주는 열이 표시됩니다. 이러한 매개 변수나 열의 속성을 조사할 때, 데이터 유형이 ADO 형태로서 표현되는 것에 주의해 주세요. .NET Framework 의 문서에는 ADO의 데이터 유형과 .NET 의 데이터 유형 (영문)의 대응부가 기재되어 편리합니다. 물론 ADO.NET 코드내에서 매개 변수를 사용할 때, 그 데이터 유형을 SqlDbType 열거형의 멤버로 표현해도 괜찮습니다. .NET 데이터 유형과 SqlDbType 데이터 유형 (영문)의 사이의 매핑도 확인할 수 있습니다.

저장 프로시저를 더블 클릭 하면, SQL 편집기가 열려, 그 저장 프로시저가 표시됩니다. 저장 프로시저는 분류 등을 사용해 알기 쉽게 표시됩니다. 이 때, 데이터베이스 내에서 실제로 사용되고 있는 CREATE PROCEDURE 구문이 아닌, ALTER PROCEDURE 구문이 표시됩니다. 이것은 사용자는 기존의 저장 프로시저를 수정하려 한다고 보기 때문입니다.

저장 프로시저 작성 & 수정

데이터베이스 내에 저장 프로시저를 작성하는 권한이 부여되어 있습니까? 권한이 없는 경우는 우선 그 권한을 취득해야 합니다. 지원이 필요한 경우는 데이터베이스 관리자에게 상담해 주세요.

저장 프로시저를 신규에 작성하려면, 서버 탐색기로 [저장 프로시저] 노드 또는 임의 저장 프로시저를 오른쪽 클릭해, [새로운 저장 프로시저]를 클릭합니다. SQL 편집기 내에 새로운 윈도우가 열어 CREATE PROCEDURE 구문의 템플릿이 표시됩니다. 이 윈도우로 저장 프로시저의 본체를 입력할 수 있습니다. 저장 프로시저를 수정하려면, 우선, 저장 프로시저를 표시할 때와 같은 조작을 실행합니다. 즉, 서버 탐색기가 그 저장 프로시저를 찾아 엽니다.

저장 프로시저로 사용하는 쿼리의 작성에 도움이 필요한 경우는 저장 프로시저 윈도우를 오른쪽 클릭하여 [SQL 의 삽입]을 클릭합니다. 또 T-SQL 의 블록을 선택해 오른쪽 클릭하여 [SQL 블록의 디자인]을 클릭하는 방법도 있습니다. 두 종류의 조작의 하나를 실행하면, 쿼리 빌더 윈도우가 열립니다. 이 윈도우의 GUI 를 사용하고 T-SQL 구문을 작성 또는 수정할 수 있습니다. 작성 또는 수정이 완료되면, 그 T-SQL 구문을 잘라내 저장 프로시저에 붙일 수 있습니다.

아쉽게도 이 편집기에는 IntelliSense 기능이 없기 때문에, SQL Server Books Online을 빈번히 참조하는 경우는 미리 열어 두세요. 저장 프로시저를 보존하려고 했을 때에 구문 에러가 있는 경우, 정정이 필요한 취지의 경고가 표시됩니다. 구문 에러가 복구되지 않으면 저장 프로시저를 보존할 수 없습니다. 따라서, 저장 프로시저의 코딩을 개시할 때에는 에러를 해결해 작성 처리를 완료하기 위한 시간을 확보해 둘 필요가 있습니다. 최초의 코딩과 테스트는 SQL Server 쿼리 analyzer로 실행할 수 있습니다만, 이것에 대해서는 다른 문서로 설명합니다.

코드의 작성이 완료하면, 저장 프로시저를 테스트할 수 있습니다. 테스트하려면, 오른쪽 클릭해 [저장 프로시저의 실행]을 클릭합니다.

초보자를 위한 정보

응용 프로그램으로 사용하는 저장 프로시저를 처음으로 작성하는 분께서는 여기서 설명하는 힌트를 참고하세요. 저장 프로시저를 적절히 실행하여 다른 프로그램과 적절히 연계 동작시킬 수 있습니다.

SET NOCOUNT ON 사용

저장 프로시저를 실행하면, 그 저장 프로시저내의 각 구문에 의한 영향을 받은 행의 수가 반환됩니다. 응용 프로그램 내에서 이 행수정보를 사용할 필요가 없는 경우는, 저장 프로시저 내에서 SET NOCOUNT ON 구문을 사용하고, 행수가 돌려주어지지 않게 합니다. (행수정보를 필요로 하는 응용 프로그램은 거의 없습니다) 이렇게 하면 클라이언트와 서버의 사이를 흐르는 트래픽을 1 개 이상 줄일 수 있습니다. 트래픽이 어느 정도 줄지는 그 저장 프로시저에 포함된 행에 영향을 미치는 구문 수에 따라서 다릅니다. 이것은 별 문제는 아니라고 생각할 수도 있지만, 트래픽량이 많은 응용 프로그램의 경우, 성능이 저하될 수 있습니다.

create procedure test_MyStoredProc @param1 int
as

set nocount on

sp_ prefix를 사용하지 않는다

sp_ prefix는 시스템 저장 프로시저용으로 예약되고 있습니다. 데이터베이스 엔진에 의해서 마스터 데이터베이스내의 저장 프로시저가 검색될 때, 이 프리픽스(Prefix)를 가지는 저장 프로시저가 먼저 검색됩니다. 즉 먼저 마스터 데이터베이스가 검색된 후, 그 저장 프로시저가 실제로 존재하는 데이터베이스가 검색하여 처리 시간이 길어집니다. 또, 같은 이름의 시스템 저장 프로시저가 우연히 존재하는 경우, 사용자의 저장 프로시저는 처리되지 않습니다.

옵션 매개 변수를 가능한 한 사용하지 않는다

옵션의 매개 변수를 다수 사용하는 경우, 정말로 사용할 필요가 있을지를 꼼꼼하게 검토해 주세요. 임의의 실행에 대해서 불필요한 매개 변수 세트를 지정하면, 불필요한 처리가 실행되어 성능이 저하될 수 있습니다. 이것을 피하려면, 상정되는 매개 변수의 편성 모두에 대해 조건 코딩을 사용합니다. 다만, 조건 코딩은 시간이 걸려, 다시 코딩 에러가 발생할 수 있습니다.

가능하면 OUTPUT 매개 변수를 사용한다

OUTPUT 매개 변수를 사용해 스칼라 데이터를 돌려주도록 하면, 저장 프로시저의 실행이 조금 빨라져, 처리 성능을 조금 절약할 수 있습니다. 응용 프로그램에 대해서 단일의 값이 반환하려면, 결과 세트를 구체화하는 것이 아니라 OUTPUT 매개 변수를 사용해 보세요. 또OUTPUT 매개 변수를 사용하는 것으로써, 커서를 돌려줄 수 있습니다. 커서와 세트 베이스의 처리의 비교에 대해서는 향후 발행되는 문서로 설명합니다.

RETURN 값을 사용한다

저장 프로시저의 처리 상황 정보를 호출한 프로그램에 반환하려면, 저장 프로시저의 반환 값을 사용합니다. 개발 그룹 내에서 반환 값과 그 의미를 통일해, 개발 멤버간에 반환 값의 사용 방법이 흩어지지 않게 해 주세요. 호출한 응용 프로그램 내에서의 에러 처리가 용이하게 되고, 문제 해결에 도움이 되는 정보를 최종 사용자에 제공할 수 있습니다.

DDL를 DML 보다 먼저 처리한다

SQL Server가 저장 프로시저를 다시 컴파일 하는 것은 데이터 정의 언어 (DDL: Data Definition Language) 구문의 뒤에 DML 구문이 실행되었을 경우입니다. 이 경우 DML은 DDL 에 의해서 수정된 오브젝트를 참조합니다. 이것은 DML 에 대한 실행 계획을 작성할 때에, DDL 에 의해서 오브젝트에 추가된 변경을 고려해야 합니다. 저장 프로시저의 선두에서 모든 DDL를 처리하는 경우, 그 저장 프로시저가 다시 컴파일 되는 것은 1 회뿐입니다. DDL 구문과 DML 구문이 혼재되면, 저장 프로시저는 반복되어 컴파일 되어 성능이 저하합니다.

마무리

저장 프로시저가 얼마나 유용한지에 대해 알 수 것이라고 생각합니다. 저장 프로시저는 SQL Server 에 포함된 무료 도구입니다. 따라서, 저장 프로시저가 응용 프로그램의 개발과 유지 보수에 도움이 된다면 꼭 활용해야 합니다. 이 문서로 설명한 개요 정보를 참고로 하고, 저장 프로시저를 사용해 보세요. 한층 더 자세한 내용에 대해서는MSDN® 은 물론 SQL Server Books Online 이나 Visual Studio 관련 문서도 참조해 주세요.