문자열 Split 하기 - 두번째 이야기
서비스 브로커를 조금 더 파야하는데 [던전 스트라이커] 2차 CBT가 코 앞이라 여유가 없군요.
각설하고 바로 오늘의 주제(?)로 넘어가겠습니다.
SP에서 배열 형태의 인자를 입력 받을 때 , (comma)와 같은 구분자를 사용한 문자열로 입력 받은 후 SP안에서 테이블로 변환해 사용하는 방법이 종종 사용됩니다.
이 과정에서 UDF를 사용할 수 있는데요. 제가 사용하던 함수는 아래 포스트에서 언급한 바가 있습니다.
XML을 사용하여 문자열 Split 하기 (2009/07/10 11:36)
- 1 ~ 10,000 까지의 정수가 INSERT되어 있는 Tally를 참조하는 방식의 IF_Split() 함수
- 전달받은 문자열을 REPLACE() 함수를 사용하여 XML 형식으로 변경한 후 XQuery를 사용하는 TF_Split() 함수
이렇게 두가지 방식을 소개했었습니다.
자 이제.. 제가 두 함수를 사용하며 겪은 문제를 공유할까 합니다.
첫째로 IF_Split() 함수는 Tally 테이블의 레코드 수가 많거나, IF_Split() 함수를 사용하는 SP의 호출 빈도가 높을 때.. 함수에 Compile Lock이 발생하는 심각한 결함이 있습니다.
증상은 SP의 Elapsed Time 증가, LCK_M_S 타입의 대기 증가로 나타나고.. sp_lock의 실행 결과에서 Compile Lock을 목격하게 됩니다.
둘째로 TF_Split() 함수는 인자로 전달되는 문자열을 구성하는 값들의 개수가 많을수록 심각한 성능 문제가 발생합니다. 대략 10,000 건 정도만 되어도 거의 응답을 받지 못할 수준;;
IF_Split() 의 문제를 막아낼 방법은 찾지 못했습니다.
거의 동일한 환경이더라도 어떤 서버에서는 Compile Lock이 발생하고 어떤 서버에서는 Compile Lock이 발생하지 않는데.. 어떤 값을 임계점으로하는지 파악해내지 못했습니다. Orz
대신 TF_Split() 의 문제는 해결할 수 있었습니다.
<X>value</X> vs. <X n="value" />
저도 한참동안 모르고 있었지만, 위 2가지 xml에 XQuery를 사용했을 때의 성능에는 큰 차이가 있습니다.
결론만 얘기하면 X element의 개수가 많을수록 전자는 후자에 비해 매우 나쁜 성능을 보입니다.
그런데 기존의 TF_Split()은 <X>value</X>와 같은 형태로 문자열을 변형한 후 XQuery를 사용했기 때문에 성능 문제가 있었던 거죠.
아래와 같이 TF_Split() 함수를 변경하면 성능 문제를 해소할 수 있습니다.
CREATE FUNCTION dbo.TF_Split (
@vchString varchar(max),
@vchDelimiter varchar(10)
)
RETURNS @tblResult table (
seq int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
string varchar(8000) NOT NULL
)
AS
BEGIN
DECLARE @xmlString xml;
SET @xmlString = CAST(
N'<X n="' + REPLACE(@vchString, @vchDelimiter, '"/><X n="') + '"/>'
AS xml);
INSERT @tblResult (string)
SELECT T.col.value('@n', 'varchar(8000)') AS n
FROM @xmlString.nodes('/X') T(col);
RETURN;
END
p.s
어제는 결혼 10주년 기념일이었습니다.
서른에 결혼해서 10년이 지나 마흔이된 지금.. 다시금 세월의 빠름을 실감하게 됩니다.
미래에 대한 설계, 지금 사는 현재에 대한 충실함.. 이 모두 중요한 일들인데.. 제가 그렇게 살고 있다고 자신있게 말하지 못하는게 부끄럽습니다. ^^;
하고자 하는 일에 좀 더 초점을 맞추고 집중해야할 시기인 것 같습니다.