🧱[SQL] Stored Procedure 이란?

🔎Stored Procedure(저장프로시저) 란 무엇인지에 대해 알아보겠습니다.

---

🚀Stored Procedure

미리 SQL문을 만들어 저장해 놓고 필요할 때 마다 호출하여 사용하는 프로그래밍 기능 입니다.

• books 테이블을 select하는 sql 문 입니다.

SELECT * FROM books

• books 테이블을 select하는 sql문을 저장해 놓은 Stored Procedure 입니다.

USE pubs
GO

--프로시져 생성구문
CREATE PROC bookSP
AS
SELECT * FROM books
GO

--프로시져 수행 구문
EXEC bookSP

　

앞서 보여드린 예시만 봐서는 굳이 왜 더 복잡한 stored procedure을 사용해야 하는지 이해가 가지 않습니다. 그렇다면 stored procedured의 장점은 무엇일까요?

Stored Procedure의 장점

1. 성능의 향상
2. 보안성에 유리
3. 유지보수, 재활용성에 유리
4. 네트워크 부하 감소

1. 성능의 향상

성능의 향상을 알아보기 위해 먼저 SQL 문과 저장 프로시저 동작 방식에 대해 알아보겠습니다.

🎯 SQL 동작방식

[첫 실행]

[N번째 실행]

이때 만약 캐시에 동일한 쿼리가 없다면 위의 과정을 처음부터 재실행 하게 됩니다.

🎯 Stored Procedure 동작방식

[Stored Procedure 생성]

[첫 실행]

• Stored Procedure 생성시 에 구문분석을 완료했기 때문에 이 단계에서는 구문분석을 하지 않습니다.
  

[N번째 실행]

• 이후 실행 부터는 캐시에 있는 것을 재사용하게 되어 수행시간이 많이 단축됩니다.
  

✨ 예제를 확인해 보겠습니다.

sql문

• 우선 sql문은 where절의 조건만 다른 3가지의 쿼리문을 매번 최적화와 컴파일을 다시 수행해야 합니다.

SELECT * FROM books WHERE quantity = 1;
SELECT * FROM books WHERE quantity = 2;
SELECT * FROM books WHERE quantity = 3;

Stored Procedure

• 그에 반해 Stored Procedure는 첫 수행인 1을 검색하는 과정에서만 최적화와 컴파일을 수행하고 N번째 수행 부터는 (2,3) 캐시에 있는것을 사용하게 됩니다.

--프로시져 생성구문
CREATE PROC bookSP
	@Quantity int
AS
	SELECT * FROM books WHERE quantity = @quantity;
--프로시져 수행 구문
EXEC bookSP 1;
EXEC bookSP 2;
EXEC bookSP 3;

따라서 stored procedure을 사용하면 성능적인 면에서 유리합니다.

2. 보안성에 유리

실제 테이블에 접근하여 다양한 조작을 하는 것은 위험한 일입니다.

따라서 사용자별로 테이블에 권한을 주는게 아닌 저장 프로시저에만 접근 권한을 주는 방식을 통해 보안성을 향상시킬 수 있습니다.

3. 유지보수, 재활용성에 유리

프로시저를 한 번 만들어 놓으면 프로시저 이름과 매개변수 만으로 계속 호출할 수 있기 때문에 유지보수와 재활용성에 유리합니다. 또한 비즈니스 로직에서 쿼리문을 일일이 찾아 수정하는 것이 아닌 프로시저를 수정하면 되기 때문에 원활한 유지보수가 가능해 집니다.

4. 네트워크 부하 감소

서버로 많은 양의 일반 쿼리를 계속 호출한다면 네트워크에 부담이 될 수 있습니다. 프로시저는 프로시저 명과 매개변수만으로 호출 가능하기 때문에 네트워크에 대한 부담을 줄일 수 있습니다.

---

Stored Procedure의 단점

최적화 단계에서 결정되는 인덱스를 사용여부로 인해 성능 변화가 생길 수 있습니다.
데이터 양이 많을 경우 인덱스를 사용하는 것은 오히려 성능이 더 나빠질 수 있습니다.