主页 / dba / 问题 / 205341
Accepted
Mazhar
Asked: 2018-05-01 03:51:19 +0800 CST

在查询中的多个列上调用相同表值函数的最有效方法

  • 772

我正在尝试调整在 20 列上调用相同表值函数 (TVF) 的查询。

我做的第一件事是将标量函数转换为内联表值函数。

是否使用CROSS APPLY性能最佳的方式在查询中的多个列上执行相同的功能？

一个简单的例子：

SELECT   Col1 = A.val
        ,Col2 = B.val
        ,Col3 = C.val
        --do the same for other 17 columns
        ,Col21
        ,Col22
        ,Col23
FROM t
CROSS APPLY
    dbo.function1(Col1) A
CROSS APPLY
    dbo.function1(Col2) B
CROSS APPLY
    dbo.function1(Col3) C
--do the same for other 17 columns

有更好的选择吗？

可以在针对 X 列的多个查询中调用相同的函数。

这是功能：

CREATE FUNCTION dbo.ConvertAmountVerified_TVF
(
    @amt VARCHAR(60)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    WITH cteLastChar
    AS(
        SELECT LastChar = RIGHT(RTRIM(@amt), 1)
    )
    SELECT
        AmountVerified  = CAST(RET.Y AS NUMERIC(18,2))
    FROM (SELECT 1 t) t
    OUTER APPLY (
        SELECT N =
                CAST(
                    CASE 
                        WHEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0) >0
                            THEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0)-1
                        WHEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, 'JKLMNOPQR', 0) >0
                            THEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, 'JKLMNOPQR', 0)-1
                        WHEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, 'pqrstuvwxy', 0) >0
                            THEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, 'pqrstuvwxy', 0)-1
                        ELSE 
                            NULL
                    END
                AS VARCHAR(1))
        FROM
            cteLastChar L
    ) NUM
    OUTER APPLY (
        SELECT N =
            CASE 
                WHEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0) >0
                    THEN 0
                WHEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, 'JKLMNOPQRpqrstuvwxy', 0) >0
                    THEN 1
                ELSE 0
            END
        FROM cteLastChar L
    ) NEG
    OUTER APPLY(
        SELECT Amt= CASE
                        WHEN NUM.N IS NULL
                            THEN @amt 
                        ELSE
                            SUBSTRING(RTRIM(@amt),1, LEN(@amt) - 1) + Num.N
                    END
    ) TP
    OUTER APPLY(
        SELECT Y =  CASE
                        WHEN NEG.N = 0
                            THEN (CAST(TP.Amt AS NUMERIC) / 100)
                        WHEN NEG.N = 1
                            THEN (CAST (TP.Amt AS NUMERIC) /100) * -1
                    END
    ) RET
) ;

GO

如果有人感兴趣，这是我继承的标量函数版本：

