分拆函数性能优化
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
*/
补充：综合编程 , 其他综合 ,
