Ad-Hoc 쿼리와 저장 프로시저(SP, Stored Procedures) 차이점

이 문서의 내용

Ad-Hoc 쿼리란?

Ad-Hoc은 일반적으로 날 쿼리라고도 부르는 SQL 구문 그 자체를 의미합니다.

SELECT * FROM table WHERE id = 123

프로그래밍에서는 다음과 같은 SQL 구문이 될 수 있습니다.

String sql = "SELECT * FROM table WHERE id = " + id;

Ad-Hoc의 가장 큰 문제는 쿼리 구문이 변수에 의해서 캐싱되지 않는다는 점입니다.

int id = 123;
String sql0 = "SELECT * FROM table WHERE id = " + id;

id = 234;
String sql1 = "SELECT * FROM table WHERE id = " + id;

위 코드에서 두 개의 sql0 sql1은 SELECT * FROM table WHERE id =라는 공통된 구문을 갖고 있습니다.

이어서 변수를 사용하므로 공통 구문을 캐싱할 것으로 기대됩니다.

하지만 실제로는 SELECT * FROM table WHERE id = 123 그리고 SELECT * FROM table WHERE id = 234 두 개의 구문이 각자 저장되어 사용됩니다.

위 코드가 서로 다른 변수 값으로 100000회 호출되었다면 Connection에서는 100000개의 구문이 저장되어 캐싱되기를 기다립니다.

극단적으로는 구문이 한 번도 재사용되지 않기에 캐싱에 의한 성능 향상을 이루지 못하고 메모리 사용량은 계속해서 늘어나게 됩니다.

반면 Ad-Hoc 사용의 장점은 편리하고 즉흥적으로 사용 할 수 있다는 점입니다.

별도 공수 없이 머리 속에서 떠오르는 그대로 SQL을 사용 할 수 있으니 임시 사용되는 SQL에 적합합니다.

저장 프로시저란?

SP(Stored Procedure)는 SQL 구문을 하나의 메소드처럼 구성하여 어떤 동작을 일괄적으로 처리합니다.

프로시져를 구성하고 여러 개의 복잡한 작업을 다수의 SQL로 연이어 처리 할 수 있으므로 일종의 프로그래밍의 역할을 대신합니다.

DELIMITER //

CREATE PROCEDURE GetAllProducts()
BEGIN
	SELECT *  FROM products;
END //

DELIMITER ;

SP의 가장 큰 장점은 성능과 관리 측면에 있습니다.

성능	관리
하나의 프로시져가 컴파일된 이후에 그 안에서 사용되는 파라미터가 다르게 호출되더라도 캐싱됩니다.	DBMS 관련 코드를 개발하는 조직이 분리되어 있다면 프로시져를 개발팀에 API처럼 제공 할 수 있습니다.
하나의 작업을 처리하기 위해 N개의 Ad-Hoc 쿼리가 필요하더라도 SP는 한 번만 호출됩니다. 이로 인해 라운드 트립(Round Trip) 상에서 이점이 있습니다.	버그 픽스를 위해 애플리케이션의 배포 없이 DB의 프로시저를 수정하는 것이 가능합니다.

하지만 모든 상황에서 SP가 Ad-Hoc보다 유리한 것은 아닙니다.

• 전반적으로 SP에서 실행되는 명령 등은 C 또는 Java와 같은 전통적인 프로그래밍에서의 것보다 훨씬 느립니다.
• 긴 구문으로 작성된 SP 분석이 난해하고 프로그래밍 환경에 비해 유지 보수와 디버깅 난이도가 높습니다.

정리 및 복습

• Ad-Hoc 쿼리는 날 쿼리라고도 부르며 일반적인 SQL 구문을 의미합니다.
• Ad-Hoc 쿼리는 즉흥적이고 임시 방편의 상황에서 사용합니다.
• SP(Stored Procedure)는 프로시저를 의미합니다.
• SP를 사용하면 캐싱, 라운드 트립과 같은 성능 상의 이점이 있습니다.
• SP를 사용하면 프로시저를 API 처럼 제공하고 애플리케이션 배포 없이 프로시저를 수정하는 것으로 버그 픽스가 가능합니다.
• 반면 SP에서 실행되는 명령은 실행이 느리고 유지 보수 및 디버깅에 어려움이 있을 수 있습니다.