CREATE   FUNCTION dbo.ConvertAmountVerified 
(
    @amt VARCHAR(50)
)
RETURNS NUMERIC (18,3)  
AS
BEGIN   
    -- Declare the return variable here
    DECLARE @Amount NUMERIC(18, 3);
    DECLARE @TempAmount VARCHAR (50);
    DECLARE @Num VARCHAR(1);
    DECLARE @LastChar VARCHAR(1);
    DECLARE @Negative BIT ;
    -- Get Last Character
    SELECT @LastChar = RIGHT(RTRIM(@amt), 1) ;
    SELECT @Num = CASE @LastChar  collate latin1_general_cs_as
                        WHEN '{'  THEN '0'                                  
                        WHEN 'A' THEN '1'                       
                        WHEN 'B' THEN '2'                       
                        WHEN 'C' THEN '3'                       
                        WHEN 'D' THEN '4'                       
                        WHEN 'E' THEN '5'                       
                        WHEN 'F' THEN '6'                       
                        WHEN 'G' THEN '7'                       
                        WHEN 'H' THEN '8'                       
                        WHEN 'I' THEN '9'                       
                        WHEN '}' THEN '0'   
                        WHEN 'J' THEN '1'
                        WHEN 'K' THEN '2'                       
                        WHEN 'L' THEN '3'                       
                        WHEN 'M' THEN '4'                       
                        WHEN 'N' THEN '5'                       
                        WHEN 'O' THEN '6'                       
                        WHEN 'P' THEN '7'                       
                        WHEN 'Q' THEN '8'                       
                        WHEN 'R' THEN '9'

                        ---ASCII
                        WHEN 'p' Then '0'
                        WHEN 'q' Then '1'
                        WHEN 'r' Then '2'
                        WHEN 's' Then '3'
                        WHEN 't' Then '4'
                        WHEN 'u' Then '5'
                        WHEN 'v' Then '6'
                        WHEN 'w' Then '7'
                        WHEN 'x' Then '8'
                        WHEN 'y' Then '9'

                        ELSE ''

                END 
    SELECT @Negative = CASE @LastChar collate latin1_general_cs_as
                        WHEN '{' THEN 0         

                        WHEN 'A' THEN 0                 
                        WHEN 'B' THEN 0                     
                        WHEN 'C' THEN 0                     
                        WHEN 'D' THEN 0                     
                        WHEN 'E' THEN 0                     
                        WHEN 'F' THEN 0                     
                        WHEN 'G' THEN 0                     
                        WHEN 'H' THEN 0                     
                        WHEN 'I' THEN 0                     
                        WHEN '}' THEN 1 

                        WHEN 'J' THEN 1                     
                        WHEN 'K' THEN 1                     
                        WHEN 'L' THEN 1                     
                        WHEN 'M' THEN 1                 
                        WHEN 'N' THEN 1                     
                        WHEN 'O' THEN 1                     
                        WHEN 'P' THEN 1                     
                        WHEN 'Q' THEN 1                     
                        WHEN 'R' THEN 1

                        ---ASCII
                        WHEN 'p' Then '1'
                        WHEN 'q' Then '1'
                        WHEN 'r' Then '1'
                        WHEN 's' Then '1'
                        WHEN 't' Then '1'
                        WHEN 'u' Then '1'
                        WHEN 'v' Then '1'
                        WHEN 'w' Then '1'
                        WHEN 'x' Then '1'
                        WHEN 'y' Then '1'
                        ELSE 0
                END 
    -- Add the T-SQL statements to compute the return value here
    if (@Num ='')
    begin
    SELECT @TempAmount=@amt;
    end 
    else
    begin
    SELECT @TempAmount = SUBSTRING(RTRIM(@amt),1, LEN(@amt) - 1) + @Num;

    end
    SELECT @Amount = CASE @Negative
                     WHEN 0 THEN (CAST(@TempAmount AS NUMERIC) / 100)
                     WHEN 1 THEN (CAST (@TempAmount AS NUMERIC) /100) * -1
                     END ;
    -- Return the result of the function
    RETURN @Amount

END

样本测试数据：

SELECT dbo.ConvertAmountVerified('00064170')    --  641.700
SELECT * FROM dbo.ConvertAmountVerified_TVF('00064170') --  641.700

SELECT dbo.ConvertAmountVerified('00057600A')   --  5760.010
SELECT * FROM dbo.ConvertAmountVerified_TVF('00057600A')    --  5760.010

SELECT dbo.ConvertAmountVerified('00059224y')   --  -5922.490
SELECT * FROM dbo.ConvertAmountVerified_TVF('00059224y')    --  -5922.490
sql-server performance

4 个回答

  1. Best Answer

    第一：应该提到的是，获得所需结果的绝对最快的方法是执行以下操作：

    1. 将数据迁移到新列甚至新表中：
      1. 新列方法：
        1. 为具有数据类型{name}_new的表添加新列DECIMAL(18, 3)
        2. VARCHAR将数据从旧列一次性迁移到DECIMAL列
        3. 将旧列重命名为{name}_old
        4. 将新列重命名为{name}
      2. 新表方法：
        1. {table_name}_new使用DECIMAL(18, 3)数据类型创建新表
        2. 将数据从当前表一次性迁移到DECIMAL基于新的表。
        3. 将旧表重命名为_old
        4. _new从新表中删除
    2. 更新应用程序等以从不插入以这种方式编码的数据
    3. 一个发布周期后，如果没有问题，删除旧的列或表
    4. 丢弃 TVF 和 UDF
    5. 永远不要再提这个！

    话虽这么说：您可以摆脱很多代码，因为这在很大程度上是不必要的重复。此外，至少有两个错误会导致输出有时不正确，或者有时会引发错误。这些错误被复制到 Joe 的代码中，因为它产生与 OP 代码相同的结果（包括错误）。例如：

    • 这些值产生正确的结果：

      00062929x
