보미네

TVF 는 2가지가 있습니다.

inline TVF (Table Value Function) 과 Multi-statement TVF 로 나뉘며,

1. inlene TVF 는 parameterized view 와 동일하며,

2. multi-statement TVF 는 Table 변수를 지정하고, 여러 문장으로 나눈 while 이라던지 필요한 연산을 하고 결과값을 table 변수에 넣어서 출력 합니다. 그럼 성능이 좋을까요? 성능이 좋을리 없겠죠 table 변수는 통계정보가 없기 때문에, SQL 엔진은 cadinality 정보를 얻어 optimize 과정에서 효율화를 못하게 됩니다. 상대적으로 비효율적인 플랜은 나쁜 성능으로 이어지기도 합니다. 고의로 이러한 과정이 필요한 경우도 있을 것이고 그것이 성능이 좋아지는 경우도 있겠지만 이 두 차이를 이해하신다면, 경우에 따라서 어떤것을 써야 할 지 선택 할 수 있으리라 봅니다.

TVF 가 주요하게 성능을 향상시키는 경우는 배열 형태의 변수를 다른 프로시저로 전달해야 하는데, 하나하나 procedure 를 호출하는 것 보다 한번에 다 담아서 한번에 Call 하게 된다면 엄청난 성능향상이 있겠죠 이런 여러 차이점이 있으니 이것이 이것보다 좋아요 라고 말하기 보다는 It depends 라고 말하게 되지요...

TVF 가 특별하게 resultset cache 기능이 없는한 TVF 로 만드는 것은 의미가 없어 보입니다. 프로시저로 통일하고 적절하게 table 형태의 변수가 필요한 경우에만 사용

쿼리의 실행 방법을 이해하고 필요없이 사용된 many-to-many join 을 query 재작성을 통해 one-to-many 조인을 이용해 값을 구하는 방법을 보여준다. one-to-many 조인은 temp 를 사용하지 않고, many-to-many join 은 temp 를 사용하기에 결과 양이 많아지면 i/o 성능에 문제가 생기기도 한다. 그러나, 실행계획을 이해하라고 글을 번역해 놓은것이지 성능을 높이기 위해 이러한 방법을 쓰라는 것은 아니다. 성능 튜닝은 다른 방법이 더욱 효율적일 수 있다.


Stream Aggregate

GROUP BY 절이 있을때, 보통 aggregate 를 수행하기 위해 SQL Server 는 두개의 물리 연산자를 가지고 있다. 하나는 저번주

에 보았던 stream aggregate 이다. 나머지 하나는 hash aggregate 이다. 이번 포스트에서는 stream aggregate 의 동작을 자

세히 살펴 보겠다.

The algorithm (알고리즘) -_- 이런건 번역하는게 이상하지만....

Stream aggregate 는 group by 컬럼(들)의 정렬된(sort) 데이터에 의존한다. 만약 하나 이상의 컬럼들이 그룹되면, 해당 모

든 컬럼들의 어떤 정렬 순서를 선택한다. 예를들면, 컬럼 a b 를 그룹하면, (a, b) 나 (b, a) 로 정렬할 수 있다. 이 정렬은

group by 컬럼을 위한 같은값을 가지는 행의 집합이 서로 인접하게 만들어 준다.

이것은 stream aggregate 알고리즘을 위한 슈도코드이다.

clear the current aggregate results
clear the current group by columns
for each input row
  begin
    if the input row does not match the current group by columns
      begin
        output the aggregate results
        clear the current aggregate results
        set the current group by columns to the input row
      end
    update the aggregate results with the input row
  end

예를들어 sum 값을 계산한다고 가정하면, 각각의 입력행에 대해 입력된 행이 현재 그룹에 속하면, (즉, 입력행의 group by

컬럼(들)이 이전행의 group by 결과와 같으면) 현재 계산중인 total 값에 적당한 값을 sum 해 업데이트 한다.  입력값이 새

로운 그룹에 속하면 (즉, 입력행의 group by 컬럼(들)이 이전행의 group by 결과와 같지 않으면) 현재의 sum 값을 출력하고

sum 값을 0 으로 초기화 하고 새로운 그룹값 계산을 시작한다.

Simple examples (간단한 예제)

create table t (a int, b int, c int)
 
select sum(c) from t group by a, b
  |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
       |--Stream Aggregate(GROUP BY:([t].[b], [t].[a]) DEFINE:([Expr1010]=COUNT_BIG([t].[c]), [Expr1011]=SUM([t].

[c])))
            |--Sort(ORDER BY:([t].[b] ASC, [t].[a] ASC))
                 |--Table Scan(OBJECT:([t]))

