업무 개발자들이 작업해놓은 결과물을 보다보면, 좀 어이없는 맹신에 빠져서 성능이나 효율을 크게 떨어뜨리는 경우를 볼 때가 있습니다. 그중에 하나가, 스토어드 프로시저에 대한 것입니다.

가장 흔한 경우가, 각 화면 하나 단위, 혹은 디비에 액세스하는 대부분의 쿼리 단위로 스토어드 프로시저를 만들어 쌓아대는 것입니다. 이렇게 할 경우 화면 갯수와 비슷한 갯수 만큼, 혹은 그보다 많은 스토어드 프로시저가 디비에 쌓이게 되죠.

이렇게 작업을 하는 이유는 하나의 미신 때문입니다. SQL은 기본적으로 스토어드 프로시저로 만들어야 최고의 성능을 낼 수 있고.. 디비로 가는 네트워크 부하도 줄일 수 있고.. 뭐 이런 식입니다.

물론 말은 맞습니다. 원론적으로 봤을 때, 일반적인 SQ문들은 처음 실행시에 스토어드 프로시저는 작성 즉시, 그리고 디비의 기동 즉시 그 프로시저의 실행 계획이 생성됩니다. 또, 수없이 길어질 수 있는 SQL 문 대신에 프로시저 이름과 파라미터 정도로만 호출하니까 네트워크 부하도 적어질 수 있지요.

그런데, 그 반대편으로는, 디비 서버에 수없이 쌓이는 프로시저들을 어떻게 관리할 것이냐의 문제도 생깁니다. 특히 테이블의 경우 갯수가 많아지더라도 사용되는 것과 사용되지 않는 것의 구별이 비교적 직관적으로 이루어질 수 있지만, 프로시저는 도대체 사용되는지의 여부와 사용된다면 어디에서 사용되는지를 한눈에 알아보기가 쉽지 않습니다. 물론 부지런해서 프로시저 앞부분에 주석을 잘 작성해두면 이런 문제는 줄일 수 있습니다만, 역시 이것도 관리의 부담입니다.

물론 스토어드 프로시저를 디비 서버에 존재하는 일종의 미들티어다, 비즈니스 로직이다, 라고 간주해버리면 머릿속의 스트레스 리스트에서 빼버릴 수도 있겠지만... 실제로는 스토어드 프로시저가 비즈니스 로직을 완전히 대체하기에는 기능적으로 너무 미약하고, 미들티어로 보기에는 더욱 그렇죠.


그럼 과연 스토어드 프로시저가 이런 부담을 감수하고라도 써야 할 궁극의 해결책인가..하면, 그렇지 않습니다. 먼저, 성능 문제에 있어서, 스토어드 프로시저가 작성 직후, 그리고 기동 직후에 컴파일되어 실행 계획이 만들어지니까 빠르기는 합니다만, 서버 기반의 업무 시스템에서 최초의 한번의 성능이라는 것은 전체에 미치는 영향이 극히 미미합니다. 일반 SQL문들도 한번만 실행되면 실행계획이 만들어지고 캐시되기 때문에, 두번째 실행부터는 스토어드 프로시저의 성능과 차이가 나지 않습니다. (물론 이 부분에 있어서는 데이터베이스 내부의 아키텍처에 관련된 좀 디테일한 변수가 있습니다.)

또, 업무 개발에서는 대단히 긴 SQL문들이 종종 등장하기는 하지만, 그런 SQL은 일부에 불과하죠. select를 이용하는 조회 쿼리라면, 개발자에게는 웬만큼 길다고 해도, 대다수는 몇 kB 이내입니다. 그럼 네트워크의 전송 패킷을 고려할 때, 4kB 이내라면 전송 바이트수가 네트워크에서 부하의 차이가 없는 동일한 한 패킷이라는 얘기죠. 4kB이면 웬만한 select문들은 다 들어가고도 남는 넉넉한 공간이고요.

프로시저의 사용에서 최악의 경우는.. 스토어드 프로시저 안에서 마음놓고 동적 쿼리(혹은 ad-hoc 쿼리)를 만들어내는 것입니다. 보통 DBA나 업무 개발자들이 깔끔한 쿼리를 만들어내기 어려울 때 이런 동적 쿼리를 쓰는데... 동적 쿼리는 말 그대로 SQL문을 문자열 연산으로 만들어내는 것이므로 매번 실행계획이 달라지고, 그래서 성능상 좋지 않다는 것은 알만한 분들은 다 압니다.

