1 개요

2 Sub Query의 해석

3 Sub Query를 Join으로 바꾸기

4 상관 서브 쿼리

데이터를 연결하여 새로운 정보를 만드는 방법은 다양하지만 가장 일반적이면서 효율적이라고 볼 수 있는 것은 역시 Join이다. 대부분의 Sub Query는 Join으로 다시 작성[1]할 수 있다. 그러나 여러 가지 상황에서 서브쿼리를 사용함으로써 효율적인 액세스를 할 수 있는 경우가 많이 있다. 서브쿼리를 사용하는데 중요한 것은 메인쿼리가 먼저 수행되는냐 아니면 서브쿼리가 먼저 수행되느냐이다. 어떤 것이 먼저 수행되는지를 결정하는 것은 통계정보와 작성된 SQL문에 따라서 역시 액세스 경로가 틀려진다. 또한 어떤 방식으로 데이터를 연결할 것인지에 대한 요소도 수행 성능에 영향을 미친다.

다음의 예를 보자. 일반적인 경우다.

SELECT *
FROM Product
WHERE ProductID IN
    (SELECT ProductID
     FROM SalesOrderDetail
     WHERE SalesOrderID > 10000)
--'Product' 테이블. 스캔 수 139, 논리적 읽기 수 299, 물리적 읽기 수 0, 미리 읽기 수 0.
--'SalesOrderDetail' 테이블. 스캔 수 1, 논리적 읽기 수 227, 물리적 읽기 수 0, 미리 읽기 수 0.

이제 위의 쿼리를 약간 변형시켜보자. 약간만 변형시켰는데도 많은 변화가 일어난다.

SELECT *
FROM Product
WHERE ProductID IN
    (SELECT ProductID + '' --요기
     FROM SalesOrderDetail
     WHERE SalesOrderID > 10000)
--'SalesOrderDetail' 테이블. 스캔 수 1, 논리적 읽기 수 227, 물리적 읽기 수 0, 미리 읽기 수 0.
--'Product' 테이블. 스캔 수 1, 논리적 읽기 수 29, 물리적 읽기 수 0, 미리 읽기 수 0.

Product 테이블에서 논리적 읽기 수가 29 인 것은 Product 테이블이 모두 29 페이지에 들어가 있기 때문인 것이다. 확인을 해보자.

–-999 행
SELECT * FROM Product 
--'Product' 테이블. 스캔 수 1, 논리적 읽기 수 29, 물리적 읽기 수 0, 미리 읽기 수 0.
 
--60452행
SELECT ProductID + '' FROM SalesOrderDetail  WHERE SalesOrderID > 10000 
--'SalesOrderDetail' 테이블. 스캔 수 1, 논리적 읽기 수 227, 물리적 읽기 수 0, 미리 읽기 수 0.

위의 두 쿼리에서 두 번째 쿼리가 I/O가 더 적은 것을 볼 수 있다. 전체적인 비용을 비교해 볼 때도 두 번째 쿼리가 유리하다는 것을 알 수 있다. 그러나 첫 번째 쿼리의 실행계획을 보면 Stream Aggregate 연산자가 있음을 알 수 있다. 이 연산은 비교되는 ProductID의 Distinct 를 보장하기 위해서 작성된 것이다. 즉, 아래와 같이 IN 연산자는 같은 비교조건이 되기 때문에 집합 연산으로 Distinct를 보장한 것과 같다.

즉, WHERE 조건에서 다음과 같이 반복되는 것과 같다.

SELECT *
FROM Product
WHERE ProductID = 8 OR ProductID = 8 OR ProductID = 8 OR ProductID = 8 ……
OR ProductID = 9 OR ProductID = 9 OR ProductID = 9 OR ProductID = 9 ……

이것은 같은 조건을 계속해서 반복하게 되므로 이것은 Distinct한 값과 같다. 그러므로 Stream Aggregate가 사용되어 값을 유일하게 만든 것이다.

앞서 서브쿼리는 조인으로 바꿀 수 있다고 했다. 위의 쿼리를 조인으로 바꾸어보자.

SELECT A.*
FROM Product A INNER JOIN SalesOrderDetail B
ON A.ProductID = B.ProductID + ''
AND B.SalesOrderID > 10000
--'SalesOrderDetail' 테이블. 스캔 수 1, 논리적 읽기 수 227, 물리적 읽기 수 0, 미리 읽기 수 0.
--'Product' 테이블. 스캔 수 1, 논리적 읽기 수 29, 물리적 읽기 수 0, 미리 읽기 수 0.