이것은 aggregate 하기전에 sort 가 필요하다는 점을 제외하면, 우리가 예전에 봤던 scalar aggregate SUM 과 기본적으로 같

은 플랜이다. (scalar aggregate 를 전체 행에 대해 하나의 큰 그룹으로 생각 할 수 있다. 그러므로 하나의 scalar

aggregate 는 행을 다른 그룹으로 넣을 필요가 없으므로, sort 가 필요 없다.)

Stream aggregate 는 입력된 행의 정렬순서를 유지한다. 그래서, 만약 group by 컬럼으로 정렬을 요청하거나 group by 컬럼

의 일부로 정렬을 요구하면, 다시 정렬할 필요가 없다.

select sum(c) from t group by a, b order by a
 
  |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
       |--Stream Aggregate(GROUP BY:([t].[a], [t].[b]) DEFINE:([Expr1010]=COUNT_BIG([t].[c]), [Expr1011]=SUM([t].

[c])))
            |--Sort(ORDER BY:([t].[a] ASC, [t].[b] ASC))
                 |--Table Scan(OBJECT:([t]))

위 실행계획은 바로위 실행계획에 비해 정렬컬럼만 반대로 바뀌었다. 이전 쿼리는 "(a,b)" 나 "(b,a)"로 정렬을 기술하지 않

았다. 이번에는 쿼리에 order by 절이 있고 컬럼 a 로 정렬을 요구했으므로 쿼리에서 컬럼 a 로 정렬을 먼저했다.

만약 우리가 적절한 인덱스가 있다면, 정렬이 필요없다.

create clustered index tab on t(a,b)
 
select sum(c) from t group by a, b
  |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
       |--Stream Aggregate(GROUP BY:([t].[a], [t].[b]) DEFINE:([Expr1010]=COUNT_BIG([t].[c]), [Expr1011]=SUM([t].

[c])))
            |--Clustered Index Scan(OBJECT:([t].[tab]), ORDERED FORWARD)

select distinct

다음 쿼리를 생각해 보자

select sum(distinct b) from t group by a
각각의 group 에서 컬럼 b 의 중복값을 제거가 필요하다. 이전 포스트에서, 이러한 방법중 하나인 sort distinct 를 이용하는 방법을 보았다. 하지만 적절한 인덱스가 있으면, stream aggregate 를 이용해서 중복값을 제거 할 수 있다.

  |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
       |--Stream Aggregate(GROUP BY:([t].[a]) DEFINE:([Expr1010]=COUNT_BIG([t].[b]), [Expr1011]=SUM([t].[b])))
            |--Stream Aggregate(GROUP BY:([t].[a], [t].[b]))
                 |--Clustered Index Scan(OBJECT:([t].[tab]), ORDERED FORWARD)

최상위의 aggregate 를 하는동안 최하위의 stream aggregate 는 중복값을 제거한다.

Multiple distincts (다중 distinct)

마지막으로 다음 쿼리를 생각해 보자

select sum(distinct b), sum(distinct c) from t group by a
 
  |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([t].[a])=([t].[a]), RESIDUAL:([t].[a] = [t].[a]))
       |--Compute Scalar(DEFINE:([t].[a]=[t].[a]))
       |    |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1018]=(0) THEN NULL ELSE [Expr1019] END))
       |         |--Stream Aggregate(GROUP BY:([t].[a]) DEFINE:([Expr1018]=COUNT_BIG([t].[c]), [Expr1019]=SUM([t].[c])))
       |              |--Sort(DISTINCT ORDER BY:([t].[a] ASC, [t].[c] ASC))
       |                   |--Clustered Index Scan(OBJECT:([t].[tab]))
       |--Compute Scalar(DEFINE:([t].[a]=[t].[a]))
            |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1020]=(0) THEN NULL ELSE [Expr1021] END))
                 |--Stream Aggregate(GROUP BY:([t].[a]) DEFINE:([Expr1020]=COUNT_BIG([t].[b]), [Expr1021]=SUM([t].[b])))
                      |--Stream Aggregate(GROUP BY:([t].[a], [t].[b]))
                           |--Clustered Index Scan(OBJECT:([t].[tab]), ORDERED FORWARD)

