Ad Hoc Query란?
비정형적인 Query를 의미합니다. 일반적으로 여러 사용자가 자주 사용하는 업무용 SQL은 Pro*C든 PowerBuilder든 Application Program을 잘 작성해서 현업 사용자에게 제공(정형적인 SQL 형태)하지만, One Time용 같이 단발성으로 필요한 경우는 프로그램하기에는 좀 그렇겠죠. 어차피 일회성이 강하니까요. 그럴땐 SQL*PLUS 나 TOAD 같은 TOOL을 통해 직접 SQL을 작성하는 것을 Ad Hoc SQL라고 부르죠. 예전엔 프로그램이 복잡(절차형 언어)해서 반드시 프로그래머에게 요청해야 가능했지만, SQL은 비절차형 언어라서 현업 사용자들도 기본 문법만 알면, 자신이 필요한 Data를 직접 뽑아볼 수 있겠죠. 그런형태로 One-time성 작업에 대해 필요한걸 그때그때 현업 사용자가 직접 DB에 Query하는 SQL 형태를 말하는 거랍니다.
왜 저장 프로시저를 이용해야 하나?
많은 사람들이 T-SQL을 저장 프로시저로 변환해야만 한다고 이야기한다. 그 말이 맞을까? SQL 서버를 제외한 다른 데이터베이스의 경우에도 모두 저장 프로시저를 이용해야만 할까? 저장 프로시저에 반대되는 개념은 Ad-hoc 쿼리, 흔히 임시 쿼리라고 부르는 것들이다. 쿼리 분석기 열고 SELECT 어쩌고 쿼리를 날리게 되면 이것은 Ad-hoc 쿼리가 된다.
Ad-hoc 쿼리에 비해서 저장 프로시저는 항상 더 나은 솔루션(Solution)일까? 대부분의 상용 RDBMS에서는 저장 프로시저를 지원하고 있고, 자주 사용되는 문들은 저장 프로시저로 작성하는 것을 권하고 있다. 물론, 그것은 저장 프로시저로 코드화 할 수 있는 경우를 이야기하는 것이다.
예를 들어 SQL 서버의 경우에는 저장 프로시저에서 여러 로우를 포함하는 Rowset을 반환할 수 있지만, 오라클에서는 참조 커서를 써야만 한다. SQL 서버에서 ADO를 통하는 경우 대부분의 Ad-hoc 쿼리들은 Auto-parameterization을 통해서 파라메터 바인딩을 지원한다. 물론 그렇다고 SQL 서버에서 Ad-hoc 쿼리가 많아지는 것이 바람직한 것은 아니다. 반면에 오라클에서는 바인드 옵션을 Force로 하지 않는 이상, Ad-hoc 쿼리들은 파라메터 바인딩 처리는 자동으로 되지 않는다. 오라클의 경우는 바인딩을 쓰지 않는 Ad-hoc 쿼리의 경우 보다 문제가 심각해질 수 있다.
여러 특성에도 불구하고 저장 프로시저는 일반적으로 Ad-hoc 쿼리보다 더 좋은 대안이 된다. 그 이유를 하나씩 살펴보자.
코드의 길이가 짧다.
가장 단순 명료한 이유이다. 간단히 몇 바이트의 명령으로 동일한 결과를 볼 수 있다면, 네트워크나 SQL 서버의 메모리 효율을 더 높여줄 수 있을 것이다. [리스트1]의 두 가지 코드는 동일한 역할을 수행하는 Northwind 카탈로그에 존재하는 CustOrderHist 라는 저장 프로시저와 저장 프로시저의 실제 코드를 비교한 것이다.
모두, 해당 고객 아이디를 인자로 받아서, 해당 고객의 주문 내역을 추적하는 역할을 수행한다. 하지만, 첫 번째 저장 프로시저는 서버로 24 바이트의 명령을 전송하면 되고, 두번째 Ad-hoc 쿼리는 그 보다 10배 많은 238 바이트의 명령을 전달해야만 한다.
저장 프로시저와 임시 쿼리 비교
-- 1. Stored Procedure, 24 Bytes
dbo.CustOrderHist "ANTON"
-- 2. Ad-hoc Query, 238 Bytes
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = "ANTON"
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
라운드 트립(Round-trip이 적다)
저장 프로시저는 컴파일 된다.
기타 이유들
저장 프로시저를 이용하는 가장 큰 세가지 이유들을 이미 설명했다. 이외에도 저장 프로시저를 이용할 때 발생하는 여러 가지 장점들이 있다. 예를 들어 저장 프로시저를 이용하면 보안에 도움을 줄 수 있다. 왜냐하면, 저장 프로시저는 테이블로의 엑세스(Access)를 우회할 수 있기 때문이다. 테이블로의 직접적인 엑세스인 INSERT,UPDATE,DELETE를 제한하고 저장 프로시저를 통해서만 접근하도록 할 수 있다.
다른 이유는 저장 프로시저로 비지니스 로직을 반영할 수 있다는 것이다. 또한, 하나의 모듈 단위로 작성된 저장 프로시저의 경우, 다른 프로시저들에서 재사용이 가능한 부분도 있다. 이러한 장점들이 나타나는 이유는 저장 프로시저가 일종의 프로그래밍으로 그 코드 내에서 흐름과 제어가 가능하다는 데에 이유가 있으며, 이것이 일반적으로 프로그램 코드 상에서 구현되었던 로직을 저장 프로시저에서도 구현하는 것이 가능하다는 데에 있다.
그리고, 저장 프로시저 또한 일종의 서브루틴이나 함수와 같은 모듈이므로, 각 기능 단위 별로 최소한으로 분리시키는 것이 코드의 재사용성을 높일 수 있게 된다.
제공 : DB포탈사이트 DBguide.net
DB 관리자는 SQL 서버로 부터 OLEDB를 통한 ad hoc 쿼리를 허락하지 말아야 한다.
OLEDB 제공자를 통한 SQL 서버로부터 ad hoc 쿼리들은 레지스트리에서 DisallowAdhocaccess 값을
설정함으로써 조정될 수 있다. name instance를 사용한다면 레지스트리 키 값에 각각의 서브 키 아래에
DisallowAdhocaccess 값을 '1'로 적용한다.
MSSQL2000
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\SQLOLEDB]
"AllowInProcess"=dword:00000001
"DisallowAdhocAccess"=dword:00000001
MSSQL2005
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\SQLNCLI]
"AllowInProcess"=dword:00000001
"DisallowAdhocAccess"=dword:00000001
로 변경을 합니다.