강좌 전 태오의 잡담>

이 강좌는 ASP나 ASP.NET을 다루려는 개발자들이 데이터베이스와 관련하여 반드시 알고있어야 할 부분중에 하나인 Stored Procedure 에 대한 이야기입니다. 사실, 이 내용은 Taeyo's Advanced ASP(정보문화사)에 존재하는 내용입니다만 여러분이 ADO.NET을 접하기 전에 먼저 알아두어야 할 것같기에 올립니다. ASP.NET 책을 준비하다보니 이 정도의 내용은 책에 쓰기 보다 강좌로 올려서 많은 분들이 보실 수 있게 하는 게 바람직해서.. 늦었지만... 이제야 올립니다 ^^

강좌 시작 >

내장 프로시져는 복잡한 SQL 문을 단순화 시켜주고, 보안적인 문제도 해결해 주는 역할을 하며, 매우 빠른 성능을 자랑한다. 이는 복잡한 쿼리들을 모아서 하나의 프로시져로 처리할 수 있으며, 그에 따른 처리결과를 돌려주는 형식을 취한다.

요즘 들어 많은 ASP 문서들에서 스토어드 프로시져의 사용을 권유하고 있다.(물론, 예전부터 그래오기는 했다) 그렇다면, 그토록 스토어드 프로시져의 사용을 권유하는 이유는 무엇일까? 이를 사용할 경우 어떠한 장점이 있기에 이를 사용하라고 그토록 이야기하는 것일까? 그 중요한 이유 중에 하나는 속도이다. 웹을 다루는 우리에게 언제나 의식할 수 밖에 없는 것이 속도라는 것은 두말할 필요가 없을 것이다. 웹 사이트가 갈수록 느려지는 이유 중에 하나는(하드웨어적으로는 문제가 없다고 가정했을 때) 사이트에 많은 데이터가 축적되어짐에 따라, 그 데이터를 처리하는 데에 드는 시간이 늘어나기 때문이다. 물론, 갈수록 많은 사용자들이 그러한 데이터의 처리를 요구하기 때문이기도 하고 말이다. 어쨋든 갈수록 방대해지는 데이터를 처리하는 데에 드는 속도는 갈수록 떨어질 것이기에, 우리는 이 부분에 민감하게 반응할 수 밖에 없다. 물론, 우리가 부유하다면 최고의 장비를 갖추어서 이 문제를 해결할 수도 있겠지만, 대부분의 경우 그럴 수가 없는 현실이다.

그리고, 개발자로서의 자존심이 그렇게 모든 것을 돈으로 해결하게 놔두지는 못할 것이기에 프로그래밍만으로 성능의 향상을 높일 수 있는 방법이 있다면 그 어떠한 시도도 지나쳐서는 안될 것이다. 웹 사이트의 속도 저하 원인중에 가장 큰 이유는 데이터의 처리속도의 저하이다. 그렇다면, 데이터의 처리속도를 조금이라도 증가시킬 수 있다면 웹 사이트의 속도도 증가할 것이라는 것은 당연하다. 바로 그러한 이유에서도 이 스토어드 프로시져는 필수적으로 사용할만한 방법인 것이다.

저장 프로시져를 사용할 경우에는 일반 SQL 문을 사용할 경우보다 뛰어난 속도의 향상을 가져올 수 있다. 다음의 표는 일반 SQL 문과 저장 프로시져간의 비교이다. 이 비교를 통해 왜 저장 프로시져가 더 빠를 수 있는지 여러분은 느낄 수 있을 것이다.

	일반 SQL 구문	저장 프로시저
만들 때	1. 키워드 분리 및 문법 검사(파싱작업) 2. 각 개체의 이름을 확인 3. 권한 및 보안의 점검 4. 옵티마이징 (최적화시킨다) 5. 컴파일의 실행	1. 키워드 분리 및 문법 검사(파싱작업) 2. 각 개체의 이름을 확인 3. 권한 및 보안의 점검 4. 옵티마이징 (최적화시킨다) 5. 결과를 서버에 저장
처음 실행 시	컴파일된 것을 실행한다.	컴파일하고, 이를 어떻게 실행할 것인지에 대한 실행계획을 만들어 캐쉬에 저장하고 난 뒤 실행한다.
이후 실행 시	위의 두 과정을 다시 또 수행	캐쉬를 확인해서 이미 실행계획이 있다면 그를 사용하며, 캐쉬에 실행계획이 없다면 위의 과정을 수행한다.