그런데, 이런 동적쿼리의 성능 저하 문제를, 스토어드 프로시저로 만들면 해결할 수 있다고 믿는 DBA, 개발자들이 적지 않다는 것입니다. 결론을 말씀드리면, 스토어드 프로시저 내에서 문자열 연산을 해서 동적 쿼리를 하면, 오히려 동일한 동적 쿼리의 SQL문을 직접 날리는 것보다 오히려 더 못한 결과가 나옵니다. 왜냐하면, 작성 시점에서 실행계획이 만들어지는 스토어드 프로시저의 특성상 한번 만들어진 실행계획은 그대로 쭈욱 가는데, 실제로 동작하는 SQL의 내용은 매번 바뀌므로 실행계획의 효율성이 들쭉날쭉할 수밖에 없기 때문입니다. (이런 문제 때문에 매번 실행시마다 스토어드 프로시저를 재컴파일하도록 강제하는 옵션까지 있습니다. 그럼 이런 경우에 프로시저는 도대체 뭥미?)

이에 반해, 어차피 동적 쿼리라면, 차라리 SQL문을 그대로 날리면 매번의 패턴에 따라 실행계획이 캐시되어 더 성능이 좋은 쿼리 결과가 나올 가능성이 높습니다. (물론 동적 쿼리는 최대한 안쓰는 것이 정답이고, 거의 대부분의 경우 동적 쿼리를 통하지 않을 다른 쿼리 튜닝 방법이 있게 마련입니다만)


성능을 위해 정말로 중요한 것은, 스토어드 프로시저가 아니라 파라미터 쿼리(Parameterized Query)입니다. SQL문에서 SQL 문에 조건 등의 값을 직접 써넣지 말고 파라미터로 값을 전달하라는 겁니다. 며칠전에도 델파이 Q/A에서 관련 답변을 한 적이 있는데요.
http://delphi.borlandforum.com/impboard/impboard.dll?action=read&db=del_qna&no=12106

위 링크의 글에서 썼다시피, 일반적으로 델파이나 C++빌더 등의 쿼리 컴포넌트에서 쓰는 파라미터들은 VCL 수준의 기능이 아니라 디비 서버의 기능입니다. 그리고 여기에서 디비 서버에서 파라미터를 쓰는 가장 큰 이유가, 파라미터 값들을 제외한 SQL문으로 공통 실행계획을 캐시하기 때문입니다. 일단 컴파일된 실행계획은 디비 서버 내에서 해시로 관리되는데, 다음번에 SQL 문이 들어오면 컴파일을 하기 전에 해당 해시의 실행계획을 찾아봐서 바로 실행하게 됩니다.

물론 요즘의 대부분의 메이저 디비 서버들은, 파라미터를 쓰지 않은 쿼리(속칭 날쿼리)를 쓰더라도 자동으로 파라미터 쿼리로 바꿔주는, 즉 알아서 파라미터를 추출해서 파라미터 쿼리로 만들어서 실행하는 기능이 있습니다. 하지만 이 기능은 SQL이 조금만 복잡해지면 제대로 동작하지 않기 때문에, 차라리 기대를 하지 않는 편이 낫습니다. 게다가, 이렇게 자동 파라미터가 되지 않는 날쿼리의 경우 매번 자동 파라미터화를 시도하고, 그때마다 실패하기 때문에 오히려 쿼리 속도가 더 느려지게 됩니다. 성능을 높이기 위한 자동화 기능이 오히려 성능을 낮추는 결과가 되는 겁니다.

파라미터 쿼리가 중요한 또 한가지 이유는, 날쿼리의 남발로 인해 정상적으로 실행계획이 생성되어 빠르게 실행되어야 할 더 중요한 SQL들의 실행 속도까지 떨어뜨릴 수 있다는 것입니다. SQL 실행계획은 정적인 것이 아니라 캐시이기 때문에, 실행계획 캐시에 실행계획들이 가득차면 당근 가장 오래된 것부터 차례로 삭제됩니다. 그런데 날쿼리를 남발하면, 조건 값만 다르고 사실은 같은 쿼리들, 즉 수없이 많은 A, A', A'', A''', A'''' 쿼리들이 실행계획 캐시를 가득 채워서 더 중요한 다른 쿼리의 실행계획이 계속 삭제되는 경우가 생기는 거죠.


요약하자면...
스토어드 프로시저의 남발은 관리 부담을 키운다
경우에 따라 스토어드 프로시저가 성능상 더 불리할 수도 있다
무조건 프로시저로 만들고 볼 것이 아니라 생각을 해야 한다 (머리는 둬서 머하나)
성능 측면에서 볼 때 프로시저보다는 파라미터 쿼리가 훨씬 중요하다