SQL문의 자료형 불일치에 대한 성능 저하

안녕하세요. 유일환입니다.
오늘은 자료형을 맞쳐서 SQL을 실행해야 하는 중요성에 대해서 적어볼까 합니다.
 
아마 많은 분들이 기본적으로 문자는 문자끼리 숫자는 숫자끼리 비교를 해야 한다는 사실을 알고 계실겁니다. 어느 튜닝문서나 보면 많이 나오는 말입니다. SQL 서버에는 자료형에 대해 우선순위가 있습니다. 숫자가 문자보다 높은 우선순위를 가지고 있습니다. 그러므로 문자와 숫자를 비교하게 되면 문자는 숫자로 변경되어 집니다.

즉 A란 테이블의 col1은 문자형이고 인덱스가 만들어져 있을 때, 다음과 같은 경우 어떤 문제가 있을까요?

SELECT * FROM A WHERE A.col1 = 30

이 경우 A.col1 = 30 이라고 숫자형의 변수값이 조건에 들어왔으므로 SQL Server의 옵티마이져는 A.col1을 숫자형으로 변경시켜서 30인 값을 찾아내게 됩니다. 이것은 SQL Server의 자료형 우선순위에서 숫자가 문자보다 높기 때문에 발생되는 일입니다. 이 결과 A테이블의 col1 인덱스는 사용도 못할 뿐더러, col1을 모두 숫자로 변경하는 오버헤드까지 추가가 됩니다.

그러므로 다음과 같이 SQL을 변경해야 하죠.

SELECT * FROM A WHERE A.col1 = CONVERT(nvarchar(10), 30)

이런식으로 변수를 문자로 변경해서 비교를 하는 것이죠. 여기까지는 많은 분들이 알고 계시는 내용일 것입니다. 대부분 숫자형과 문자형에 대해서만 이렇게 알고 계신데, 문자형과 문자형의 경우도 문제가 될 수 있습니다. 오늘은 문자형과 문자형의 경우에 대해서 경험을 적어볼까 합니다.
 
현재 지금 저희가 개발하고 있는 환경은 자바와 SQL Sever입니다. SQL을 실행하기 위한 드라이버는 jtds-1.2.jar 라는 오픈소스를 사용하고 있습니다. 제가 여기 처음 와서 일을 할때 가장 많았던 문제가, 쿼리분석기로 실행을 하면 느리지 않던 SQL이 자바를 이용해서 SQL을 실행하면 느리다는 점이었습니다.
 
문제점을 찾기 위해서 쿼리분석기에서 실행했을 때의 실행계획과 자바에서 실행했을 때의 실행계획을 트레이스 해서 두 실행계획의 차이를 찾아보았죠.두 SQL의 실행계획이 틀리더군요. 그래서 자바에서 실행되는 SQL에 여러가지 처리를 시도해 보았습니다.
조인방법을 변경해 본다거나, 인덱스를 변경한다거나 하는 방식으로 쿼리분석기에서 실행한 SQL과 동일한 실행계획이 만들어지도록 자바의 SQL을 계속 수정해 나갔습니다.

그 결과 쿼리분석기에서 실행한것과 동일하게 좋은 성능을 낼 수 있도록 자바의 SQL을 변경할 수 있었습니다.

하지만, 근본적인 문제를 찾지 않고서는 계속해서 이런 작업을 해야 할거 같았습니다. 그래서 문제가 무엇일지 계속해서 연구해 보았습니다. 그 결과 프로필러를 통해 자바에서 SQL로 SQL문과 매개변수를 넘길 때 매개변수의 자료형에 문제가 있다는 사실을 알게 되었습니다.

다음은 매개변수가 넘어오는 부분입니다.

 N'@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000)', N'200606', N'200606', 15, N'1108'

보시다시피, 모든 문자형 자료가 nvarchar(4000) 자료형으로 해서 변수가 넘어오고 있습니다. 즉, sp_executesql로 SQL이 실행되는데, 매개변수의 문자형은 모두 nvarchar(4000)이 되는 것이죠. 하지만 같은 문자형인데 이것이 영향을 미칠까라는 의문점에
쿼리분석기에서는 빠르지만 자바에서 느린 SQL을 찾아서 테스트를 해봤습니다.

...
FROM    테이블
WHERE   컬럼1 = '200601'
...

이와 같은 SQL을 다음과 같이 변경해서 실행해봤습니다.
...
FROM    테이블
WHERE   컬럼1 = CONVERT(nvarchar(4000), '200601')
...

