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

Christa Carpentiere
Microsoft Corporation

2004년 3월

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

요약: .NET 프로그래밍 언어를 주로 사용하는 개발자를 위해 SQL Server 저장 프로시저를 소개합니다. 저장 프로시저 사용의 장단점을 비롯하여 Microsoft Visual Studio .NET 2003에서 제공하는 저장 프로시저 작업 도구의 개요를 살펴보고 몇 가지 유용한 예제를 배워 봅니다(7페이지/인쇄 페이지 기준).

목차

소개
저장 프로시저를 사용하는 이유
Visual Studio .NET 도구
시작을 위한 팁
결론

소개

이 기사는 응용 프로그램에 필요한 T-SQL(Transact-SQL) 캡슐화를 위해 저장 프로시저를 사용할 경우의 주요 장단점을 제시함으로써 사용자가 작업 환경에서 저장 프로시저 사용을 결정할 때 필요한 정보를 제공하기 위한 것입니다. 또한 저장 프로시저 사용자들을 위해 .NET 응용 프로그램에서 저장 프로시저를 사용하기 위한 도구와 활용 사례 등을 설명합니다.

물론 일부 사용자는 비즈니스 논리를 중간 계층이나 데이터베이스에서만 인코딩해야 한다고 생각할 수도 있고, 인라인 쿼리만을 사용해야 한다고 생각할 수도 있으며, 저장된 프로시저를 모든 경우에 다 사용해야 한다고 확고하게 믿고 있을 수도 있습니다. 이러한 모든 방법에는 각각 장단점이 있습니다. 중요한 점은 자신의 응용 프로그램과 환경에 중요한 사항을 고려하는 것입니다. 그러면, 저장 프로시저의 정확한 개념과 T-SQL 코드 캡슐화에 저장 프로시저를 사용하는 몇 가지 이유를 살펴보도록 하겠습니다.

저장 프로시저를 사용하는 이유

일반적으로는 SqlCommand 개체에 두루 사용할 T-SQL을 작성하게 되지만, T-SQL을 데이터 액세스 코드에 결합하여 사용하는 것보다 더 유용하게 사용할 수 있는 방법도 있습니다. 응용 프로그램은 시간이 지남에 따라 기능이 계속 추가되므로 그 내부에는 복잡한 T-SQL 프로시저가 코딩되어 있을 수 있습니다. 이 경우 저장 프로시저를 대신 사용하여 이러한 코드를 캡슐화할 수 있습니다.

저장 프로시저에 대해 어느 정도 알고 있는 사용자가 많겠지만, 모르고 있는 사용자를 위해 굳이 설명하자면 저장 프로시저는 단일 코드 단위로 데이터베이스에 함께 저장되는 T-SQL 문 그룹입니다. 입력 매개 변수를 사용하여 런타임 정보를 전달하고 데이터를 결과 집합 또는 출력 매개 변수로 다시 가져올 수 있습니다. 저장 프로시저는 처음 실행할 때 컴파일됩니다. 이때 실행 계획이 만들어집니다. 이 실행 계획은 기본적으로 T-SQL에 의해 지정된 저장 프로시저의 결과를 가져오기 위해 Microsoft SQL Server™가 따라야 하는 단계의 기록이라고 할 수 있습니다. 만들어진 실행 계획은 나중에 사용할 수 있도록 메모리에 캐시됩니다. 이 경우 SQL Server는 저장 프로시저에 대해 수행할 작업을 확인하기 위해 코드를 다시 분석할 필요가 없으며 단순히 캐시된 계획을 참조할 뿐이므로 저장 프로시저의 성능이 향상됩니다. 캐시된 계획은 SQL Server를 다시 시작하거나 일정 기간 동안 사용률이 낮아 메모리에서 삭제될 때까지 사용할 수 있습니다.

성능