뭐가 차이가 있다는 것인지 이해가 잘 안가는가? 그렇다면 유심히 보지 않아서 그럴 것이다. 다시 한번 자세히 살펴보자. 분명한 차이가 있다. 일반 SQL 구문의 경우는 실행할 때마다 언제나 파싱작업, 개체,보안확인 작업, 컴파일 작업을 반복하게 된다. 하지만, 스토어드 프로시져는 처음에만 단 한번 그 작업을 수행하고, 그에 대한 모든 사항을 캐쉬에 올려놓게 된다. 해서 이후 실행시에는 파싱작업, 개체, 보안확인 작업, 컴파일 작업은 필요하지 않은 것이다.

웹과 같이 수많은 사용자가 같은 쿼리를 수도 없이 요청하는 경우를 생각해 보라. 매번 "파싱작업, 개체,보안확인 작업, 컴파일 작업"을 반복하는 것과, 단 한번만 이를 수행하고 이후는 캐쉬에서 가져와서 빠르게 수행하는 것과는 대단한 차이가 생길 것이다. 사이트의 규모에 따라 예상치도 않은 성능의 향상을 가져올 수도 있다.

그 뿐이 아니다. ASP에서 ADO를 통해서 데이터베이스에 명령을 날리는 경우 , 그 명령라인의 길이가 길수록 더 많은 크기의 데이터를 서버로 전송해야 하기에 네트웍의 트래픽도 늘어나게 된다. 그 명령문의 길이가 짧으면 짧을수록 트래픽은 줄어들 것이고 말이다. "에이.. 몇 글자나 된다고 그래?" 할지 모르겠지만 이것은 그냥 웃으며 넘길 정도의 문제가 아니다.

글 집필시의 최고 이슈 사이트인 모교사랑(ILoveSchool.co.kr)의 경우 동시 사용자가 5만명에 이르른다고 한다. 그렇다면 쿼리문의 한, 두글자의 차이는 5만글자, 10만글자의 차이로 늘어나게 되는 것이다. 이제도 웃을 수 있을까? 스토어드 프로시져를 사용하면 이 긴 SQL 구문이 프로시져 내부로 들어가게 되기에 이 트래픽 또한 줄일 수 있다.

이제는 맘에 들 것이며, 마구 사용하고 싶어질 것이다.

거기다가 다음과 같은 추가적인 이익도 있으니 이마저 듣고나면 스토어드 프로시져를 사용하지 않고는 못 배길 것이다. 그것은 바로 보안적인 잇점이다. 이제는 익숙해져버린 ASP Security Hole을 여러분은 알고 계실 것이다. 일명 백도어라고 불리우는 ASP 소스를 훔쳐볼 수 있는 방법말이다. 만일, 여러분이 ASP 페이지에서 일반 SQL 구문을 사용하여 페이지를 구성하였다면, 이 백도어가 오픈될 경우 이 쿼리는 모두 노출될 수 있을 것이다. 하지만, 스토어드 프로시져를 사용한다면 모든 SQL 문은 프로시져 내부로 숨어있기에, 단지 프로시져의 이름만이 노출될 뿐, 안심설계일 수 있는 것이다.

그렇다. 저장 프로시져의 사용은 여러모로 우리에게 유용하다. 그렇다면, 이런 기분좋은 느낌으로 이를 공부해보도록 하자. SQL 서버 BOOKS ONLINE(SQL 설치하면 같이 설치되는 도움말) 이나 SQL 서버책에서 이 스토어드 프로시져를 만드는 구문을 살펴보면 다음과 같을 것이다.

CREATE PROC[EDURE] procedure_name [;number]     [         {@parameter data_type} [VARYING] [= default] [OUTPUT]     ]     [,...n] [WITH     {         RECOMPILE         | ENCRYPTION         | RECOMPILE, ENCRYPTION     } ] [FOR REPLICATION] AS     sql_statement [...n]

오오... 대단히 어려워 보인다. 데이터베이스를 전문으로 하지 않는 한, 각각의 인자들이 어디에 어떻게 쓰이는지 모두 기억하고 있기는 어려울 것이다. 이 구문에 대한 자세한 사항은 안타깝게도 여기서 모두 다룰 수는 없다. 이 책은 ASP 책이며, 그 본분을 다하고 싶기 때문이다. 더욱 자세한 사항은 SQL 서버 책을 살펴보셔야 할 것이다.(BOOKS ONLINE도 좋다) 그렇기에 더욱 자세한 부분들에 대해서는 여러분이 차후 시간이 날 경우에 스스로 학습법을 통해서 습득하시기를 바라며, 우리는 이 책에서 필요한 정도의 수준만을 같이 해 보려 한다. (좀 더 중급 개발자가 되기 위해서는 SQL 서버는 아니더라도 또한, 아주 깊이 있게는 아니더라도 반드시 한 개이상의 데이터베이스를 다룰 수가 있어야 할 것이다. 알고보면 우리도 언제나 노력해야 하는 고된 직업을 가지고 있다. )

