Sql-Server

在查詢中的多個列上呼叫相同表值函式的最有效方法

  • May 4, 2018

我正在嘗試調整在 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

第一：應該提到的是，獲得所需結果的絕對最快的方法是執行以下操作：

  1. 將數據遷移到新列甚至新表中：

  2. 新列方法：

    1. 為具有數據類型{name}_new的表添加新列DECIMAL(18, 3)
    2. VARCHAR將數據從舊列一次性遷移到DECIMAL列
    3. 將舊列重命名為{name}_old
    4. 將新列重命名為{name}

  3. 新表方法：

    1. {table_name}_new使用DECIMAL(18, 3)數據類型創建新表
    2. 將數據從目前表一次性遷移到DECIMAL基於新的表。
    3. 將舊表重命名為_old
    4. _new從新表中刪除

  4. 更新應用程序等以從不插入以這種方式編碼的數據

  5. 一個發布週期後，如果沒有問題，刪除舊的列或表

  6. 丟棄 TVF 和 UDF

  7. 永遠不要再提這個！

話雖這麼說：您可以擺脫很多程式碼，因為這在很大程度上是不必要的重複。此外，至少有兩個錯誤會導致輸出有時不正確，或者有時會引發錯誤。這些錯誤被複製到 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. 我使用了由 OP 重寫的原始 UDF 中指示的輸入參數和返回值數據類型，除非有充分的理由從VARCHAR(50)toVARCHAR(60)和 from NUMERIC (18,3)to 去NUMERIC (18,2)（很好的理由是“他們錯了”），那麼我會堅持帶有原始簽名/類型。
  4. 我在 3 個數字文字/常量的末尾添加了一個句點/小數點：100.、-1.和1.. 這不在此 TVF 的原始版本中（在此答案的歷史中），但我注意到CONVERT_IMPLICITXML 執行計劃中的一些呼叫（因為100是 anINT但操作需要是NUMERIC/ DECIMAL）所以我只是提前處理了.
  5. 我使用該函式創建一個字元串字元，CHAR()而不是將數字的字元串版本（例如'2'）傳遞給CONVERT函式（這也是我最初所做的，在歷史上也是如此）。這似乎比以往任何時候都快。只有幾毫秒，但仍然如此。

測試

請注意，我必須過濾掉以結尾的行，}因為這會導致 OP 和 Joe 的 TVF 出錯。雖然我的程式碼處理}正確，但我希望與 3 個版本中正在測試的行保持一致。這就是為什麼設置查詢生成的行數略高於我在上面提到的測試結果中所測試的行數的原因。

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 時間僅略低--@Dummy =，3 個 TVF 中的排名相同。但有趣的是，當取消註釋變數時，排名會發生一些變化：

  • 喬的 TVF： 3295 毫秒
  • OP 的 TVF：2240 毫秒
  • 所羅門的 TVF：1203 毫秒

不知道為什麼 OP 的程式碼在這種情況下會表現得更好（而我和 Joe 的程式碼只略有改進），但它在許多測試中似乎是一致的。不，我沒有查看執行計劃的差異，因為我沒有時間對此進行調查。

更快

我已經完成了對替代方法的測試，它確實對上面顯示的內容提供了輕微但明確的改進。新方法使用 SQLCLR，它似乎可以更好地擴展。我發現在查詢的第二列中添加時，T-SQL 方法在時間上加倍。但是，當使用 SQLCLR Scalar UDF 添加其他列時，時間增加了，但與單列時間增加的數量不同。也許在呼叫 SQLCLR 方法時會有一些初始成本（與初始載入應用程序域和程序集到應用程序域的成本無關），因為時間是（經過的時間，而不是 CPU 時間）：

  • 1 列：1018 毫秒
  • 2 列：1750 - 1800 毫秒
  • 3 列：2500 - 2600 毫秒

因此，時間（轉儲到變數，不返回結果集）可能有 200 ms - 250 ms 的成本，然後是每個實例時間 750 ms - 800 ms。CPU 計時分別為：1、2 和 3 個 UDF 實例分別為 950 毫秒、1750 毫秒和 2400 毫秒。

C# 程式碼

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);
   }
}

我最初用作SqlDecimal返回類型，但使用它而不是SqlDouble/會降低性能FLOAT。有時 FLOAT 有問題（因為它是一個不精確的類型），但我通過以下查詢驗證了 T-SQL TVF 並沒有檢測到差異：

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]);

測試

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;

引用自：https://dba.stackexchange.com/questions/205341

相關問答

Sql-Server

常量變數為空時執行計劃不同

  • November 3, 2020
檢視問答
Sql-Server

在沒有 CTE/內聯表的情況下計算目前值和下一個值之間的差異

  • March 14, 2019
檢視問答
Sql-Server

添加一個額外的“order by”列給了我一個更糟糕的計劃

  • March 12, 2019
檢視問答
Sql-Server

預登台數據導致執行計劃成本飆升

  • February 17, 2019
檢視問答
Sql-Server

高效的 SQL 查詢返回滿足對孩子的約束的父母

  • June 1, 2018
檢視問答
Sql-Server

嵌套循環估計太低，導致 tempdb 溢出

  • May 23, 2018
檢視問答