실행계획과 논리적 읽기 수도 같다. 그러나 결과가 같은가? 결과가 엄청나게 많이 나왔을 것이다. 무엇일 잘못되었을까? 답은 이미 앞에서 말하였다. 서브쿼리의 결과가 60452 행이므로 1:M의 조인은 M만큼 결과가 나오는 것이 당연하다. 그럼 Distinct 하면 되지 않냐고 하여 아래와 같이 DISTINCT를 SELECT 절에 붙여 넣었다.

SELECT DISTINCT A.*
FROM Product A INNER JOIN SalesOrderDetail B
ON A.ProductID = B.ProductID + ''
AND B.SalesOrderID > 10000
--'Product' 테이블. 스캔 수 139, 논리적 읽기 수 299, 물리적 읽기 수 0, 미리 읽기 수 0.
--'SalesOrderDetail' 테이블. 스캔 수 1, 논리적 읽기 수 227, 물리적 읽기 수 0, 미리 읽기 수 0.

실행계획이 우리가 첫 번째 봤던 쿼리의 실행계획으로 변해 버렸다. 필자의 예상으로는 전체 결과를 리턴하는 시간이 더 오래 걸릴 것이라 예상했는데 빗나갔다. MSSQL Server는 조인을 하기 전에 Stream Aggregate로 중간 결과를 줄여버렸다. 제법 똑똑하다. 실제로는 위 쿼리는 다음과 같이 SQL을 작성하는 것과 같다.

SELECT A.*
FROM Product A INNER JOIN (SELECT ProductID 
                             FROM SalesOrderDetail
                             WHERE SalesOrderID > 10000
                             GROUP BY ProductID) B
ON A.ProductID  = B.ProductID

그러나 우리가 작성했던 두 번째 서브쿼리의 결과가 나오지 않았다. 그렇다면 옵티마이저의 비용 계산에 혼돈을 주기 위해 A쪽을 Suppressing하면 A쪽부터 읽어야 하고, 그렇게 되면 아래의 결과가 정확히 두 집합의 연결 횟수만큼인 139번 풀스캔으로 읽어야 한다는 결과가 나온다.

-- B 집합
SELECT ProductID 
FROM SalesOrderDetail
WHERE SalesOrderID > 10000
GROUP BY ProductID

SELECT A.*
FROM Product A INNER JOIN (SELECT ProductID 
                             FROM SalesOrderDetail
                             WHERE SalesOrderID > 10000
                             GROUP BY ProductID) B
ON A.ProductID + '' = B.ProductID
OPTION (FORCE ORDER, LOOP JOIN)
--'Worktable' 테이블. 스캔 수 998, 논리적 읽기 수 1139, 물리적 읽기 수 0, 미리 읽기 수 0.
--'SalesOrderDetail' 테이블. 스캔 수 1, 논리적 읽기 수 227, 물리적 읽기 수 0, 미리 읽기 수 0.
--'Product' 테이블. 스캔 수 1, 논리적 읽기 수 29, 물리적 읽기 수 0, 미리 읽기 수 0.

SELECT 139 * 999
--138861

I/O 양을 보면 B 집합을 한번 풀스캔 하였고, Worktable을 998번 풀스캔 했으므로 Product의 총 건수인 999와 정확히 부합한다. MSSQL Server의 옵티마이저는 B 집합을 999번 풀스캔 할 수는 없는 노릇이므로 Tempdb에 임시테이블을 만들어 저장한 한다. 이것은 Nested Loop Join을 해야 하므로 데이터를 계속 바인딩하지 않게 하기 위함이다. 만약 같은 테이블을 계속해서 풀스캔한다면 스캔하는 동안에 Lock이 걸려 다른 쿼리에도 영향을 줄 수 있기 때문이다. 상당히 비효율적이다.
물론 이러한 과정을 유도하기 위해서 힌트를 사용했다. 그러므로 힌트를 제거하면 Merge Join으로 풀릴 확률이 높다. 왜냐하면 Product 테이블을 ProductID에 클러스터드 인덱스가 잡혀 있기 때문에 이미 정렬된 상태이고, B집합도 GROUP BY로 인해 정렬이 될꺼라 예상되기 때문이다. 실제로 실행해 보면 Merge Join으로 풀리는 것을 알 수 있다.

SELECT A.*
FROM Product A INNER JOIN (SELECT ProductID 
                                              FROM SalesOrderDetail
                                              WHERE SalesOrderID > 10000
                                              GROUP BY ProductID) B