일단, 위의 구문을 따르는 가장 간단한 예문을 보면 다음과 같다.

CREATE PROC usp_UpdatePrice_Titles
AS
    Update titles SET price = price * 1.2

이렇게 바라보니 어렵지 않다. 가장 간단한 구문의 스토어드 프로시져는 다음과 같은 구성을 지니게 된다.

CREATE PROC(혹은 PROCEDURE) 프로시져 이름
AS
    수행할 SQL 쿼리문장

오오, 이제는 이해하고 싶어지려 한다. 뭐든지 쉬운 것부터 접근해 나가는 것을 필자는 좋아한다. 처음부터 너무 어렵게 시작하면 뭐든지 하기가 싫어지는 법이니 말이다. 왠지 초보취급을 하듯이 이야기하는 것 같다고 느낀다면, 필자가 처음에 스토어드 프로시져를 알기 위해서 꽤나 어렵게 공부했기에. 그렇기에 여러분에게는 쉽게 풀어주기 위해서 노력하고자 하는 것이니 양해해 주길 바라며, 이미 기본지식을 가지고 있는 사람은 적당히 자신에게 맞는 부분으로 속독하며 보아나가면 될 것 같다.

쿼리 분석기(Query Analyzer)를 열고 다음과 같은 구문을 입력해보도록 한다.

-- pubs를 사용한다고 설정 Use pubs -- 가장 비싼 책의 가격을 확인 Select max(price) from titles GO -- 프로시저 생성 CREATE PROC usp_UpdatePrice_Titles AS Update titles SET price = price * 1.2 GO -- 만들어진 프로시저를 실행 Exec usp_UpdatePrice_Titles GO -- 가장 비싼 책의 가격을 확인 Select max(price) from titles

먼저 위에서 사용한 SQL 문들을 살펴보도록 하자. 먼저 Select max(price) from titles 를 통해서 현재 책 가격중에 가장 비싼 책의 가격을 먼저 알아보도록 한다. 그래야, 책의 가격을 변동시킨 뒤, 제대로 변경되었는지 확인이 가능할테니 말이다. 그 다음에 Go 라고 되어져 있는 것은 SQL 서버에서 쿼리를 수행할 경우, 각각의 배치를 구분하기 위해서 사용한 것이다. 스토어드 프로시져를 만드는 작업이전에는 어떠한 명령도 이전에 존재하지 않아야 하기에 Go를 두어 이전의 작업을 먼저 끝내게 한 것이다.

그리고 난 뒤, 저장 프로시져를 만든다. 이 프로시져의 이름은 usp_UpdatePrice_Titles 이며, 이름은 여러분이 원하는대로 주어도 무관하다. 그리고, 이 프로시져는 Update titles SET price = price * 1.2 란 쿼리를 수행하도록 만든 것을 볼 수가 있을 것이다. 이 프로시져가 만들어지면 이후로는 Update titles SET price = price * 1.2 대신에 usp_UpdatePrice_Titles를 실행시켜도 같은 결과를 볼 수가 있을 것이다.

이제 프로시져는 만들어 졌다. 그렇다면, 이 만들어진 프로시져를 실행해보도록 하자. 사용자가 만든 저장 프로시져를 실행하기 위해서는 EXEC 라는 명령을 사용하면 된다. 해서 소스에서는 EXEC usp_UpdatePrice_Titles를 사용하여 우리가 만든 프로시져를 수행하도록 하고 있다.

그리고, 다시 가장 비싼 책의 가격을 알아보니 20% 인상이 되어져 있는 것을 알 수가 있다. 이것은 위의 쿼리가 수행되면서 나타나는 결과화면으로 원래의 가격과 인상된 가격의 모습을 볼 수가 있다.

---------------------
22.9500
Warning: Null value eliminated from aggregate.

---------------------
27.5400
Warning: Null value eliminated from aggregate.