00021577E
00000509H

    • 这些值会产生不正确的结果：

      00002020Q
00016723L
00009431O
00017221R

    • 此值会产生错误：

      00062145}
anything ending with "}"

    使用 将所有 3 个版本与 448,740 行进行比较SET STATISTICS TIME ON;，它们的运行时间都刚刚超过 5000 毫秒。但是对于 CPU 时间，结果是：

    • OP 的 TVF：7031 毫秒
    • 乔的 TVF： 3734 毫秒
    • 所罗门的 TVF：1407 毫秒

    设置：数据

    下面创建一个表并填充它。这应该在所有运行 SQL Server 2017 的系统中创建相同的数据集，因为它们在spt_values. 这有助于为在他们的系统上测试的其他人提供比较基础，因为随机生成的数据会影响系统之间的时间差异，甚至如果重新生成样本数据，甚至会影响同一系统上的测试之间的时间差异。我从与 Joe 相同的 3 列表开始，但使用问题中的示例值作为模板来提出各种数字值，并附有每个可能的尾随字符选项（包括无尾随字符）。这也是我在列上强制排序的原因：我不希望我使用二进制排序实例的事实不公平地否定使用COLLATE关键字以在 TVF 中强制使用不同的排序规则）。

    唯一的区别在于表中行的顺序。

    USE [tempdb];
SET NOCOUNT ON;

CREATE TABLE dbo.TestVals
(
  [TestValsID] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  [Col1] VARCHAR(50) COLLATE Latin1_General_100_CI_AS NOT NULL,
  [Col2] VARCHAR(50) COLLATE Latin1_General_100_CI_AS NOT NULL,
  [Col3] VARCHAR(50) COLLATE Latin1_General_100_CI_AS NOT NULL
);

;WITH cte AS
(
  SELECT (val.[number] + tmp.[blah]) AS [num]
  FROM [master].[dbo].[spt_values] val
  CROSS JOIN (VALUES (1), (7845), (0), (237), (61063), (999)) tmp(blah)
  WHERE val.[number] BETWEEN 0 AND 1000000
)
INSERT INTO dbo.TestVals ([Col1], [Col2], [Col3])
  SELECT FORMATMESSAGE('%08d%s', cte.[num], tab.[col]) AS [Col1],
       FORMATMESSAGE('%08d%s', ((cte.[num] + 2) * 2), tab.[col]) AS [Col2],
       FORMATMESSAGE('%08d%s', ((cte.[num] + 1) * 3), tab.[col]) AS [Col3]
  FROM    cte
  CROSS JOIN (VALUES (''), ('{'), ('A'), ('B'), ('C'), ('D'), ('E'), ('F'),
              ('G'), ('H'), ('I'), ('}'), ('J'), ('K'), ('L'), ('M'), ('N'),
              ('O'), ('P'), ('Q'), ('R'), ('p'), ('q'), ('r'), ('s'), ('t'),
              ('u'), ('v'), ('w'), ('x'), ('y')) tab(col)
  ORDER BY NEWID();
-- 463698 rows

    设置：TVF

    GO