--'Product' 테이블. 스캔 수 1, 논리적 읽기 수 29, 물리적 읽기 수 0, 미리 읽기 수 0.
--'SalesOrderDetail' 테이블. 스캔 수 1, 논리적 읽기 수 227, 물리적 읽기 수 0, 미리 읽기 수 0.

우리가 원하는 대로 나왔다. 단지 Compute Scalar 연산자가 생겼는데 이것은 A.ProductID + '' 연산이므로 신경쓰지 않아도 된다. 이제 분석이 끝났다. 이제 선택의 길만 남았다. 현재는 데이터의 양이 얼마되지 않으므로 만족할 만한 성능을 낼 수 있다. 그러나 데이터가 커지고 SalesOrderID > 10000 조건이 자주 변하여 조건의 선택도가 떨어진다면 실행계획은 또 다시 변할 수 있다.
또한 더 많은 I/O를 하고도 응답시간이 더 빠른 경우도 있다. 이러한 것은 I/O의 비용 말고도 또 다른 팩터가 영향을 더 끼칠 수 있다는 것이다. 또한 서버의 상황에 따라서도 응답시간은 차이가 날 수 있다. Merge Join으로 실행계획을 선택하는 경우 풀스캔을 해야 하는데 중간에 한 행이라도 Lock을 걸고 있다면 I/O양을 줄이는 것보다 더 큰 손해를 감수해야 할 것이다. 그러므로 우리는 다음 두 쿼리를 최적으로 판단할 수 있다.

--1
SELECT *
FROM Product
WHERE ProductID IN
    (SELECT ProductID
     FROM SalesOrderDetail
     WHERE SalesOrderID > 10000)

--2
SELECT A.*
FROM Product A INNER JOIN (SELECT ProductID 
                             FROM SalesOrderDetail
                             WHERE SalesOrderID > 10000
                             GROUP BY ProductID) B
ON A.ProductID  = B.ProductID

두 쿼리의 실행계획은 같다.

상관서브쿼리는 메인 쿼리가 먼저 읽혀지고 서브쿼리로 연결되는 형태를 말한다. 즉, 메인 쿼리의 각각의 행마다 서브쿼리가 수행되는 형태의 쿼리를 의미한다. 다음과 같은 경우는 상관서브쿼리의 형태이다.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE 100 IN
   (SELECT royaltyper
   FROM titleauthor
   WHERE titleauthor.au_ID = authors.au_id)
--'authors' 테이블. 스캔 수 9, 논리적 읽기 수 18, 물리적 읽기 수 0, 미리 읽기 수 0.
--'titleauthor' 테이블. 스캔 수 1, 논리적 읽기 수 2, 물리적 읽기 수 0, 미리 읽기 수 0.

이것은 Suppressing과 같은 기법으로 서브쿼리를 먼저 수행시킬 수 없다. 연결고리가 되는 au_id가 메인쿼리에 있기 때문에 하위쿼리를 먼저 실행시킬 수가 없다. IN 연산자의 사용으로 Stream Aggregate 연산자를 다시 볼 수 있다. 하위 쿼리의 결과가 9개의 행이므로 authors 테이블은 9번 Index Seek 한 것을 볼 수 있다. 어쩔 수 없이 상관서브쿼리를 써야 할 경우도 있다. 그렇지만 대부분의 경우는 상관서브쿼리를 사용하지 않아도 되는 경우가 많이 있다. 다음과 같은 경우 대부분의 부하는 상관 서브 쿼리로 인해 생긴 것이다.

select (select count(*) + 1 from titles b where a.title_id > b.title_id) 'id', title_id
from titles a
order by 1
--'titles' 테이블. 스캔 수 19, 논리적 읽기 수 37, 물리적 읽기 수 3, 미리 읽기 수 0.
--이런 누적과 같은 종류의 상관서브쿼리나 부등호[* >, <, >=, <=]가 사용된 데이터의 연결은 
--커서를 사용한 솔루션이 더 빠른 경우가 대부분이다. 

I/O양을 보면 알 수 있듯이 전체 19번 풀스캔을 했다. 셀프조인이므로 한 번은 읽었으며, 나머지 18번은 SELECT의 서브쿼리에서 읽은 것이다. 현재의 예는 데이터가 얼마 되지 않아 성능차를 보이지는 않지만 데이터가 늘어날수록 성능차는 기아급수적으로 늘어난다. 이 경우는 일련번호를 만들기 위한 것이다. 차라리 이런 경우라면 임시테이블을 사용하여 IDENTITY() 함수를 이용하는 것이 더 낫다.