CLR 을 이용한 튜닝 입니다. 이번 쿼리는 약 9배 정도의 성능향상이 있습니다.
아래 쿼리가 기존의 fn_split 을 사용할 경우 9초, CLR 을 이용한 fn_split 는 1초 입니다.
그런데 이번에는 약간의 주의사항이 있습니다. 적용시 개발과 함께 살펴봐야 할 듯 합니다.
주의사항
아래 참고 URL 을 읽어보니 table return function 의 경우 unicode 만 리턴 합니다. 만약 non-unicode 로 반드시 받아야 한다면
1) sql 이나 application 에서 결과값을 varchar (8000) 으로 convert 해 써야 합니다.
https://msdn.microsoft.com/en-us/library/ms131103(v=SQL.100).aspx
2) 들어오는 입력 인자는 8000자를 넘어도 상관 없지만 나오는 출력 컬럼은 4000자를 넘지 못합니다.
테스트 쿼리는 아래와 같습니다.
set nocount on
declare @source varchar(8000) = 'jfkdslafjldsa,fdsaf,dsa,f,sda,fd,,f,,asdf,f,ds,f,a,sfd,ds,af,dsa,fdsa,f,ght,wh,w,jh5,j,j,6w,j6,j6,j6,5t,agg,,,,' declare @delimiter char(1) = ',' select * from dbo.fn_split (@source, @delimiter) go
declare @source varchar(8000) = '' declare @delimiter char(1) = '' select * from dbo.fn_split (@source, @delimiter) go
declare @source varchar(8000) = 'a' declare @delimiter char(1) = '' select * from dbo.fn_split (@source, @delimiter) go
declare @source varchar(8000) = '' declare @delimiter char(1) = ',' select * from dbo.fn_split (@source, @delimiter) go
declare @source varchar(8000) = 'jfkdslafjldsa,fdsaf,dsa,f,sda,fd,,f,,asdf,f,ds,f,a,sfd,ds,af,dsa,fdsa,f,ght,wh,w,jh5,j,j,6w,j6,j6,j6,5t,agg,,,,' declare @delimiter char(1) = ',' declare @result varchar(8000) = '' declare @cnt int = 1 while (1=1) begin select @result = item from dbo.fn_split (@source, @delimiter) set @cnt = @cnt + 1 ; if @cnt >= 30000 break end
|
기존 프로그램과 clr 프로그램을 살펴 보겠습니다.
기존 function | /* @source 를 @delimiter로 구분자로 하여 테이블 형태로 반환 */ CREATE function dbo.fn_split ( @source varchar(8000), @delimiter char(1) ) returns @array table(Item varchar(8000)) as begin if @source <> '' begin declare @Item varchar(8000) declare @thisPos int, @nextPos int, @totalLength int
select @thisPos = 1, @nextPos = charindex(@delimiter, @source, 1), @totalLength = len(@source)
while @thisPos <= @totalLength and @nextPos > 0 begin If @nextPos > @thisPos begin set @Item = substring(@source, @thisPos, @nextPos-@thisPos) if len(@Item) > 0 insert into @array select @Item end
select @thisPos = @nextPos + 1, @nextPos = charindex(@delimiter,@source,@thisPos) end
set @Item = substring(@source, @thisPos, @totalLength-@thisPos + 1) If len(@Item) > 0 insert into @array select @Item
end return end |
CLR | using System; using System.Data; using System.Collections; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions { [SqlFunction(Name = "fn_split", FillRowMethodName="FillRow", TableDefinition = "item nvarchar(4000)")] public static IEnumerable fn_split(SqlString source, SqlChars delimiter) { if (delimiter.Length == 0) { return new string[1] { source.Value }; } //return source.Value.Split(delimiter[0]); return source.Value.Split(new[]{delimiter[0]}, StringSplitOptions.RemoveEmptyEntries);
} public static void FillRow (object row, out SqlString str) { str = new SqlString((string)row); } } |
http://www.codeproject.com/Articles/680161/Getting-Started-With-SQL-Server-CLR-User-Defi
https://msdn.microsoft.com/en-us/library/ms131103.aspx
https://msdn.microsoft.com/ko-kr/library/ms131053(v=sql.120).aspx