用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 差不多。

huaxianz

TRUE,如果抛开楼主提到的5000万行，神马都不是个事儿。

melmonash

huaxianz 发表于 2013-4-10 20:38
典型的CLR Scalar Funtion的应用:
就是说拿出你的.net programming的经验来写scalar function。因为对于sca ...

谢谢提供的连接，真是每天都能学到新东西啊。

jl162401

后续update：
1. 网上有人建议改成inline table udf, 改是可以改 但不知道怎么用 cross apply?

2.现在这个函数的测试结果 如果不调用完全做select * from table，30分钟， 调用udf函数 2.5个小时，三哥不愿意改 东拉西扯

3. C#的那个 没有跟进 有机会等我弄好编译环境了 再试试看

4. 另外建议三哥简化udf，只处理newline和pipe，剩下的交给我方进行处理 几行C就搞定了， 还在测试中， 不过这个和我曾经提到的第二个问题是互相矛盾的 如果replace的次数影响性能的话

5. 另外可能性就是我老板的老板（女的）和三哥说 不雅 off， 我方自己做

jl162401

典 发表于 2013-4-11 08:41
还是看具体应用，如果建立加另一列在表里把处理后的字符串保存下来，就不用每次query处理了，

不知道具体 ...

这个问题我们讨论过

1. 导出文件是delimited file, 如果分隔符（例如PIPE |)，出现在某个字段的数据中，导出成本文后后很难处理，和delimiter列分隔符混在一起了
除非可以确保分隔符从不出现在数据中 这个很难说 SQL server上啥都可能出现

2. 另外就是0D0A, 0D, 0A如何出现在数据中，导成文本后，很难处理（和行分隔符)混在一起了

至少这两个处理需要在数据库中操作

jl162401

wonderdream 发表于 2013-4-10 23:59
这种单纯处理special character的不一定用SQLCLR。

权限有限，不能发link。你到google里搜 “t-sql remove ...

while loop的效率应该也不会很高吧，说了归齐 这个需要逐行操作 类似于Cursor的，效率上肯定会很低

你说的while loop是不是这样的

CREATE FUNCTION dbo.StripNonAlphaNumerics
(
  @s VARCHAR(255),
  @pattern VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
  DECLARE @p INT = 1, @n VARCHAR(255) = '';
  WHILE @p <= LEN(@s)
  BEGIN
    IF SUBSTRING(@s, @p, 1) LIKE @pattern
    BEGIN
      SET @n += @c;
    END
    SET @p += 1;
  END
  RETURN(@n);
END
GO

SELECT r = dbo.StripNonAlphaNumerics('Bob''s dog & #1 friend are dope, yo!', '[A-Za-z0-9]');

我找三哥来试试看

jl162401

这个也很酷啊

select
ltrim(rtrim(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
' Enter The string you want to
sanitize here'
,char(1),'')        ,char(2),'')        ,char(3),'')        ,char(4),'')
,char(5),'')        ,char(6),'')        ,char(7),'')        ,char(8),'')
,char(9),'')    ,char(10),'')        ,char(11),'')        ,char(12),'')
,char(13),'')        ,char(14),'')   ,char(15),'')        ,char(16),'')
,char(17),'')        ,char(18),'')        ,char(18),'')   ,char(20),'')
,char(21),'')        ,char(22),'')        ,char(23),'')        ,char(24),'')
,char(25),'')        ,char(26),'')        ,char(27),'')        ,char(28),'')
,char(29),'')   ,char(30),'')        ,char(32),'')   ,char(32),'')
))

典

Cross apply会快些
因为不明白用途所以很难说，这个query是用来导出数据还是用来做别的？（比如给应用程序提供数据），
每天都把这个表导出来一遍？表里的数据每天都是新数据？如果数据大部分没变，为何要反复导出？能不能用过渡表---先replce掉保存在另外一个临时表？，，，，

wonderdream

其实无论是udf还是SQLCLR，基于5千万条以上的数据，都不可能有很好的效率，毕竟invocation overhead摆在那里。 还是像典说的那样，把处理过的数据放在另外一个table里才可能达到好的query performance.

毕竟SQL是 set based language, 如果要在runtime处理每行的数据，太慢了。

huaxianz

jl162401 发表于 2013-4-13 15:07
这个也很酷啊

select

这个绝对比原先的好  虽然可读性可能变得更差了。SQL Server有个概念叫execution context，具体的可一次参考http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

jl162401

我在晚上search了while loop的方法 改写成这样

create function dbo.[ReplaceSplVarcharsBICD_test3] (@s varchar(8000)) returns varchar(8000)
   with schemabinding
begin
   if @s is null
      return null
   declare @s2 varchar(8000)
   set @s2 = ''
   declare @l int
   set @l = len(@s)
   declare @p int
   set @p = 1
   while @p <= @l begin
      declare @c int
      set @c = ascii(substring(@s, @p, 1))
                  --any characters less than 32(dec,Space), or 124 (PIPE) or 127 (DEL) need to be replace with 32 (dec,Space)
      if @c between 32 and 123 or @c between 125 and 126
         set @s2 = @s2 + char(@c)
                 --any characters less than 32(dec,Space), or 124 (PIPE) or 127 (DEL) need to be replace with 32 (dec,Space)
                  if @c between 0 and 31 or @c = 124 or @c= 127
         set @s2 = @s2 + char(32)
      set @p = @p + 1
      end
   if len(@s2) = 0
      return null
   return ltrim(rtrim(@s2))
end
go

测试结果35分钟 原先运行要2.5小时
要求三哥回去好好测试

wonderdream

jl162401 发表于 2013-4-16 19:38
我在晚上search了while loop的方法 改写成这样

create function dbo.[ReplaceSplVarcharsBICD_test3] (@s  ...

Sounds good.

建议把table做partitioning，这样可以maximize cpu parallism。几个partition并行处理，效率更高

jl162401

table不能改做partitioning 业务系统

huaxianz

wonderdream 发表于 2013-4-16 22:40
Sounds good.

建议把table做partitioning，这样可以maximize cpu parallism。几个partition并行处理，效 ...

Partition的好处不是parallelism,而是possible partition elimination. 并行处理的先决条件是Enterprise edition, 即使是single partition的big table, Sql Server也有可能选择并行处理，whenever query optimizer thinks appropriate.

wonderdream

huaxianz 发表于 2013-4-17 08:08
Partition的好处不是parallelism,而是possible partition elimination. 并行处理的先决条件是Enterprise  ...

In SQL Server 2008, MS has improved the dynamic threads  allocation and parallelism over partitioned table.

Refer to

• "Demo SQL Server 2008 Partitioned Table Parallelism" on Channel 9
• "Query Processing Enhancements on Partitioned Tables and Indexes" on MSDN
  

(sry, i cant post links)

In addition, the table partitioning allows to split the large table data across multiple drives and boost I/O. And ususally, the bottlenect is not CPU but disk I/O.

wonderdream

jl162401 发表于 2013-4-16 22:50
table不能改做partitioning 业务系统

table partitioning should be invisible to the application system. It should only affect your database layer especially table maintenance.

There are some issues when used for OLTP but there are also benefits especially for large tables. Decision should be made based on your actual requirement and query patterns.

One use case I can think of is that you can put your "very rarely update" data onto slower drive and put your "often update and insert" data onto your fastest drive (e.g. Fusion IO or SSD). That should boost your application performance as the CRUD will be much faster.