캐시된 실행 계획은 쿼리에 대한 저장 프로시저의 성능을 향상시키기 위해 사용됩니다. 그러나 SQL Server의 지난 두 버전에서는 실행 계획이 저장 프로시저에 포함되어 있는지 여부에 관계없이 모든 T-SQL 일괄 작업에 대해 실행 계획이 캐시됩니다. 따라서 이 기능에 따른 성능 향상은 더 이상 저장 프로시저만의 장점이 될 수 없습니다. 정적 구문을 사용하는 모든 T-SQL 일괄 작업도 해당 실행 계획이 메모리에서 삭제되지 않을 만큼만 자주 전송된다면 성능이 동일한 수준으로 향상되기 때문입니다. 여기서는 "정적"이라는 말이 핵심입니다. 주석을 추가하는 것과 같이 사소한 작업이라고 해도 일단 내용이 변경되면 캐시된 계획과 일치하지 않기 때문에 계획을 다시 사용할 수 없게 됩니다.

그러나 네트워크 트래픽을 줄이기 위해 저장 프로시저를 사용한 경우에도 성능 향상의 효과를 얻을 수 있습니다. 전체 T-SQL 루틴을 보내는 대신 복잡한 작업에 광범위하게 포함할 수 있는 EXECUTE stored_proc_name 문만 네트워크를 통해 보내면 되기 때문입니다. 잘 만들어진 저장 프로시저는 클라이언트와 서버 간의 여러 번에 걸친 통신을 한 번의 호출로 대신할 수 있습니다.

또한 RPC(원격 프로시저 호출)를 사용하여 서버의 저장 프로시저를 처리하는 방법으로 저장 프로시저의 사용을 통해 실행 계획을 다시 사용할 수 있으므로 성능이 향상됩니다. StoredProcedure의 SqlCommand.CommandType을 사용하면 RPC를 통해 저장 프로시저가 실행됩니다. RPC가 매개 변수를 마샬링하고 서버 쪽의 프로시저를 호출하는 방식은 엔진이 일치하는 실행 계획을 쉽게 찾아 업데이트된 매개 변수 값을 넣기만 하면 되도록 합니다.

성능 향상을 위해 저장 프로시저의 사용을 고려할 때 염두에 두어야 할 마지막 사항은 T-SQL 기능 활용 여부입니다. 데이터로 수행하려는 작업을 고려해 보십시오.

• 세트 기반 작업을 사용하거나 T-SQL에서 완벽하게 지원되는 기타 작업을 수행한다면 인라인 쿼리를 사용해도 되지만 저장 프로시저를 사용할 수도 있습니다.
• 행 기반 작업 또는 복잡한 문자열 조작을 수행하려는 경우에는 T-SQL에서 이러한 처리 작업을 수행하지 않는 것이 좋습니다. Yukon이 출시되고 CLR(공용 언어 런타임)이 통합되기 전까지는 T-SQL에서 저장 프로시저를 사용할 수 없기 때문입니다.

관리 용이성 및 절차 단순화

저장 프로시저가 제공할 수 있는 두 번째 장점은 관리의 용이성입니다. 이론적으로는 데이터베이스 스키마가 전혀 변경되지 않고 비즈니스 규칙이 수정되는 일도 없는 것이 좋겠지만, 실제로는 이러한 일들이 발생할 수밖에 없습니다. 그러므로 이 경우 응용 프로그램 코드에서 정보를 변경하는 대신 새로운 영업 정책을 지원하기 위해 추가된 새 X, Y 및 Z 테이블의 데이터를 포함하도록 저장된 프로시저를 수정하는 것이 더 쉬울 수 있습니다. 저장 프로시저에서 정보를 변경하면 저장 프로시저의 내부적인 구현이 변경되더라도 반환되는 판매 정보는 동일하므로 응용 프로그램에 대해 업데이트가 투명해집니다. 일반적으로 어셈블리를 변경, 테스트 및 재배포하는 것에 비해 저장 프로시저를 업데이트하는 것이 시간과 노력이 적게 듭니다.

또한 구현을 단순화하고 이 코드를 저장 프로시저에 유지함으로써 응용 프로그램이 일관성 있게 데이터에 액세스하도록 할 수 있습니다. 여러 곳에서 코드를 일정하게 유지하지 않더라도 사용자는 일관성 있는 정보를 볼 수 있습니다.