CREATE OR ALTER FUNCTION dbo.ConvertAmountVerified_Solomon
(
    @amt VARCHAR(50)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN

    WITH ctePosition AS
    (
        SELECT CHARINDEX(RIGHT(RTRIM(@amt), 1) COLLATE Latin1_General_100_BIN2,
                             '{ABCDEFGHI}JKLMNOPQRpqrstuvwxy') AS [Value]
    ),
    cteAppend AS
    (
        SELECT pos.[Value] AS [Position],
               IIF(pos.[Value] > 0,
                      CHAR(48 + ((pos.[Value] - 1) % 10)),
                      '') AS [Value]
        FROM   ctePosition pos
    )
    SELECT (CONVERT(DECIMAL(18, 3),
                    IIF(app.[Position] > 0,
                           SUBSTRING(RTRIM(@amt), 1, LEN(@amt) - 1) + app.[Value],
                           @amt))
                        / 100. )
                    * IIF(app.[Position] > 10, -1., 1.) AS [AmountVerified]
    FROM   cteAppend app;
GO

    请注意：

    1. 我使用了二进制（即_BIN2）排序规则，它比区分大小写的排序规则更快，因为它不需要考虑任何语言规则。
    2. 唯一真正重要的是字母字符列表中最右侧字符的位置（即“索引”）加上两个大括号。操作上所做的一切都来自那个位置，而不是角色本身的价值。
    3. I used the input parameter and return value datatypes as indicated in the original UDF that was rewritten by the O.P. Unless there was good reason to go from VARCHAR(50) to VARCHAR(60), and from NUMERIC (18,3) to NUMERIC (18,2) (good reason would be "they were wrong"), then I would stick with the original signature / types.
    4. I added a period / decimal point to the end of the 3 numeric literals / constants: 100., -1., and 1.. This was not in my original version of this TVF (in the history of this answer) but I noticed some CONVERT_IMPLICIT calls in the XML execution plan (since 100 is an INT but the operation needs to be NUMERIC / DECIMAL) so I just took care of that ahead of time.
    5. I create a string character using the CHAR() function rather than passing a string version of a number (e.g. '2') into a CONVERT function (which was what I was originally doing, again in the history). This appears to be ever so slightly faster. Only a few milliseconds, but still.

    TEST

    Please note that I had to filter out rows ending with } as that caused the O.P.'s and Joe's TVFs to error. While my code handles the } correctly, I wanted to be consistent with what rows were being tested across the 3 versions. This is why the number of rows generated by the setup query is slightly higher than the number I noted above the test results for how many rows were being tested.

    SET STATISTICS TIME ON;

DECLARE @Dummy DECIMAL(18, 3);
SELECT --@Dummy =  -- commented out = results to client; uncomment to not return results
cnvrtS.[AmountVerified]
FROM  dbo.TestVals vals
CROSS APPLY dbo.ConvertAmountVerified_Solomon(vals.[Col1]) cnvrtS
WHERE RIGHT(vals.[Col1], 1) <> '}'; -- filter out rows that cause error in O.P.'s code

SET STATISTICS TIME OFF;
GO

    CPU time is only slightly lower when uncommenting the --@Dummy =, and the ranking among the 3 TVFs is the same. But interestingly enough, when uncommenting the variable, the rankings change a little:

    • Joe's TVF: 3295 ms
    • O.P.'s TVF: 2240 ms
    • Solomon's TVF: 1203 ms

    Not sure why the O.P.'s code would perform so much better in this scenario (whereas my and Joe's code only improved marginally), but it does seem consistent across many tests. And no, I did not look at execution plan differences as I don't have time to investigate that.

    EVEN FASTERER

    I have completed testing of the alternate approach and it does provide a slight but definite improvement to what is shown above. The new approach uses SQLCLR and it appears to scale better. I found that when adding in the second column to the query, the T-SQL approach double in time. But, when adding in additional columns using a SQLCLR Scalar UDF, the time went up, but not by the same amount as the single column timing. Maybe there is some initial overhead in invoking the SQLCLR method (not associated with the overhead of the initial loading of the App Domain and of the Assembly into the App Domain) because the timings were (elapsed time, not CPU time):

    • 1 column: 1018 ms
    • 2 columns: 1750 - 1800 ms
    • 3 columns: 2500 - 2600 ms

    So it's possible that the timing (of dumping to a variable, not returning the result set) has a 200 ms - 250 ms overhead and then 750 ms - 800 ms per instance time. CPU timings were: 950 ms, 1750 ms, and 2400 ms for 1, 2, and 3 instances of the UDF, respectively.

    C# CODE

    using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class Transformations
{
    private const string _CHARLIST_ = "{ABCDEFGHI}JKLMNOPQRpqrstuvwxy";

    [SqlFunction(IsDeterministic = true, IsPrecise = true,
        DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None)]
    public static SqlDouble ConvertAmountVerified_SQLCLR(
        [SqlFacet(MaxSize = 50)] SqlString Amt)
    {
        string _Amount = Amt.Value.TrimEnd();

        int _LastCharIndex = (_Amount.Length - 1);
        int _Position = _CHARLIST_.IndexOf(_Amount[_LastCharIndex]);

        if (_Position >= 0)
        {
            char[] _TempAmount = _Amount.ToCharArray();
            _TempAmount[_LastCharIndex] = char.ConvertFromUtf32(48 + (_Position % 10))[0];
            _Amount = new string(_TempAmount);
        }

        decimal _Return = decimal.Parse(_Amount) / 100M;

        if (_Position > 9)
        {
            _Return *= -1M;
        }

        return new SqlDouble((double)_Return);
    }
}

    I originally used SqlDecimal as the return type, but there is a performance penalty for using that as opposed to SqlDouble / FLOAT. Sometimes FLOAT has issues (due to it being an imprecise type), but I verified against the T-SQL TVF via the following query and no differences were detected:

    SELECT cnvrtS.[AmountVerified],
       dbo.ConvertAmountVerified_SQLCLR(vals.[Col1])
