I have written another UDF to use while loop shown as below:
create function dbo.[ReplaceSplVarcharsBICD_test3] (@s varchar(8000)) returns varchar(8000)
with schemabinding
begin
if @s-2 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
Testing results:
1. If we don't call any udf, it took 30 minutes to dump all the data into a file
2. If we use the first udf, it took 2.5 hours to dump all the data into a file
3. If we use the second udf, it took 35 minutes to dump all the data into a file.
Can you let me know if I rewrite the UDF into inline table level UDF, how to use the cross apply
My current sql is shown as below:
SELECT 'D' AS RECORDTYPECODE
, a.[Id]
,a.[FundsMovementItemTypeId]
,a.[FundsMovementSetId]
,a.[FeeTypeId]
,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[AccountName])
,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[GLAccountName])
,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[LodgementReference])
,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[AccountNumber])
,a.[IsSundryParties]
,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[TraceBSB])
,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[TraceAccountName])
,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[TraceAccountNumber])
,a.[IsSundryPartiesTraceACC]
,a.[IsAttractGST]
,a.[IsCustomerAccount]
,a.[Amount]
,a.[OnDateTime]
,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[AccountBankCode])
,a.[U1]
,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[U2])
,a.[U3]
,a.[U5]
,a.[genID]
FROM %DBNAME%.FundsMovementItem a