이전 글에서 살펴본 다중 scalar distinct 예제에서 살펴본 것과 같이, 이 쿼를를 두개의 부분으로 나누어 볼 수 있다. 각 distinct set 에서 하나는, distinct 한 c 값을 구하기 위해 sum(distinct c) 는 정렬이 필요하고, 반면 sum(distinct b) 값을 구할때는 clustered index 와 stream aggregate 를 이용해 정렬없이 구했다. 그리고 마지막 결과를 구하기 위해 group by 컬럼 a 에 대한 각각의 sum 값을 merge join 했다. merge join 을 사용한 것은 각각의 두 입력이 이미 group by 컬럼에 대해  이미 정렬되어 있기 때문이다. (compute scalar 연산인 [t].[a] = [t].[a]”는 내부 목적으로 필요하므로 무시하면 된다.)

aggregate 값은 unique를 보장하기 때문에 one-to-many merge join 이 사용되어야 하고, many-to-many 방법을 사용할 필요가 없다. 이것은 약간의 성능 문제이지 정확도 문제는 아니다. 쿼리를 명시적 join 으로 다시 작성하면 one-to-many join 을 얻을 수 있다.

select sum_b, sum_c
from
  (select a, sum(distinct b) as sum_b from t group by a) r
  join
  (select a, sum(distinct c) as sum_c from t group by a) s
  on r.a = s.a
 
  |--Merge Join(Inner Join, MERGE:([t].[a])=([t].[a]), RESIDUAL:([t].[a]=[t].[a]))
       |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [Expr1020]=(0) THEN NULL ELSE [Expr1021] END))
       |    |--Stream Aggregate(GROUP BY:([t].[a]) DEFINE:([Expr1020]=COUNT_BIG([t].[c]), [Expr1021]=SUM([t].[c])))
       |         |--Sort(DISTINCT ORDER BY:([t].[a] ASC, [t].[c] ASC))
       |              |--Clustered Index Scan(OBJECT:([t].[tab]))
       |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1022]=(0) THEN NULL ELSE [Expr1023] END))
            |--Stream Aggregate(GROUP BY:([t].[a]) DEFINE:([Expr1022]=COUNT_BIG([t].[b]), [Expr1023]=SUM([t].[b])))
                 |--Stream Aggregate(GROUP BY:([t].[a], [t].[b]))
                      |--Clustered Index Scan(OBJECT:([t].[tab]), ORDERED FORWARD)

Next ...
다음번 포스트에는 다른 aggregation 연산자에 대해서 살펴보겠다. (hash aggregate)

by Craig Freedman

Aggregation 은 큰 행집합을 하나의 작은 하나의 행집합으로 만드는 것을 말한다. 전형적인 Aggregate 펑션

은 count, min, max, sum avg 가 있다. SQL Server 는 STDEV 이나 VAR 도 지원한다.

나는 이 포스트를 여러개의 포스트로 나누어 쓸것이며, 이번 포스트에서는 "scalar aggregates" 를 다

룬다. Scalar aggregates 는 select list 에서 aggregate 펑션이 사용되고 group by 절이 없는 것을 말한다

Scalar aggregate 는 항상 하나의 단일행을 반환한다.

Scalar Aggregation
Scalar Aggregation 에 대한 하나의 연산자 : Stream aggregate 예제

select count(*) from t
  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
       |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
            |--Table Scan(OBJECT:([t]))

이 Aggregation 은 언어에서 처음 학습하는 "Hello World" 와 같은 것이다. stream aggregation든 단지 입

력된 행수를 더하고 그 결과를 반환한다. 이 stream aggregate 는 count([Expr1005]) 를 하는데 bigint 형

으로 계산한다. compute scalar 연산은 int 형태의 output 형태를 만들기 위해 필요하다. 주의할 것은

scalar stream aggregate 연산은 빈 입력셋을 주더라도 하나의 아웃풋을 내는 유일한 연산자 이다. (아마도

유일한 예제이다. 지금 다른것은 생각나지 않는다.)

min max sum 과 같은, 다른 간단한 scalar aggregate funtion 들을 어떻게 쓰는지 쉽게 볼 수 있다. 또한

다중 scalar aggregate 를 한번에 계산 할 수도 있다.

select min(a), max(b) from t
  |--Stream Aggregate(DEFINE:([Expr1004]=MIN([t].[a]), [Expr1005]=MAX([t].[b])))
       |--Table Scan(OBJECT:([t]))

이 계획은 t 라는 테이블을 읽어 a 컬럼의 최소값 b 컬럼의 최대값을 찾는다. 주의깊게 볼 것은 min max

aggregate 들은 결과를 형변환 할 필요가 없다는 것이다. 이러한 aggregate 들은 컬럼 a b 의 기본 type들

에 기초해 계산된다.

