MS-SQL Server

[소개]SQL Server 2012 (Denali)에서 추가된 비교 함수(Logical Functions)

임현수 2011. 8. 29. 21:06
SQL Server 2012에서 신규로 추가된 비교 함수를 소개드리고, 간단히 성능 테스트해 본 결과 공유드립니다.

1. IIF 함수
1-1. 소개
  - CASE 구문과 동일한 기능을 하며, true/false로 구분되어지는 구문을 CASE 구문에 비하여 단순한게 표현이 가능합니다.
  - 오라클의 DECODE함수와 동일하다고 보시면 됩니다.

1-2. 문법
  IIF ( boolean_expression, true_value, false_value )

1-3. 예제

  SELECT IIF ( 45 > 30, 1, 0 ) AS Result;

  -- 결과창

  



2. CHOOSE 함수
1-1. 소개
  - Index 값 위치의 변수값을 돌려주는 구문 (제한된 범위 내에서 선택이 필요한 경우 유용함)

1-2. 문법
  CHOOSE ( index, val_1, val_2 [, val_n ] )

1-3. 예제
  SELECT CHOOSE (1, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;
 
SELECT CHOOSE (2, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;
  SELECT CHOOSE (3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;

  -- 결과창

  



3. IIF 함수 성능 테스트
3-1. 테스트 목적
  - CASE 구문을 사용하는 것이 IIF 함수를 사용하는 것보다 빠르지 않을까라는 의심을 하게 됨.
  - 느리다고 생각한 이유는 내장함수더라도 함수니까 CASE 구문에 비해서 가독성이 높아지더라도 성능저하가 있을 것으로 추정해 봄.

3-3. 테스트 데이터 생성
-- tempdb로 이동

use tempdb

 
-- 테스트에 사용할 500만건 데이터를 생성하기
-- drop table iiftest

select top 5000000

        identity(int, 1, 1) as seqNo

,       a.name

,       a.xtype

,       a.crDate

,       a.refDate

into iiftest

from sys.sysobjects a

        cross join sys.sysobjects b

        cross join sys.sysobjects c

        cross join (select top 10 * from sys.sysobjects) d

-- 결과창




3-3. 테스트 쿼리 수행 (CASE 구문 2개와 IIF 함수 구문 1개 수행하여 수행시간 비교)
-- 쿼리 수행 시간 비교
set statistics time on

-- IIF 함수

select

        sum(

               iif(name='sysfiles1', iif((seqNo%2)=1, iif((seqNO%5)=1, 1, 0), 0), 0))

from iiftest with (readuncommitted)

-- CASE 구문_01

select sum(

               case

                       when name = 'sysfiles1' and (seqNo%2)=1 and (seqNO%5)=1 then 1

                       else 0

               end)

from iiftest with (readuncommitted)

-- CASE 구문_02

select sum(

               case

                       when name = 'sysfiles1' then

                                      case

                                             when (seqNo%2)=then (case when (seqNO%5)=1 then 1 else 0 end)

                                             else 0

                                      end

                       else 0

               end)

from iiftest with (readuncommitted)


-- 결과값



-- 소요시간 (거의 결과 시간이 동일함을 볼 수 있슴)


-- 실행계획 (동일한 실행계획이 나오는 것을 볼 수 있음)


3-4. 의견
  - IIF 함수가 CASE 구문에 비해서 성능이 좋지 않을 것 같다는 생각은 보기좋게 빗나갔습니다.
  - 실행계획이나 테스트 내용에서 볼 수 있듯이 CASE 구문과 동일하게 동작합니다.
  - Depth 제한등도 CASE 구문과 동일한 것으로 보아, 가독성만 높인 것으로 보입니다.
  - 개인적으로 2 depth 이상으로 들어가는 경우 IIF 구문보다는 CASE 구문을 권장하고 싶습니다. DECODE 구문을 사용해 본 경험상,
    남용하게 되면 가독력이 CASE 구문에 비해서 떨어지고, 오류를 범할 확률도 높아진다고 생각하기 때문입니다 .(개인적으로!!!)

4. 참고자료
  - IIF 함수 도움말
  - CHOOSE 함수 도움말
  - IIF 함수 블로그 글 01
  - IIF 함수 블로그 글 02