FROM   dbo.TestVals vals
CROSS APPLY dbo.ConvertAmountVerified_Solomon(vals.[Col1]) cnvrtS
WHERE  cnvrtS.[AmountVerified] <> dbo.ConvertAmountVerified_SQLCLR(vals.[Col1]);

    TEST

    SET STATISTICS TIME ON;

DECLARE @Dummy DECIMAL(18, 3), @Dummy2 DECIMAL(18, 3), @Dummy3 DECIMAL(18, 3);
SELECT @Dummy = 
       dbo.ConvertAmountVerified_SQLCLR(vals.[Col1])
              , @Dummy2 =
       dbo.ConvertAmountVerified_SQLCLR(vals.[Col2])
              , @Dummy3 =
       dbo.ConvertAmountVerified_SQLCLR(vals.[Col3])
FROM  dbo.TestVals vals
WHERE RIGHT(vals.[Col1], 1) <> '}';

SET STATISTICS TIME OFF;
    • 8

  2. 我将首先将一些测试数据放入表中。我不知道你的真实数据是什么样的，所以我只使用了顺序整数：

    CREATE TABLE APPLY_FUNCTION_TO_ME (
    COL1 VARCHAR(60),
    COL2 VARCHAR(60),
    COL3 VARCHAR(60)
);

