[MSSQL] 저장 프로시저 (Stored Procedure) 란?

실무에서는 프로그램에서 만들어 놓은 SQL문을 저장해 놓고, 필요할 때마다 호출해서 사용하는 방식으로 프로그램을 만든다.

 

저장 프로시저 (Stroed Procedure)

 

저장 프로시저는 이러한 방식이 가능하도록하는 각 DBMS 에서 제공하는 프로그래밍 기능이다.

Oracle, MySQL 등 대부분의 DBMS 에서 제공하고 있는데, 오늘은 SQL Server에서 제공하는 저장 프로시저에 대해 정리할 생각이다.

 

저장 프로시저는 쿼리문들의 집합으로, 어떤 동작을 여러쿼리를 거쳐서 일괄적으로 처리할 때 사용한다.

 

여기서 이런 질문을 할 수도 있다.

 

"그럼 그냥 SQL 문 쓰면 되지 , 굳이 SP를 만들어 써야하나?"

 

저장 프로시저를 실무에서 사용하는 이유가 있다.

 

먼저, SQL Server의 성능을 향상 시킬 수 있다.

저장 프로시저를 처음에 실행하면 최적화, 컴파일 단계를 거쳐 그 결과가 캐시(메모리)에 저장되게 되는데, 이 후에 해당 SP를 실행하게 되면 캐시(메모리)에 있는 것을 가져와서 사용하므로 실행속도가 빨라지게 된다.

그렇기 때문에 일반 쿼리를 반복해서 실행하는 것보다 SP 를 사용하는게  성능적인 측면에서 좋다.

 

두번째, 유지보수 및 재활용 측면에서 좋다.

C#, Java등으로 만들어진 응용프로그램에서 직접 SQL문을 호출하지 않고 저장 프로시저의 이름을 호출하도록 설정하여 사용하는 경우가 많은데, 이때 개발자는 수정요건이 발생할때 코드 내 SQL문을 건드리는게 아니라 SP 파일만 수정하면 되기 때문에 유지보수 측면에서 유리해진다. 

또한 한번 저장 프로시저를 생성해 놓으면, 언제든 실행이 가능하기 때문에 재활용 측면에서 매우 좋다.

 

셋째, 보안을 강화할 수 있다.

사용자별로 테이블에 권한을 주는게 아닌 저장 프로시저에만 접근 권한을 주는 방식으로 보안을 강화할 수 있다.

실제 테이블에 접근하여 다양한 조작을 하는 것은 위험하기 때문에 실무에서는 실제로 개발자에게는 sp권한만 주는 방식을 많이 사용한다.

 

마지막으로, 네트워크의 부하를 줄일 수 있다.

클라이언트에서 서버로 쿼리의 모든 텍스트가 전송될 경우 네트워크에는 큰 부하가 발생하게 된다. 하지만 저장 프로시저를 이용한다면 저장프로시저의 이름, 매개변수 등 몇글자만 전송하면 되기 때문에 부하를 크게 줄일 수 있다.

 

일반적인 SQL 문과 저장 프로시저 동작 방식 비교

 

저장 프로시저를 사용하면보통 일반적인 T-SQL 사용보다 시스템의 성능이 향상 된다고 한다.

그 이유는 뭇엇일까? 내부에서 처리되는 방식을 비교해서 한번 확인해보자.

 

일반 T-SQL 동작 방식

위의 그림은 일반적인 T-SQL 문을 처음으로 실행하면 위의 프로세스로 동작한다.

만약에 아래 쿼리를 실행한다고 가정하자.

SELECT name FROM userTbl;

그러면 먼저 구문 분석단계에서 구문 자체에 오류가 없는지 분석을 할 것이다. 만약 오타가 잇으면 여기서 오류가 발생되어 에러메시지를 띄울 것이다.

 

다음은 개체 이름 확인 단계에서 userTbl 이라는 테이블이 현재 데이터베이스에 있는지 확인을 한다. 만약에 userTbl이 있으면 그안에 name이라는 열이 있는지를 확인할 것이다.

 

그다음 사용권한 확인 단계에서 userTbl을 현재 접근중인 사용자가 권한이 있는지를 확인한다.

 

다음으로 최적화 단계에서 해당 쿼리문이 가장 좋은 성능을 낼 수 있는 경로를 결정한다. 인덱스 사용여부에 따라 경로가 결정된다고 보면된다. 위의 쿼리의 경우 전체 데이터를 가져오기 때문에 아마도 테이블 스캔이나 클러스터 인덱스 스캔이 될 것이다.

 

다음은 최적화된 결과를 바탕으로 컴파일 및 실행 계획 등록 단계에서 해당 실행계획 결과를 메모리(캐시)에 등록한다.

 

그리고 컴파일된 결과를 실행한다. 

 

단 한 줄의 쿼리지만 이렇게 많은 절차를 거친다...! 한번쯤은 제대로 알아두면 좋으니 잘 보고 공부해두자.

 

만약에 동일한 SQL문을 실행하면 아래와 같이 단순하게 동작하게 된다.

여러 과정이 생략되다 보니 시간이 단축되게 될 것이다.

만약에 메모리(캐시)에 동일한 쿼리가 없다면 위의 전체 과정을 다시 반복할 것이다.

여기서 주의해야할 점은 쿼리 전체가 한글자도 틀리자 않고 같아야 한다는 것이다.

 

 

다음은 저장 프로시저의 동작 방식이다.

 

먼저 저장 프로시저를 정의 했을때 작동방식을 알아보자.

 

일단 먼저 해당 저장 프로시저에서 구문 오류가 있는 지를 파악하는 과정을 거친다.

 

다음은 지연된 이름 확인(deferred name resolution) 과정을 거치게 되는데 이는 저장 프로시저의 특징중 하나이니 잘 기억해두자.