결과중에 있는 경고는 책값중에 값이 NULL 인 것이 있어서 나는 경고이니 심각하게 받아들이지 않아도 된다. 일반적인 SQL 구문을 스토어드 프로시져로 바꾸는 것은 그리 어렵지 않았다. 간단하게만 사용한다면 저장 프로시져도 그렇게 어렵지는 않다.

이번에는 이 저장 프로시져에 인자를 넘기는 예를 한번 해보도록 하자. 우리 회사에서 갑작스러운 이유로 특정 타입의 책들에 대해서는 수시로 가격을 조금은 올리는 경우가 잦다고 가정을 해 보자. 물론, 실제상황에서 그런 일은 없을테지만 단지 예에 불과하니까 "예"하고 같이 해보자.

위와 같은 상황이라면 어떤 쿼리문이 필요할까? 다음과 같은 쿼리문이 필요할 것이다.

Update titles SET price = price + 올릴가격
Where type = 올릴가격의 책 타입

그렇다면, 이번에는 이것을 프로시져로 만들어 보도록 하자. 그렇다면 이 프로시져로는 두 개의 인자를 넘길 수 있어야 할 것이다. 올릴 가격에 대한 값과, 값을 올릴 책의 타입을 말이다. 그런 경우에는 프로시져를 어떻게 제작해야 하는가? 다음처럼 하면 된다.

CREATE PROC usp_UpdatePrice2_Titles     @type char(12),     @plus int AS>     Update titles SET price = price + @plus     Where type = @type

프로시져에서 사용하는 변수는 @하나로 시작한다. 위의 소스에서 보면 프로시져 이름 뒤로, 프로시져 내부의 SQL 문에서 필요한 두가지의 인자를 넘기도록 하는 것을 볼 수가 있다. 변수이름은 여러분이 원하는 대로 줄 수 있지만, 그 변수의 타입은 잘 맞추어 주어야 한다. 우선 첫 번째 인자로는 책의 타입을 넘겨줄 것이기에 변수 이름을 @type 그 데이터타입은 char(12)으로 준 것을 볼 수있다. 그 다음 인자로는 , 를 구분자로 하여 @plus 라는 지정하고 있는데, 이는 책의 인상가격이 될 것이다. 데이터 타입은 int 이고 말이다. 이렇게 프로시져 뒤로 두 개의 변수를 받아들이게 되면, 프로시져 내에서는 이 값을 가지고 처리를 할 수 있게 된다.

이번 프로시져에서는 이 넘어온 두 개의 변수를 사용해서 특정 타입의 책 가격을 특정 값만큼 인상하고 있다. 어렵지 않은 편이다. 그럼 실제로 한번 이 프로시져를 만들어 보자.

Query Anayzer 에다 위의 프로시져 구문을 입력하고 실행해 보자. 오타 없이 잘 수행했다면 여러분은 The command(s) completed successfully.란 메시지를 받을 것이다. 이제 이 프로시져는 만들어 졌다. 그렇다면, 이제 이 프로시져를 사용해 보도록 하자. 우리는 책중에서 Business 타입인 책들의 가격을 10 달러 인상시킬 계획이다. 그렇게 한번 해보자.

이 프로시져를 사용하기 전에 확인차원에서 현재 titles 테이블의 Business 타입의 책중에 가장 비싼 책의 값이 얼마인지 미리 확인해 두도록 하자. 다음 쿼리를 통해서 말이다.

Select max(price) from titles Where type = 'business'

위에서부터 주욱 따라하셨다면 이 값은 23.988 라고 나올 것이다. 이 값을 기억해 두자. 23.988 이 아니라면 그래도 좋다. 하지만, 그럴 경우 그 값을 어딘가에 적어두도록 하자.

이제 다음처럼 프로시져를 사용해 보도록 하자.

Exec usp_UpdatePrice2_Titles 'business', 10

주의할 것은 우리가 프로시져를 만들 때, 첫 번째 인자가 char인 타입 컬럼, 두 번째 인자가 인상할 가격인 plus 이었으므로 그 순서에 맞추어 값을 넣어주어야 한다는 사실이다. 만일, 그 순서와 무관하게 가격을 넣고 싶다면, 다음처럼 그 변수이름을 사용해서 값을 넣을 수도 있다.

Exec usp_UpdatePrice2_Titles @plus=10, @type=business

프로시져를 수행했다면 Select 쿼리를 통해서 가격이 10 달러 증가했는지 확인해 보도록 하자. (필자의 경우라면 가격은 33.988이 되어져 있다.)

Back