去年无聊的时候想到想玩一下根据ASCII表的排列顺序将字符串内的数值往前或者后移N个位,顺便看一下是T-SQL性能好还是用C#写CLR函数处理得快。结果是在50万行以下其实两者差距很小,当然这是在我的笔记本上测试的结果(I5双核四线程+8G内存+5600转的机械硬盘)。在100万行以上的时候用T-SQL基于集合的方式就体现出优势了,用了大概1分多钟就处理完100万行,而CLR用了4分多钟还没处理完。这里贴上当时的T-SQL代码。
if OBJECT_ID('tempdb..#tt') is not null drop table #tt if OBJECT_ID('tempdb..#t') is not null drop table #t if OBJECT_ID('tempdb..#ttt') is not null drop table #ttt if OBJECT_ID('tempdb..#tttt') is not null drop table #tttt select dbo.tblNumbers.id, abs(CHECKSUM(NEWID()))%62 as fld1, abs(CHECKSUM(NEWID()))%62 as fld2, abs(CHECKSUM(NEWID()))%62 as fld3, abs(CHECKSUM(NEWID()))%62 as fld4, abs(CHECKSUM(NEWID()))%62 as fld5, abs(CHECKSUM(NEWID()))%62 as fld6, abs(CHECKSUM(NEWID()))%62 as fld7, abs(CHECKSUM(NEWID()))%62 as fld8, abs(CHECKSUM(NEWID()))%62 as fld9, abs(CHECKSUM(NEWID()))%62 as fld10, abs(CHECKSUM(NEWID()))%62 as fld11, abs(CHECKSUM(NEWID()))%62 as fld12, abs(CHECKSUM(NEWID()))%62 as fld13, abs(CHECKSUM(NEWID()))%62 as fld14, abs(CHECKSUM(NEWID()))%62 as fld15, abs(CHECKSUM(NEWID()))%62 as fld16 into #tt from dbo.tblNumbers WHERE ID<= 100000; select [str], ROW_NUMBER() over(order by CHECKSUM(NEWID()))-1 as rn into #t from (select CHAR(ID+64) as [str] from dbo.tblNumbers where ID <= 26 union all select CHAR(ID+96) as [str] from dbo.tblNumbers where ID <= 26 union all select cast(ID-1 as varchar) as [str] from dbo.tblNumbers where ID <= 10) as t select tt.ID, left(t1.str + t2.str + t3.str + t4.str + t5.str + t6.str + t7.str + t8.str + t9.str + t10.str + t11.str + t12.str + t13.str + t14.str + t15.str + t16.str + REPLICATE(' ',1000),1000) as result into #tttt from #tt as tt join #t t1 on t1.rn = tt.fld1 join #t t2 on t2.rn = tt.fld2 join #t t3 on t3.rn = tt.fld3 join #t t4 on t4.rn = tt.fld4 join #t t5 on t5.rn = tt.fld5 join #t t6 on t6.rn = tt.fld6 join #t t7 on t7.rn = tt.fld7 join #t t8 on t8.rn = tt.fld8 join #t t9 on t9.rn = tt.fld9 join #t t10 on t10.rn = tt.fld10 join #t t11 on t11.rn = tt.fld11 join #t t12 on t12.rn = tt.fld12 join #t t13 on t13.rn = tt.fld13 join #t t14 on t14.rn = tt.fld14 join #t t15 on t15.rn = tt.fld15 join #t t16 on t16.rn = tt.fld16 checkpoint DBCC DROPCLEANBUFFERS DBCC FREESYSTEMCACHE('all') select dbo.ufn_MoveCharacterBackOrForwardByNPos(result,'forward',1), result from #tttt select dbo.ufn_clr_MoveCharacterBackOrForwardByNPos(result,'forward',1), result from #tttt select dbo.ufn_MoveCharacterBackOrForwardByNPos('oOv3lVLo4W3B44L7','forward',1) select dbo.ufn_clr_MoveCharacterBackOrForwardByNPos('oOv3lVLo4W3B44L7','forward',1) declare @int as int = 1; declare @cmd as varchar(max) = ''; declare @cmdd as varchar(max) = ''; declare @comand as varchar(max) = ''; declare @comandd as varchar(max) = ''; --select ISNULL(NULLIF(0,0),26) --select @@VERSION while @int <= 101 begin set @cmd = @cmd + 'substring(result,'+CAST(@int as varchar)+',1) as fld' +CAST(@int as varchar) + ',' set @cmdd = @cmdd + 'case when ASCII(fld' +CAST(@int as varchar) + ') between 65 and 90 then CHAR(ISNULL(NULLIF((ASCII(fld' +CAST(@int as varchar) + ')-64+1)%26,0),26)+64) when ASCII(fld' +CAST(@int as varchar) + ') between 97 and 122 then CHAR(ISNULL(NULLIF((ASCII(fld' +CAST(@int as varchar) + ')-64+1)%26,0),26)+64) when fld' +CAST(@int as varchar) + ' like ''[0-9]'' then CHAR(ISNULL(NULLIF((ASCII(fld' +CAST(@int as varchar) + ')-47+1)%10,0),10)+47) else fld' +CAST(@int as varchar) + ' end +'; if LEN(@cmd) > 7950 begin set @comand = @comand + cast(@cmd as varchar(max)) set @cmd = ''; end if LEN(@cmdd) > 7950 begin --print len(@cmdd) set @comandd = @comandd + cast(@cmdd as varchar(max)) set @cmdd = ''; end set @int = @int + 1 end --select @cmdd if LEN(@cmd) > 0 set @comand = 'select rtrim(result) AS result,' + @comand + cast(left(@cmd,len(@cmd)-1) as varchar(max)) + ' from #tttt as a' --select @cmdd, LEN(@cmdd) --select @comandd, LEN(@cmdd) if LEN(@cmdd) > 0 set @comandd = 'select rtrim(' + @comandd + cast(left(@cmdd,len(@cmdd)-1) as varchar(max)) + ') as final_str, result from (' + @comand + ') as t' --select @comandd EXEC( @comandd) --select * from #ttt CREATE TYPE udt_TwoCharacterColumn8000Long AS TABLE ( col1 VARCHAR(8000) NULL, col2 VARCHAR(8000) NULL ) alter PROCEDURE usp_UDTtest @t as udt_TwoCharacterColumn8000Long readonly as select * from @t declare @a as udt_TwoCharacterColumn8000Long insert into @a select 'abc', 'def' exec usp_UDTtest @a