저장 프로시저의 경우에는 프로시저를 정의하는 시점에 테이블과 같은 해당 개체의 존재 여부와 상관없이 정의가 가능한데, 그 이유는 해당 테이블의 존재 여부를 프로시저의 실행 시점에 확인하기 때문이다. 그렇기에 해당 테이블의 존재 여부와 상관없이 프로시저는 정의할 수 있다. 그런데 테이블의 열이름이 틀리면 오류가 발생된다.

실무에서 없는 테이블을 프로시저 정의에 사용하는 등의 실수를 할 수 있으니 주의하자.

 

다음은 생성권한은 확인하는 단계인데 사용자가 저장 프로시저를 생성할 권한이 있는지를 확인하는 과정이다.

 

마지막으로 시스템 테이블 등록을 진행한다. 저장 프로시저의 이름과 코드가 관련 시스템 테이블에 등록되는 과정이다. 만약 관련 내용을 확인하고자 하면 카탈로그 뷰 sys.objects 및 sys.sql_modules 등을 확인하자.

 

 

 

다음은 첫번째로 저장 프로시저를 실행했을때 벌어지는 일이다.

일반적인 쿼리를 수행하는 것과 비슷하다. 일단 정의 단계에서 구문분석은 끝났기때문에 따로 구문분석을 하지 않는다.

앞서 정의시에 지연된 이름 확인이란게 있었는데, 실제로 해당 개체가 유효한지를 개체이름 확인 단계에서 진행하게 된다. 다시 말해서 저장 프로시저의 실행 시에만 해당 개체가 존재하면 실행이 된다.

 

그리고 그 이후에 앞서 쿼리문을 돌렸을때와 같이 사용권한 확인, 최적화, 컴파일 및 실행계획 등록 단계를 거치 실행 되게 된다.

 

 

이후에 두번째 실행 부터는 메모리(캐시)에 있는 것을 그대로 가져와 재사용하게 되어 수행시간이 많이 단축되게 된다.

 

이렇게만 보면 일반 sql문과 거의 차이가 없어보인다.

하지만 아래 상황을 보자.

SELECT * FROM userTbl WHERE name ='이승기';
SELECT * FROM userTbl WHERE name ='성시경';
SELECT * FROM userTbl WHERE name ='은지원';

해당 쿼리는 where 조건의 값만 다르다. 그런데 앞서 말한 것처럼 일반 쿼리는 글자 하나라도 다르면 다른 쿼리라 인식하기 때문에 세 쿼리 모두 다 다른 것으로 인식해버린다.

그렇기 때문에 매번 최적화와 컴파일을 다시 수행해야한다.

 

이걸 그냥 아래와 같은 저장 프로시저로 만들면?

CREATE PROC select_by_name
	@Name NVARCHAR(3)
AS
	SELECT * FROM userTbl WHERE name =@name;

 

EXEC select_by_name '이승기';
EXEC select_by_name '성시경';
EXEC select_by_name '은지원';

이렇게 하면 첫번째 이승기를 검색하는 과정에서만 최적화 및 컴파일을 수행하고 나머지는 메모리(캐시)에 있는것을 사용하게 된다. 실제로 다른 것들의 경과시간이 0ms 인지는 직접 확인해보길 바란다.

 

결국 자주 쓰는 쿼리라면 일반 쿼리를 여러 개 날리는 것보다 저정 프로시저를 쓰는게 성능적인 측면에서 효과적인 것을 확인 할 수 있다.

 

저장 프로시저의 문제점

그렇다면 저장 프로시저는 과연 만능의 성능을 자랑할까?

 

대부분의 경우에는 성능이 향상되나 항상 그렇지는 않다.

앞에서 저장 프로시저를 실행할 때 최적화 단계를 수행한다고 말씀드렸다. 최적화 단계에서 인덱스를 사용할지 안할지를 결정하게 되는데, 다들 아시는 것처럼 인덱스를 사용한다고 항상 수행결과가 빨라지지 않는다.

만약에 가져올 데이터가 다량인데 인덱스를 사용하면 오히려 성능이 바빠지게 될 것이다.

 

저장 프로시저는 첫번째 수행 시에 최적화가 이루어져서 인덱스 사용여부가 결정되어 버린다. 

만약에 첫번째 수행때 데이터를 몇건만 가져오도록 파라미터가 설정되어 있다면, 인덱스를 사용하도록 최적화되어 컴파일 됐을 것이다.

그런데 두번째 수행에서 많은 건수의 데이터를 가져오도록 파라미터가 들어가면..? 일반 쿼리문이었다면 파라미터가 달라졌으니 다시 최적화되어 컴파일 되겠지만... 안타깝게도 저장 프로시저는 그냥 인덱스를 사용하는 프로시저를 실행시켜 버릴 것이다.

 

이렇게 되어 버리면 성능에 크게 문제가 될 것이다. 이를 방지 하기 위해서는 저장 프로시저를 다시 컴파일 해줘야한다.

 

다시 컴파일 하는 방법은 여러개가 있는데, 

실무에서는 보통 인덱스 사용여부가 불분명하다면 저장 프로시저를 생성한느 시점에서 아예 실행시마다 다시 컴파일 되도록 설정해버리기도 한다.

DROP PROC sp_recompile_test
GO
CREATE PROC sp_recompile_test
	[매개변수]
WITH RECOMPILE
AS
	[사용 될 쿼리문]
GO

 

오늘은 저장 프로시저에 대해 알아봤다.

다음 포스팅에서는 저장 프로시저를 사용하는 방법에 대해 간단하게 포스팅 해보도록 하겠다.

 

끝.

 

ref.

-우재남, 『이것이 SQL Server다』, 한빛미디어(2020)