INSERT INTO APPLY_FUNCTION_TO_ME WITH (TABLOCK)
SELECT RN, RN, RN
FROM (
    SELECT CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(60)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

    选择所有关闭了结果集的行提供了一个基线：

    -- CPU time = 1359 ms,  elapsed time = 1434 ms.
SELECT COL1 FROM dbo.APPLY_FUNCTION_TO_ME

    如果使用函数调用的类似查询需要更多时间，那么我们对函数的开销有一个粗略的估计。以下是我将您的 TVF 称为原样的结果：

    -- CPU time = 41703 ms,  elapsed time = 41899 ms.
SELECT t1.AmountVerified
FROM dbo.APPLY_FUNCTION_TO_ME
CROSS APPLY dbo.ConvertAmountVerified_TVF (COL1) t1
OPTION (MAXDOP 1);

    因此，该函数需要大约 40 秒的 CPU 时间来处理 650 万行。将其乘以 20 即为 800 秒的 CPU 时间。我在您的函数代码中注意到两件事：

    1. 不必要的使用OUTER APPLY。CROSS APPLY将为您提供相同的结果，并且对于此查询，它将避免一堆不必要的连接。这样可以节省一点时间。这主要取决于完整查询是否并行。我对您的数据或查询一无所知，所以我只是用MAXDOP 1. 在这种情况下，我最好使用CROSS APPLY.

    2. CHARINDEX当您只是针对一小部分匹配值搜索一个字符时，会有很多调用。您可以使用该ASCII()函数和一些数学运算来避免所有字符串比较。

    这是编写函数的另一种方法：

    CREATE OR ALTER FUNCTION dbo.ConvertAmountVerified_TVF3
(
    @amt VARCHAR(60)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    WITH cteLastChar
    AS(
        SELECT LastCharASCIICode =  ASCII(RIGHT(RTRIM(@amt), 1) COLLATE Latin1_General_CS_AS)
    )
    SELECT
        AmountVerified  = CAST(RET.Y AS NUMERIC(18,2))
    FROM cteLastChar
    CROSS APPLY (
        SELECT N =
                CAST(
                    CASE 
                        --WHEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0) >0
                        --    THEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0)-1
                        WHEN LastCharASCIICode = 123 THEN 0
                        WHEN LastCharASCIICode BETWEEN 65 AND 73 THEN LastCharASCIICode - 64
                        WHEN LastCharASCIICode = 125 THEN 10

                        --WHEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, 'JKLMNOPQR', 0) >0
                        --    THEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, 'JKLMNOPQR', 0)-1
                        WHEN LastCharASCIICode BETWEEN 74 AND 82 THEN LastCharASCIICode - 74

                        --WHEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, 'pqrstuvwxy', 0) >0
                        --    THEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, 'pqrstuvwxy', 0)-1
                        WHEN LastCharASCIICode BETWEEN 112 AND 121 THEN LastCharASCIICode - 112
                        ELSE 
                            NULL
                    END
                AS VARCHAR(1))
        --FROM
        --    cteLastChar L
    ) NUM
    CROSS APPLY (
        SELECT N =
            CASE 
                --WHEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, '{ABCDEFGHI}', 0) >0
                WHEN LastCharASCIICode = 123 OR LastCharASCIICode = 125 OR LastCharASCIICode BETWEEN 65 AND 73
                    THEN 0

                --WHEN CHARINDEX(L.LastChar  COLLATE Latin1_General_CS_AS, 'JKLMNOPQRpqrstuvwxy', 0) >0
                WHEN LastCharASCIICode BETWEEN 74 AND 82 OR LastCharASCIICode BETWEEN 112 AND 121
                    THEN 1
                ELSE 0
            END
        --FROM cteLastChar L
    ) NEG
    CROSS APPLY(
        SELECT Amt= CASE
                        WHEN NUM.N IS NULL
                            THEN @amt 
                        ELSE
                            SUBSTRING(RTRIM(@amt),1, LEN(@amt) - 1) + Num.N
                    END
    ) TP
    CROSS APPLY(
        SELECT Y =  CASE
                        WHEN NEG.N = 0
                            THEN (CAST(TP.Amt AS NUMERIC) / 100)
                        WHEN NEG.N = 1
                            THEN (CAST (TP.Amt AS NUMERIC) /100) * -1
                    END
    ) RET
) ;

GO

    在我的机器上，新功能明显更快：

    -- CPU time = 7813 ms,  elapsed time = 7876 ms.
SELECT t1.AmountVerified
FROM dbo.APPLY_FUNCTION_TO_ME
CROSS APPLY dbo.ConvertAmountVerified_TVF3 (COL1) t1
OPTION (MAXDOP 1);

    可能还有一些额外的优化可用，但我的直觉说它们不会太多。根据您的代码在做什么，我看不出您会如何通过以不同方式调用您的函数来进一步改进。这只是一堆字符串操作。每行调用该函数 20 次将比一次慢，但定义已经被内联。

    • 4

  3. Alternatively you can create one permanent table.This is one time creation.

    CREATE TABLE CharVal (
    charactor CHAR(1) collate latin1_general_cs_as NOT NULL
    ,positiveval INT NOT NULL
    ,negativeval INT NOT NULL
    ,PRIMARY KEY (charactor)
    )

insert into CharVal (charactor,positiveval,negativeval) VALUES

 ( '{' ,'0', 0 ),( 'A' ,'1', 0 ) ,( 'B' ,'2', 0 ) ,( 'C' ,'3', 0 ) ,( 'D' ,'4', 0 )       
                         ,( 'E' ,'5', 0 )  ,( 'F' ,'6', 0 ) ,( 'G' ,'7', 0 ) ,( 'H' ,'8', 0 )       
