|
目前有个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倍快 还是几倍快 |