결과는 자바에서 실행한것처럼 느린 실행계획이 만들어져 실행이 되었습니다. 즉 매개변수의 자료형을 잘못 정해준것이 실행계획의 변화를 가져오고 이것이 문제를 일으킨 것입니다. 보통 SQL에서는 이와 같은 이유로 실행계획이 변경이 되어도 성능에 문제가 되지는 않더군요. 하지만 굉장히 복잡한 SQL의 경우 이런 자료형의 불일치로 인해 실행계획이 변경이 되고 그로 인해 성능이 굉장히 많이 다운되더군요.
 
실행계획을 보면 정상적인 경우 WHERE조건에서 걸러내야 하는 부분이, FILTER 조건이 추가되어서 걸르게 되더군요. 또한 프로필러를 이용해 CPU사용시간을 보면 CPU사용시간이 굉장히 늘어나서 성능이 저하되더군요. 

그래서, 자바에 있던 SQL을 다음과 같이 실행해서 처리해 보았습니다.

...
FROM    테이블
WHERE   컬럼1 = CONVERT(nchar(6), @P1)
...

즉, 자바에서 @P1은 자료형을 nvarchar(4000)으로 해서 넘기지만, 다시 SQL내에서 이것을 nchar(6)으로 변경함으로서 원래 컬럼1에 대한 아무 변형이 없도록 시도를 한 것입니다. 그결과 좋은 성능을 내는 SQL이 되더군요. 이 외에도 쿼리분석기에서 여러가지 테스트를 해보았습니다.

...
FROM    테이블
WHERE   컬럼1 = CONVERT(nvarchar(4000), '200601')
...

이 SQL의 자료형 nvarchar(4000)을 nvarchar(3000), nvarchar(2000), .... nvarchar(1000), ... nvarchar(100) 이런식으로 변경을 하면서 시도를 해봤습니다. 그 결과 특정크기 이상에서만 실행계획이 변경이 된다는 것입니다. 예를 들어 컬럼1은 nchar(6)이고, 매개변수가 nvarchar(800)일 때 까지는 실행계획이 동일합니다. 하지만, 매개변수가 nvarchar(801)이 되면서부터 실행계획이 변경이 됩니다.

(예를 든것입니다. 정확히 800이지는 않습니다. 경우에 따라 틀린거 같습니다.)
즉, 같은 문자형에도 우선순위가 있는데 nvarchar가 nchar보다는 우선순위가 높습니다.

하지만, nchar로 만들어진 인덱스가 nvarchar형의 매개변수가 들어왔다고 해서, nchar를 nvarchar로 변경을 시키지는 않습니다.
같은 문자형이기 때문이지요. 하지만, 원래 컬럼이 nchar(6)인데, 매개변수가 nvarchar(4000)과 같이 자릿수가 틀리게 해서 동적SQL이 실행이 되면, 실행계획은 변경이 되고 그로 인해 SQL이 느려진다는 겁니다.

즉, 문자형과 문자형을 비교할 때도 char냐, varchar냐, 자릿수가 어떻게 되느냐에 따라 실행계획이 틀릴 수 있다는 것입니다. 이를 위한 가장 좋은 해결 방법은 비교할 매개변수를 원래 테이블의 자릿수에 맞추어서 넘겨줘야 합니다. 하지만 이는 드라이버에 따라서 다양한거 같더군요. 만약에 드라이버가 SQL 서버의 자료형과 맞쳐서 변수를 넘겨주는 기능이 있다면 이는 걱정하지 않아도 되겠죠.

프로시저를 사용할 경우도 걱정하지 않아도 됩니다. 프로시저 내에서 자료형을 실제 테이블과 맞쳐서 선언하면 되니까요. 하지만 실제 개발환경은 다양하니까, 그 다양한 환경에 맞추어 방법을 찾아서 수행해야 할거 같습니다. 혹시나, 쿼리분석기에서는 빠르던 SQL이 실제 소스에 올려놓으니까 느린경우에 대해 처리 방법의 하나로서 참고가 될거 같아 적어 봤습니다.

출처 : http://www.dbguide.net/dbqa/dbqa120001.jsp?divcateno=244&divcateno_=244&idx=807&mode=view

아래는 SQL Server 2005에서 사용하는 데이터 형식의 우선순위를 나타낸 목록이다.

1.     사용자정의데이터형식(가장높음)

2.     sql_variant

3.     xml

4.     datetime

5.     smalldatetime

6.     float

7.     real

8.     decimal

9.     money

10.  smallmoney

11.  bigint

12.  int

13.  smallint

14.  tinyint

15.  bit

16.  ntext

17.  text

18.  image

19.  timestamp

20.  uniqueidentifier

21.  nvarchar(nvarchar(max) 포함)

22.  nchar

23.  varchar(varchar(max) 포함)

24.  char

25.   varbinary(varbinary(max) 포함)

26.   binary(가장낮음)