分拆函数性能优化

use Tempdb

GO

if OBJECT_ID ( 'F_Str1' ) is not null

drop function F_Str1

go

/*

此函数缺点，字符长度越长性能越低。

*/

create FUNCTION dbo . F_Str1 (

@Str NVARCHAR ( 4000 ), -- 需要处理的字符串

@Condition nvarchar ( 1000 ) -- 保留字符串如 :N'%[0-9a-zA-Z]%', 注意这里需要有 []

)

RETURNS NVARCHAR ( 4000 )

AS

BEGIN

if charindex ( '[^' , @Condition )=0

set @Condition = STUFF ( @Condition , charindex ( '[' , @Condition )+ 1 , 0 , '^' ) --这一段是为了与后面函数的条件统一

declare @i int

set @i = PATINDEX ( @Condition , @Str )

WHILE @i > 0

SELECT @Str = STUFF ( @Str , @i , 1 , '' ), @i = PATINDEX ( @Condition , @Str )

RETURN @Str

END

go

IF OBJECT_ID ( 'F_Str2' ) IS NOT NULL

DROP FUNCTION F_Str2

go

CREATE FUNCTION dbo . F_Str2 (

@Str NVARCHAR ( 4000 ), -- 需要处理的字符串

@Condition nvarchar ( 1000 ) -- 保留字符串如 :N'%[0-9a-zA-Z]%', 注意这里需要有 []

)

RETURNS NVARCHAR ( 4000 )

AS

BEGIN

DECLARE @s NVARCHAR ( 4000 ), @s2 NVARCHAR ( 4000 )

, @s3 NVARCHAR ( 2 ), @s4 NVARCHAR ( 2 )

, @a NVARCHAR ( 4000 ), @b NVARCHAR ( 4000 )

, @i INT , @j INT , @k INT

SELECT @s = '' , @s2 = '' , @i = 1 , @k = len ( @Str ), @j = CEILING ( @k * 1.0 / 2 )

SELECT @a =LEFT( @Str , @j ), @b =RIGHT( @Str , @k - @j )

WHILE @i <= @j

begin

SELECT @s3 = SUBSTRING ( @a , @i , 1 ), @s4 = SUBSTRING ( @b , @i , 1 )

if @s3 LIKE @Condition

SET @s = @s + @s3

if @s4 LIKE @Condition

SET @s2 = @s2 + @s4

SET @i = @i + 1

end

RETURN @s + @s2

END

GO

--- 以保留数字和字母（字符长度为 4000 ）为例，测试结果：

use tempdb

go

select @@VERSION--测试环境

/*

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 6.1 <X86> (Build 7600: )

*/

go

IF OBJECT_ID ( 'Tempdb..#T' ) IS NOT NULL

DROP TABLE #T

CREATE TABLE #T( Col NVARCHAR ( 4000))

go

DECLARE @s NVARCHAR ( 4000)

SET @s= REPLICATE ( N' 中 A 国 B' , 2000) -- 改動一下測試數據

INSERT #T SELECT @s

go 10 --10 条记录

GO

-- 清空缓存计划

DBCC FREEPROCCACHE -- 从过程缓存中删除所有元素

DBCC DROPCLEANBUFFERS -- 从缓冲池中删除所有清除缓冲区。

DBCC FREESYSTEMCACHE( 'Tempdb' ) -- 从所有缓存中释放所有未使用的缓存条目

-- 多执行几次取最小值

--F_str1 函数

DECLARE @dt DATETIME = GETDATE ()

SELECT LEN ( dbo. F_str1( Col, N'%[0-9a-zA-Z]%' )) AS [ 檢查長度 ], dbo. F_str1( Col, N'%[0-9a-zA-Z]%' )

FROM #T

SELECT DATEDIFF ( ms, @dt, GETDATE ())

-- 时间为

/*

21466 ms

*/

go

--F_str2 函数

DECLARE @dt DATETIME = GETDATE ()

SELECT LEN ( dbo. F_str2( Col, N'%[0-9a-zA-Z]%' )) AS [ 檢查長度 ], dbo. F_str2( Col, N'%[0-9a-zA-Z]%' )

FROM #T

SELECT DATEDIFF ( ms, @dt, GETDATE ())

/*

496 ms -- 效率提高了44倍多

*/

-- 当字符串长度越大时，效率提高越明显。

以下针对性过滤写法，利用指定排序规则，过滤以上测试数据的中文字符

以下以排序 Latin1_General_CI_AS 为例，支持数字与字符不支持繁体和简体字 , 当不支持时会显示为 ”?” ，利用这一特性可以这样写：

GO

if OBJECT_ID ( 'F_Str3' ) is not null

drop function F_Str3

go

create FUNCTION dbo. F_Str3(

@Str NVARCHAR ( 4000) --

)

RETURNS NVARCHAR ( 4000)

AS

BEGIN

set @Str= REPLACE ( cast ( @Str as varchar ( 8000)) COLLATE Latin1_General_CI_AS, '?' , '' ) -- 取巧方法

RETURN @Str

END

GO

-- 测试结果

--F_str3 函数

DECLARE @dt DATETIME = GETDATE ()

SELECT LEN ( dbo. F_str3( Col)) AS [ 檢查長度 ], dbo. F_str3( Col) FROM #T

SELECT DATEDIFF ( ms, @dt, GETDATE ())

/*

266 ms

*/

闹剧总结