,( 'I' ,'9', 0 ),( '}' ,'0', 1 ),( 'J' ,'1', 1  ),( 'K' ,'2', 1 ) ,( 'L' ,'3', 1 ) ,( 'M' ,'4', 1 )       
,( 'N' ,'5', 1 )  ,( 'O' ,'6', 1 )  ,( 'P' ,'7', 1 )  ,( 'Q' ,'8', 1 )  ,( 'R' ,'9', 1  )
---ASCII
,( 'p' , '0', '1'),( 'q' , '1', '1'),( 'r' , '2', '1'),( 's' , '3', '1')
,( 't' , '4', '1'),( 'u' , '5', '1'),( 'v' , '6', '1'),( 'w' , '7', '1')
,( 'x' , '8', '1'),( 'y' , '9', '1')

--neg
('{' ,2, 0) ,('A' ,2, 0) ,('B' ,2, 0)  ,('C' ,2, 0) ,('D' ,2, 0)                    
,('E' ,2, 0),('F' ,2, 0)  ,('G' ,2, 0) ,('H' ,2, 0) ,('I' ,2, 0) ,('}' ,2, 1)
,('J' ,2, 1) ,('K' ,2, 1) ,('L' ,2, 1) ,('M' ,2, 1) ,('N' ,2, 1)                    
,('O' ,2, 1)  ,('P' ,2, 1)  ,('Q' ,2, 1) ,('R' ,2, 1)
  ---ASCII
,( 'p' ,2, '1'),( 'q' ,2, '1')
,( 'r' ,2, '1'),( 's' ,2, '1')
,( 't' ,2, '1'),( 'u' ,2, '1')
,( 'v' ,2, '1'),( 'w' ,2, '1')
,( 'x' ,2, '1'),( 'y' ,2, '1')

    Then TVF

    ALTER FUNCTION dbo.ConvertAmountVerified_TVFHarsh (@amt VARCHAR(60))
RETURNS TABLE
    WITH SCHEMABINDING
AS
RETURN (
        WITH MainCTE AS (
                SELECT TOP 1 
                Amt = CASE 
                        WHEN positiveval IS NULL
                            THEN @amt
                        ELSE SUBSTRING(RTRIM(@amt), 1, LEN(@amt) - 1) + positiveval
                        END
                    ,negativeval
                FROM (
                    SELECT positiveval
                        ,negativeval negativeval
                        ,1 sortorder
                    FROM dbo.CharVal WITH (NOLOCK)
                    WHERE (charactor = RIGHT(RTRIM(@amt), 1))

                    UNION ALL

                    SELECT NULL
                        ,0
                        ,0
                    ) t4
                ORDER BY sortorder DESC
                )

        SELECT AmountVerified = CASE 
                WHEN negativeval = 0
                    THEN (CAST(TP.Amt AS NUMERIC) / 100)
                WHEN negativeval = 1
                    THEN (CAST(TP.Amt AS NUMERIC) / 100) * - 1
                END
        FROM MainCTE TP
        );
GO

    From @Joe example,

    -- It take 30 s

    SELECT t1.AmountVerified
FROM dbo.APPLY_FUNCTION_TO_ME
CROSS APPLY dbo.ConvertAmountVerified_TVFHarsh (COL1) t1
OPTION (MAXDOP 1);

    If it is possible, Amount can be formatted at UI level also. This is the best option. Otherwise you can share your original query also. OR if possible keep formatted value in table also.

    • 2

  4. Try to use the following

    -- Get Last Character
SELECT @LastChar = RIGHT(RTRIM(@amt), 1) collate latin1_general_cs_as;

DECLARE @CharPos int=NULLIF(CHARINDEX(@LastChar,'{ABCDEFGHI}JKLMNOPQRpqrstuvwxy'),0)-1
SET @Num = ISNULL(@CharPos%10,''); 
SET @Negative = IIF(@CharPos>9,1,0);

    instead

    SELECT @Num =
    CASE @LastChar  collate latin1_general_cs_as
        WHEN '{'  THEN '0'
...

SELECT @Negative =
    CASE @LastChar collate latin1_general_cs_as
        WHEN '{' THEN 0