또한, T-SQL을 저장 프로시저에 저장하면 더욱 효과적으로 버전을 제어할 수 있다는 또 다른 관리상의 이점도 있습니다. 다른 소스 코드 모듈과 마찬가지로 저장 프로시저를 만들고 수정하는 스크립트의 버전을 제어할 수 있습니다. Microsoft Visual SourceSafe 또는 기타 소스 제어 도구를 사용하여 이전 버전의 저장 프로시저로 되돌리거나 참조할 수 있습니다.

관리의 용이성을 향상시키기 위해 저장 프로시저를 사용할 때 한 가지 주의해야 할 점은 스키마와 규칙이 변경될 수도 있다는 것입니다. 저장 프로시저에 입력된 매개 변수나 반환되는 데이터를 변경해야 할 만큼 많은 부분이 변경되는 경우에는 어셈블리의 코드를 업데이트하여 매개 변수를 추가하고 GetValue() 호출을 업데이트하는 등의 작업을 수행해야 합니다.

저장 프로시저를 사용하여 비즈니스 논리를 캡슐화할 경우에는 SQL Server를 사용해야 하므로 응용 프로그램의 이식성이 제한된다는 문제도 고려해야 합니다. 응용 프로그램 이식성이 중요한 환경에서는 비즈니스 논리를 RDBMS 중립 중간 계층에 캡슐화하는 것이 좋습니다.

보안

저장 프로시저 사용을 고려해 볼 마지막 이유는 저장 프로시저를 통해 보안을 강화할 수 있다는 점입니다.

정보에 대한 사용자 액세스 규정이라는 측면에서, 사용자에게 기본 테이블에 대한 권한은 부여하지 않고 저장 프로시저에 대한 권한을 부여하여 특정 데이터에 액세스하도록 할 수 있습니다. 저장 프로시저는 표시되는 데이터를 동적으로 변경하기 위해 사용자 입력을 받아들인다는 점을 제외하면 SQL Server 뷰와 비슷하다고 할 수 있습니다.

저장 프로시저를 통해 코드 보안을 강화할 수도 있습니다. 저장 프로시저는 일부 SQL 주입 공격 유형, 특히 AND 또는 OR과 같은 연산자를 사용하여 명령을 유효한 입력 매개 변수 값에 추가하는 공격 유형으로부터 시스템을 보호합니다. 또한 응용 프로그램 보안이 손상되는 경우 비즈니스 규칙 구현을 숨깁니다. 이러한 종류의 정보가 지적 재산으로 간주되는 비즈니스의 경우 이는 중요한 요소입니다.

또한 저장 프로시저를 사용하면 ADO.NET의 SqlParameter 클래스를 통해 저장 프로시저 매개 변수의 데이터 형식을 지정할 수 있습니다. 이를 통해 사용자가 입력한 값의 형식에 대한 유효성 검사를 심층적인 방어 전략의 일부로 쉽게 수행할 수 있습니다. 매개 변수는 저장 프로시저에서 유효한 사용자 입력의 범위를 제한하는 데 유용한 것처럼 인라인 쿼리에서도 유용합니다.

보안을 강화하기 위해 저장 프로시저를 사용할 때 주의해야 할 점은 보안이나 코딩이 잘못되면 저장 프로시저를 사용하더라도 공격에 노출된다는 사실입니다. SQL Server 역할 만들기 및 할당 작업을 부주의하게 수행할 경우 사용자에게 허용되지 않아야 할 액세스 권한이 허용됩니다. 저장 프로시저의 사용을 통해 DML(데이터 조작 언어)이 입력 매개 변수에 추가되는 것과 같은 모든 SQL 주입 공격을 사실상 막을 수 있다고 확신하는 경우에도 공격의 대상이 될 수 있습니다.