avg 와 같은 aggregate들은 실제로는 sum 과 count 로 부터 계산 되어진다.

select avg(a) from t
  |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE

[Expr1006]/CONVERT_IMPLICIT(int,[Expr1005],0) END))
       |--Stream Aggregate(DEFINE:([Expr1005]=COUNT_BIG([t].[a]), [Expr1006]=SUM([t].[a])))
            |--Table Scan(OBJECT:([t]))

위 average 값을 계산하기 위해 sum 과 count 로 부터 계산되어진다. CASE 표현은 0으로 나누지 않기 위해

서 필요하다.

반면 sum 은 이렇게 각각 계산되어질 필요가 없으나, 역시 count 가 필요하다.

select sum(a) from t
  |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END))
       |--Stream Aggregate(DEFINE:([Expr1005]=COUNT_BIG([t].[a]), [Expr1006]=SUM([t].[a])))
            |--Table Scan(OBJECT:([t]))

이 CASE 표현은 행이 없을때, 0 대신 null 을 표현하기 위해 필요하다.

Scalar Distinct

이제는 aggregate 에 DISTINCT 키워드를 추가하면 어떤일이 일어나는지 살펴보자.

select count(distinct a) from t
  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
       |--Stream Aggregate(DEFINE:([Expr1007]=COUNT([t].[a])))
            |--Sort(DISTINCT ORDER BY:([t].[a] ASC))
                 |--Table Scan(OBJECT:([t]))

이 쿼리는 컬럼 a 의 unique 값만의 행수를 계산하는 것이다. sort 연산자를 사용해 컬럼 a 의 중복되는 값을 제외했다.  sort 하면 중복된 값이 서로 인접해 있기 때문에 중복행을 제거하는 쉬운 방법이다.

distinct aggregate 연산들이 모두 중복값 제거가 필요한 것은 아니다. 예를들어, min max 는 distinct 가 있던 없던 상관 없다.

select min(distinct a), max(distinct b) from t
  |--Stream Aggregate(DEFINE:([Expr1004]=MIN([t].[a]), [Expr1005]=MAX([t].[b])))
       |--Table Scan(OBJECT:([t]))

만약, unique 인덱스가 있으면, 중복값 제거절차를 수행하지 않는다. 왜냐하면, unique 인덱스는 중복값이 없다는 것을 보장하기 때문이다.

create unique index ta on t(a)

select count(distinct a) from t

drop index t.ta
  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
       |--Stream Aggregate(DEFINE:([Expr1007]=COUNT([t].[a])))
            |--Index Scan(OBJECT:([t].[ta]))

Multiple Distinct

아래 쿼리를 고려해 보자.

select count(distinct a), count(distinct b) from t

위에서 보았듯이 "count(distinct a)"는 컬럼 a 에서 중복값을 제거해 구한다. 유사하게 "count(distinct b)" 컬럼 b 의 중복값을 제거해서 구한다. 그러나, 두 집합이 다르다. 어떻게 동시에 구할수 있을까? 답은 못한다 이다. 첫번째 aggregate 결과를 구하고, 나머지를 구하고, 그런후 두개의 결과를 하나의 결과 row 에 결합한다.

  |--Nested Loops(Inner Join)
       |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))
       |    |--Stream Aggregate(DEFINE:([Expr1010]=COUNT([t].[a])))
       |         |--Sort(DISTINCT ORDER BY:([t].[a] ASC))
       |              |--Table Scan(OBJECT:([t]))
       |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0)))
            |--Stream Aggregate(DEFINE:([Expr1011]=COUNT([t].[b])))
                 |--Sort(DISTINCT ORDER BY:([t].[b] ASC))
                      |--Table Scan(OBJECT:([t]))

원래의 쿼리에서 구개의 count 값을 구해 nested loops join 을 수행한다. 입력중 하나는 컬럼 a 의 중복값을 제거하고 나머지는 컬럼 b 의 중복값을 제거한다. 이 nested loop join 은 join 조건이 없다. 이것은 cross join 이다. 왜냐하면 두개의 input rows 는  각각 하나의 결과를 만들고 그것의 cross join 결과 역시 단일 row 이다.

만약 두개이상의 distinct aggregate 연산이 다른 컬럼에 있으면, 하나 이상의 cross join 이 사용된다. 이러한 계획은 non-distinct 와 distinct aggregate 가 같이 섞여 있어도 사용된다. 이럴경우 cross join 의 한쪽에는 sort 가 없다.

다음은 group by 가 포함된 aggregation 이다.

by Craig Freedman