...

    One variant with using an auxiliary table

    -- auxiliary table
CREATE TABLE LastCharLink(
  LastChar varchar(1) collate latin1_general_cs_as NOT NULL,
  Num varchar(1) NOT NULL,
  Prefix varchar(1) NOT NULL,
CONSTRAINT PK_LastCharLink PRIMARY KEY(LastChar)
)

INSERT LastCharLink(LastChar,Num,Prefix)VALUES
('{','0',''),
('A','1',''),
('B','2',''),
('C','3',''),
('D','4',''),
('E','5',''),
('F','6',''), 
('G','7',''), 
('H','8',''), 
('I','9',''), 
('}','0','-'), 
('J','1','-'),
('K','2','-'),
('L','3','-'),
('M','4','-'),
('N','5','-'),
('O','6','-'),
('P','7','-'),
('Q','8','-'),
('R','9','-'),                
('p','0','-'),
('q','1','-'),
('r','2','-'),
('s','3','-'),
('t','4','-'),
('u','5','-'),
('v','6','-'),
('w','7','-'),
('x','8','-'),
('y','9','-')

    A test query

    CREATE TABLE #TestAmounts(Amt varchar(10))
INSERT #TestAmounts(Amt)VALUES('00064170'),('00057600A'),('00066294R'),('00059224}'),('00012345p')

SELECT
  *,
  CAST( -- step 5 - final cast
      CAST( -- step 3 - convert to number
          CONCAT( -- step 2 - add a sign and an additional number
              l.Prefix,
              LEFT(RTRIM(a.Amt),LEN(RTRIM(a.Amt))-IIF(l.LastChar IS NULL,0,1)), -- step 1 - remove last char
              l.Num
            )
          AS numeric(18,3)
        )/100 -- step 4 - divide
      AS numeric(18,3)
    ) ResultAmt
FROM #TestAmounts a
LEFT JOIN LastCharLink l ON RIGHT(RTRIM(a.Amt),1) collate latin1_general_cs_as=l.LastChar

DROP TABLE #TestAmounts

    As variant you also can try to use a temporary auxiliary table #LastCharLink or a variable table @LastCharLink (but it can be slower than a real or temporary table)

    DECLARE @LastCharLink TABLE(
  LastChar varchar(1) collate latin1_general_cs_as NOT NULL,
  Num varchar(1) NOT NULL,
  Prefix varchar(1) NOT NULL,
PRIMARY KEY(LastChar)
)

INSERT LastCharLink(LastChar,Num,Prefix)VALUES
('{','0',''),
('A','1',''),
('B','2',''),
('C','3',''),
('D','4',''),
('E','5',''),
...

    And use it as

    FROM #TestAmounts a
LEFT JOIN #LastCharLink l ON ...

    or

    FROM #TestAmounts a
LEFT JOIN @LastCharLink l ON ...

    Then you also can create a simple inline function and put into it all the conversions

    CREATE FUNCTION NewConvertAmountVerified(
  @Amt varchar(50),
  @LastChar varchar(1),
  @Num varchar(1),
  @Prefix varchar(1)
)
RETURNS numeric(18,3)
AS
BEGIN
  RETURN CAST( -- step 3 - convert to number
              CONCAT( -- step 2 - add a sign and an additional number
                  @Prefix,
                  LEFT(@Amt,LEN(@Amt)-IIF(@LastChar IS NULL,0,1)), -- step 1 - remove last char
                  @Num
                )
              AS numeric(18,3)
            )/100 -- step 4 - divide
END
GO

    And then use this function as

    CREATE TABLE #TestAmounts(Amt varchar(10))
INSERT #TestAmounts(Amt)VALUES('00064170'),('00057600A'),('00066294R'),('00059224}'),('00012345p')

SELECT
  *,
  -- you need to use `RTRIM` here
  dbo.NewConvertAmountVerified(RTRIM(a.Amt),l.LastChar,l.Num,l.Prefix) ResultAmt
FROM #TestAmounts a
LEFT JOIN LastCharLink l ON RIGHT(RTRIM(a.Amt),1) collate latin1_general_cs_as=l.LastChar

DROP TABLE #TestAmounts
    • 2

相关问题