또한 데이터 형식 유효성 검사에 매개 변수를 사용하는 것은 T-SQL이 코드에 있든 저장 프로시저에 있든 상관없이 안전하지 않습니다. 사용자가 입력한 모든 데이터, 특히 텍스트 데이터는 데이터베이스에 전달하기 전에 추가적인 유효성 검사를 받아야 합니다.

저장 프로시저의 적합성

저장 프로시저 사용이 적합한 경우도 있고 그렇지 않은 경우도 있습니다. 저장 프로시저의 장점을 다시 요약해 보면 다음과 같습니다.

• 네트워크 트래픽을 줄여 성능을 향상시킵니다.
• 단일 지점에서 유지 관리를 수행할 수 있습니다.
• 비즈니스 규칙을 단순화하여 일관성과 보안성을 높입니다.
• 일부 공격 형태를 최소화하여 보안을 강화합니다.
• 실행 계획을 다시 사용할 수 있도록 합니다.

저장 프로시저가 제공하는 위와 같은 이점을 활용할 수 있는 사용자 환경에서는 저장 프로시저를 사용할 것을 적극 권장합니다. 저장 프로시저를 사용하여 환경 내에서 데이터를 처리하는 방법을 향상시킬 수 있기 때문입니다. 반면 이식성에 문제가 있거나, T-SQL이 아닌 프로세스를 주로 사용하는 작업을 수행하거나, 데이터베이스 스키마가 불안정한 등의 문제로 인해 이러한 장점을 활용할 수 없는 경우 다른 방법을 사용하는 것이 좋습니다.

사내에 T-SQL 전문 기술을 얼마나 보유하고 있는지도 고려해야 합니다. 저장 프로시저를 활용하려면 상당량의 T-SQL 관련 지식이 필요합니다. 해당 지식이 없는 경우에는 DBA 또는 저장 프로시저 작성을 대신해 줄 사람이 있어야 합니다. 보유하고 있는 T-SQL 관련 지식이 많을수록 뛰어난 저장 프로시저를 작성할 수 있으며 관리하기도 쉽기 때문입니다. 예를 들어, T-SQL은 행 기반 작업이 아니라 세트 기반 작업에 중점을 두고 있습니다. 데이터 집합에 대한 지식이 없다는 이유로 T-SQL을 사용하지 않는다면 성능이 저하될 것입니다. T-SQL에 대해 잘 모른다면 지금부터 배워 보십시오. 지식을 어디에 보관해 두든, 해당 지식을 통해 코드가 향상될 것입니다.

저장 프로시저를 통해 응용 프로그램에 특별한 기능을 추가할 수 있다고 생각한다면 이 기사를 계속 읽어 보시기 바랍니다. 저장 프로시저 작업을 쉽게 하는 몇 가지 도구와 효과적인 저장 프로시저 작성 사례를 살펴보겠습니다.

Visual Studio .NET 도구

Microsoft Visual Studio .NET은 SQL Server 저장 프로시저와 기타 데이터베이스 개체를 보거나 조작할 수 있도록 하는 몇 가지 도구를 제공합니다. 이 도구의 기능에 대해 간단히 살펴보도록 하겠습니다.

저장 프로시저 보기

서버 탐색기를 사용하여 기존 저장 프로시저를 찾아 해당 프로시저에 필요한 매개 변수를 확인하거나 저장 프로시저의 내부 구현을 살펴볼 수 있습니다. SQL Server가 설치되어 있는 서버에 연결되어 있는 경우 ServerName, SQL Server, SQLInstanceName, Northwind, 저장 프로시저 및 CustOrdersDetail을 차례로 확장합니다. 탐색기에 저장 프로시저에 필요한 모든 매개 변수와 저장 프로시저가 반환하는 모든 열이 표시됩니다. 이들의 속성을 확인해 보면 데이터 형식이 ADO 형식으로 표현되어 있음을 알 수 있습니다. 프레임워크 문서를 통해 이 형식과 .NET 형식 을 간편하게 비교해 볼 수 있습니다. 물론 ADO.NET 코드의 매개 변수를 사용할 때는 매개 변수의 데이터 형식을 SqlDbType 열거의 멤버로 표현할 수 있습니다. .NET 형식과 SqlDbType 형식  비교를 확인할 수 있습니다.

