[DBMS] MSSQL 최적화에 관련한 경험담

제가 대용량 데이터(6억 건 이상)에 대한 검색 및 텍스트 비교를 수행해야 할 일이 있었습니다.
검색 자체는 파티션 나누고 어쩌고한 것을 넘겨 받아서 별 문제가 없었는데
텍스트 비교가 어마무지하게 느린 겁니다.

이 텍스트 비교가 어떤 식인가 하니

    1. 16진수가 텍스트로 저장되어 있다.
    2. 저장된 텍스트를 1글자씩 읽어서 비교 문자열의 1글자씩과 비교한다. (총 12만회)
    3. 비교는 16진수로 변환하여 차이를 구해서 차이가 6이상일 때, 3이상일 때, 그 외의 경우로 처리한다.
    4. 16진수 변환 함수는 사용자 정의 함수이며, 1글자씩 비교하는 함수도 사용자 정의 함수이다.
    5. 1글자씩 비교하는 함수에서 16진수 변환 함수를 호출하여 비교한다.

이런 조건으로 단독 수행을 했을 때도 4초 이상이 걸렸습니다.
짧은 건 2초, 긴 건 6초였지만 평균적으로 4초...
문제는 150건은 동시에 검색해야 하는 게 목표라는 겁니다.
50건만 동시에 검색해도 결과가 평균 40초대...

이것을 1초 내외(50건 동시 검색)로 줄인 일인데
처음에는 인덱스를 다시 건다, 파티션을 나눈다 어쩐다 해 봤지만
시간(6억건이 넘는 데이터를 인덱스 걸고 파티션 나누고... ㅜㅜ)만 걸리고 다 실패. ㅜㅜ

결국 함수를 어떻게 최적화를 해보려고 파고 들었는데
도통 길이 안보이다가 우연히 ascii() 함수를 발견합니다.
16진수 변환의 목적이 두 수의 차이니까 사용자 정의 함수 대신 ascii() 함수를 쓰면 빠르겠다는 생각!!!
그래서 비교할 두 수(1문자)를 ascii()로 변환('A' 보다 작으면 7 가산)해서 1초 내외로 줄였습니다.

이걸로 끝이었으면 여기다 안 썼을 텐데 실제로 이 글을 쓰는 이유는 지금부터입니다.

기존
set @err = abs(dbo.Hex1ToDec(substring(@org_dna, @i, 1)) - dbo.Hex1ToDec(substring(@cmp_dna, @i*8-7, 1)))

변경
set @org_char=upper(substring(@org_dna, @i, 1))
set @cmp_char=upper(substring(@cmp_dna, @i*8-7, 1))
if(@org_char<'A')
begin
            set @org_int=ascii(@org_char)+7
end
else
begin
            set @org_int=ascii(@org_char)
end
if(@cmp_char<'A')
begin
            set @cmp_int=ascii(@cmp_char)+7
end
else
begin
            set @cmp_int=ascii(@cmp_char)
end
set @err = abs(@org_int - @cmp_int)

한 줄이면 되던 것이 여러 줄로 늘어나는 일이 생겨서
16진수 변환 함수를 고쳐서 사용하기로 했습니다.
기존의 변환 함수는 case when을 써서 반환값을 넘기는 방식이었기에
ascii()함수를 써서 반환값을 넘기는 방식으로 바꾸고 기대에 차서 돌렸더니...
4초던 결과가 3초로 줄었습니다.

그래서 결국 한 줄로 줄이는 계획은 포기.

결론은

*********************************

사용자 정의 함수 안에서 또 사용자 정의 함수를 사용하는 것은 금물!!!

*********************************