用SQL Server同学请进 customized replace function

jl162401

目前有个Sql server的SQL语句调用了一个customized replace function, such as this:

SELECT 'D' AS RECORDTYPECODE,
a.[Id]
,a.[Col1]
,a.[Col2]
,a.[Col3]
,%DBNAME%.ReplaceSplVarcharsBICD(a.[Col4])
,%DBNAME%.ReplaceSplVarcharsBICD(a.[Col5])
,%DBNAME%.ReplaceSplVarcharsBICD(a.[Col6])
,a.[genID]
FROM %DBNAME%.TableABC a

TableABC 应该有5千万条以上的数据 全部文本导出大概是1个GB

Customized function 逻辑如下:

USE [BPSUAT_BICD]
GO
/****** Object:  UserDefinedFunction [dbo].[ReplaceSplVarcharsBICD]    Script Date: 04/10/2013 16:08:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE   function [dbo].[ReplaceSplVarcharsBICD](@value varchar(8000))RETURNS varchar(8000)
as
begin
declare @result varchar(8000);

-- This function is used to Replace some Special characters with space for varchar and char datatypes.

-- Replacing for Enter, newline
select @result = REPLACE(REPLACE(REPLACE(@value, CHAR(10) + CHAR(13), ' '),CHAR(10),' '), CHAR(13),' ');

--Replacing other non printable characters from Hex 00 to Hex'1F' and  Hex'7F'

select @result = REPLACE(@result, CHAR(0),' ');
select @result = REPLACE(@result, CHAR(1),' ');
select @result = REPLACE(@result, CHAR(2),' ');
select @result = REPLACE(@result, CHAR(3),' ');
select @result = REPLACE(@result, CHAR(4),' ');
select @result = REPLACE(@result, CHAR(5),' ');
select @result = REPLACE(@result, CHAR(6),' ');
select @result = REPLACE(@result, CHAR(7),' ');
select @result = REPLACE(@result, CHAR(8),' ');
select @result = REPLACE(@result, CHAR(9),' ');

--select @result = REPLACE(@result, CHAR(10),' ');
select @result = REPLACE(@result, CHAR(11),' ');
select @result = REPLACE(@result, CHAR(12),' ');
--select @result = REPLACE(@result, CHAR(13),' ');
select @result = REPLACE(@result, CHAR(14),' ');
select @result = REPLACE(@result, CHAR(15),' ');
select @result = REPLACE(@result, CHAR(16),' ');
select @result = REPLACE(@result, CHAR(17),' ');
select @result = REPLACE(@result, CHAR(18),' ');
select @result = REPLACE(@result, CHAR(19),' ');
select @result = REPLACE(@result, CHAR(20),' ');


select @result = REPLACE(@result, CHAR(21),' ');
select @result = REPLACE(@result, CHAR(22),' ');
select @result = REPLACE(@result, CHAR(23),' ');
select @result = REPLACE(@result, CHAR(24),' ');
select @result = REPLACE(@result, CHAR(25),' ');
select @result = REPLACE(@result, CHAR(26),' ');
select @result = REPLACE(@result, CHAR(27),' ');
select @result = REPLACE(@result, CHAR(28),' ');
select @result = REPLACE(@result, CHAR(29),' ');
select @result = REPLACE(@result, CHAR(30),' ');
select @result = REPLACE(@result, CHAR(31),' ');
select @result = REPLACE(@result, CHAR(127),' ');

--Replacing the pipe symbol
select @result = REPLACE(@result, CHAR(124),' ');

--Repalcing the NULs
select @result = Nullif(@result,'');

--Removing the Right Spaces
select @result = RTRIM(@result);

--Removing the Left Spaces
select @result = LTRIM(@result);


return @result;

end


我的问题如下：
1. 这个函数本身有什么方法可以提高效能
2. 这个函数每一次调用 是读字段文本(例如col4） 仅仅一次 还是 N多次
3. 直接导出 用C或者sed/perl等替换应该会更快 但是100倍快 还是几倍快

jl162401

顶起

huaxianz

楼住的问题很有营养

1. 典型的CLR Scalar Funtion的应用。用T-SQL自带的string function太卡了。
2. 问题没看懂。我只看到只有一次引用，还是我理解错误？
3. 同1。看过几本书，印象中cursor based应用平均来讲效率高一些，但低于10倍。但是你这个例子也许能达到也说不准。看到这个function我都要哭了，居然能写成这样。总之，根本就不用出SQL Server，导入导出也是overhead。

huaxianz

又重新看了一下，明白楼主第二个问题的意思了。答案是一次。前一个output是后一个的input。

jl162401

huaxianz 发表于 2013-4-10 19:41
楼住的问题很有营养

1. 典型的CLR Scalar Funtion的应用。用T-SQL自带的string function太卡了。 ...

典型的CLR Scalar Funtion的应用

这个如何理解 我在网上看到很多人提到CLR

这是一个印度公司写的函数 我觉得实在太慢了 希望他们改 兄弟不编程很多年了 而且也没用过SQL server

有两种方式： 改写函数本身
                      存成文本 然后处理

不知道函数本身的改进方法如何？

jl162401

google了一下 有点明白了

CLR， 你的意思是用VB或者C#写的一个function，

huaxianz

典型的CLR Scalar Funtion的应用:
就是说拿出你的.net programming的经验来写scalar function。因为对于scalar来说，都是row-by-row的cursor like operation,那是比如C#那种procedural programming language最擅长的，效率上和编写难易度上都不是一个数量级的;相反set based operation是SQL的天下，甩procedural几条街。
就单你这个例子来讲，不用说.net上的String class比T-SQL string function强多少，光一个支持regular expression就干掉T-SQL.这就是为什么微软从SQL Server 2005引入了CLR integration的概念，简单的说在SQLOS上内置了一个managed code的execution environment。
本来想找一个官方的architechure diagram，一时也没找到。具体的可以参考http://msdn.microsoft.com/en-us/library/ms131102.aspx

huaxianz

jl162401 发表于 2013-4-10 20:29
google了一下 有点明白了

CLR， 你的意思是用VB或者C#写的一个function，  ...

spot on
急赤白脸的写了这么一大堆没有点儿浮云啊

jl162401

非常谢谢 有兴趣短我你的linkedin的信息

有机会可以交流一下

明天我写邮件教育一下印度三弟

jl162401

网上找了个例子 自己小改了一下

是不是类似这种：

huaxianz

楼主完全无视我的要求

形似了，剩下的就是load assembly到server instance中，注册一个t-sql scalar function来调用CLR版，server level需要turn on clr enabled option, database level needs set access security permission... 具体的在我给链接上有详细的说明

典

这种代码雷人得很，其实也不一定搞 CLR,看你具体应用，

wonderdream

这种单纯处理special character的不一定用SQLCLR。

权限有限，不能发link。你到google里搜 “t-sql remove special character”，stackoverflow里有好几种用while loop来实现替换的， 比用这么多replace的效率高多了。

huaxianz

当然不是"必须"clr实现。
但是如果说scalar function,string manipulation,50000000 rows这些关键字放在一起还不是clr典型应用的话，我真不知道该说啥了。随便找一本书或者一个视频都会提到这种scenario。
说真的，如果我看到while loop这种relational engine killer被用在50000000行数据上，这种程序员直接拉出去毙了。

典

还是看具体应用，如果建立加另一列在表里把处理后的字符串保存下来，就不用每次query处理了，

不知道具体应用，我不觉得每次都需要把所有记录返回给客户，一种可能是客户端只需要提取几十条百来条记录，但写查询的人没处理好每次都是计算所有的记录再提取部分记录。

如果是上面的情况完全可以把记录提取出去后，在客户端c#里做Replace,这样一来运行效率和CLR 差不多。