저장 프로시저를 두 번 클릭하면 Visual Studio가 해당 저장 프로시저를 SQL 편집기에서 엽니다. 이 SQL 편집기에서 편리한 색 구분 기능 등 모든 기능을 사용할 수 있습니다. SQL 편집기는 실제로 데이터베이스에 존재하는 CREATE PROCEDURE 구문을 표시하는 대신 사용자가 저장 프로시저를 수정할 것이라고 가정하여 ALTER PROCEDURE 구문을 표시합니다.

저장 프로시저 작성 및 수정

적절한 데이터베이스에 저장 프로시저를 만들 권한이 없는 경우 저장 프로시저를 만들거나 수정하려면 먼저 권한을 받아야 합니다. 도움이 필요하면 관리자에게 문의하십시오.

서버 탐색기에서 저장 프로시저 노드 또는 저장 프로시저를 마우스 오른쪽 단추로 클릭하고 새 저장 프로시저를 선택하여 새 저장 프로시저를 만들 수 있습니다. SQL 편집기에 기본 CREATE PROCEDURE 구문이 표시된 새 창이 열립니다. 여기에 저장 프로시저의 본문을 입력하면 됩니다. 저장 프로시저 수정은 저장 프로시저를 볼 때와 같은 방법으로 시작하면 됩니다. 즉, 서버 탐색기에서 저장 프로시저를 찾아서 엽니다.

저장 프로시저에 사용할 쿼리를 만들어야 할 경우 저장 프로시저 창을 마우스 오른쪽 단추로 클릭하고 SQL 삽입을 선택합니다. 또는 T-SQL 블록을 선택한 다음 SQL 블록 디자인을 선택해도 됩니다. 그러면 그래픽 인터페이스를 통해 T-SQL 문을 작성 또는 수정할 수 있는 쿼리 작성기 창이 열립니다. 작성 또는 수정을 완료한 후 T-SQL을 복사하여 저장 프로시저에 붙여 넣습니다.

불행하게도 이 편집기에는 IntelliSense가 없기 때문에 SQL Server 온라인 설명서를 열어 놓고 자주 참조해야 합니다. 저장 프로시저를 저장하려고 하면 수정이 필요한 모든 구문에 대해 경고가 표시됩니다. 구문 오류를 수정해야 저장 프로시저를 저장할 수 있으므로 코딩을 시작하기 전에 프로시저 작업을 수행 및 완료할 수 있는 충분한 시간이 있는지를 확인하는 것이 좋습니다. SQL Server 쿼리 분석기를 사용하면 언제든지 코딩 및 테스트를 시작할 수 있지만 이는 별개의 문제입니다.

원하는 코드 작성을 완료하면 마우스 오른쪽 단추를 클릭하고 저장 프로시저 실행 옵션을 선택하여 저장 프로시저를 테스트할 수 있습니다.

시작을 위한 팁

응용 프로그램에 사용할 저장 프로시저를 만드는 데 도움이 될 수 있는 몇 가지 팁을 소개하겠습니다. 이 팁을 참고하면 제대로 실행되고 다른 구성 요소와 문제 없이 작동되는 저장 프로시저를 만들 수 있습니다.

SET NOCOUNT ON을 사용합니다.

기본적으로 저장 프로시저는 프로시저에 있는 각 문의 영향을 받는 행 수를 반환합니다. 응용 프로그램에서 이 정보를 사용할 필요가 없다면(대부분의 응용 프로그램이 이에 해당) 저장 프로시저에 SET NOCOUNT ON 문을 사용하여 이 동작을 중지시키십시오. 그러면 저장 프로시저에 포함된 문 중 행에 영향을 주는 문의 수에 따라 클라이언트와 서버 간의 왕복 횟수가 1회 이상 줄어듭니다. 이는 사소한 문제인 것 같지만, 트래픽이 많은 응용 프로그램의 성능을 저하시킬 수도 있습니다.

create procedure test_MyStoredProc @param1 int as set nocount on

sp_ prefix를 사용하지 않습니다.

sp_ prefix는 시스템 저장 프로시저에 예약되어 있습니다. 데이터베이스 엔진은 항상 master 데이터베이스에서 이 접두사가 있는 저장 프로시저를 찾습니다. 엔진이 master 데이터베이스를 먼저 검색한 다음 저장 프로시저가 있는 데이터베이스를 검색하기 때문에 처리가 완료되는 데 시간이 약간 더 걸립니다. 또한 처리하려는 저장 프로시저와 같은 이름의 시스템 저장 프로시저가 있으면 해당 프로시저는 처리되지 않습니다.

선택적 매개 변수는 꼭 필요한 경우에만 사용합니다.

선택적 매개 변수는 신중하게 사용해야 합니다. 실행에 대해 입력된 매개 변수 집합을 기준으로 할 때 필수적이지 않은 추가 작업은 성능에 쉽게 영향을 줄 수 있습니다. 모든 매개 변수 조합에 조건부 코딩을 사용하여 이 문제를 해결할 수 있지만 여기에는 시간이 많이 걸리고 오류 발생 가능성이 높아집니다.

가능하면 OUTPUT 매개 변수를 사용합니다.

OUTPUT 매개 변수를 사용하여 스칼라 데이터를 반환함으로써 속도를 다소 높이고 처리 효율성도 다소 높일 수 있습니다. 응용 프로그램에서 단일 값을 반환해야 하는 경우 결과 집합을 구성하는 대신 이 방법을 사용해 보십시오. 또한 적절한 경우에는 OUTPUT 매개 변수를 사용하여 행 기반 작업을 수행할 수도 있습니다. 행 기반 처리와 세트 기반 처리에 대한 비교 논의는 다른 기사에서 다루도록 하겠습니다.

RETURN 값을 사용합니다.

저장 프로시저의 반환 값을 사용하여 호출하는 응용 프로그램에 처리 상태 정보를 반환합니다. 개발 그룹 내에서 반환 값 집합과 그 의미를 표준화하여 일관성 있게 사용하도록 합니다. 이를 통해 호출하는 응용 프로그램에서 발생한 오류를 보다 쉽게 해결하고 최종 사용자에게 문제에 대한 유용한 정보를 제공할 수 있습니다.

모든 DDL을 먼저 실행한 후에 DML을 실행합니다.

DDL(데이터 정의 언어) 문을 실행한 후에 DML 문을 실행하면 SQL Server는 저장 프로시저를 다시 컴파일합니다. 이때 DML은 DDL에서 수정한 개체를 참조합니다. 이러한 현상은 SQL Server가 DML에 대한 계획을 만들기 위해 DDL에서 개체를 변경한 내용을 고려해야 하기 때문에 발생합니다. 따라서 저장 프로시저가 시작될 때 모든 DDL을 실행하면 한 번만 다시 컴파일하면 됩니다. DDL 문과 DML 문을 혼합하여 사용하면 저장 프로시저를 여러 번 다시 컴파일해야 하기 때문에 성능이 저하됩니다.

다른 작업에서와 마찬가지로 주석을 사용합니다.

작성한 코드를 항상 직접 유지 관리하지는 않을 것입니다. 나중에 다른 사람이 코드의 내용을 알아야 할 수도 있습니다. 이것만으로도 주석을 사용할 충분한 이유가 되는 것입니다.

결론

저장 프로시저의 유용성에 대해 알아 보았습니다. 저장 프로시저는 SQL Server와 함께 무료로 제공되는 도구이므로 응용 프로그램 개발 및 유지 관리에 도움이 된다고 판단되면 언제든지 활용할 수 있습니다. 이 기사에서 제공한 소개 정보를 바탕으로 저장 프로시저 사용을 시작해 보십시오. 저장 프로시저 사용 중의 자세한 내용은 SQL Server 온라인 설명서, Visual Studio 설명서 및 MSDN을 참조하십시오.

---

최종 수정일: 2004년 8월 3일