純量 UDF 內嵌Scalar UDF Inlining

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本文介紹純量 UDF 內嵌,這是智慧型查詢處理功能套件下的一項功能。This article introduces Scalar UDF inlining, a feature under the intelligent query processing suite of features. 此功能可針對在 SQL ServerSQL Server (從 SQL Server 2019SQL Server 2019 開始) 及 SQL DatabaseSQL Database 中叫用 UDF 的查詢改善其效能。This feature improves the performance of queries that invoke scalar UDFs in SQL ServerSQL Server (starting with SQL Server 2019SQL Server 2019) and SQL DatabaseSQL Database.

T-SQL 純量使用者自訂函式T-SQL Scalar User-Defined Functions

以 Transact-SQL 實作並傳回單一資料值的使用者定義函式,就是所謂的 T-SQL 純量使用者定義函式。User-Defined Functions that are implemented in Transact-SQL and return a single data value are referred to as T-SQL Scalar User-Defined Functions. T-SQL UDF 是一種在 SQL 查詢之間達成程式碼重複使用和模組化的優雅方式。T-SQL UDFs are an elegant way to achieve code reuse and modularity across SQL queries. 某些計算 (例如複雜的商務規則) 更容易以命令式 UDF 格式表達。Some computations (such as complex business rules) are easier to express in imperative UDF form. UDF 有助於建置複雜的邏輯,而不需要撰寫複雜 SQL 查詢的專業知識。UDFs help in building up complex logic without requiring expertise in writing complex SQL queries.

純量 UDF 的效能Performance of Scalar UDFs

純量 UDF 最終效能不佳通常是下列原因所致。Scalar UDFs typically end up performing poorly due to the following reasons.

  • 反覆叫用: UDF 會以反覆方式叫用,每個合格的元組一次。Iterative invocation: UDFs are invoked in an iterative manner, once per qualifying tuple. 這會因函式叫用而產生重複內容切換的額外成本。This incurs additional costs of repeated context switching due to function invocation. 特別是,在其定義中執行 SQL 查詢的 UDF 會受到嚴重影響。Especially, UDFs that execute SQL queries in their definition are severely affected.
  • 缺少成本估算: 在最佳化期間,只會估算關係運算子的成本,而不會估算純量運算子的成本。Lack of costing: During optimization, only relational operators are costed, while scalar operators are not. 在引進純量 UDF 之前,其他純量運算子通常很便宜,而不需要進行成本估算。Prior to the introduction of scalar UDFs, other scalar operators were generally cheap and did not require costing. 為純量運算子新增少量 CPU 成本便已足夠。A small CPU cost added for a scalar operation was enough. 但還是有一些實際成本很高,卻仍然未充分表示的情況。There are scenarios where the actual cost is significant, and yet still remains underrepresented.
  • 解譯執行: UDF 會評估為陳述式批次,逐一執行陳述式。Interpreted execution: UDFs are evaluated as a batch of statements, executed statement-by-statement. 系統會編譯每個陳述式本身,並快取已編譯的計劃。Each statement itself is compiled, and the compiled plan is cached. 雖然此快取策略可因避免重新編譯而節省一些時間,但每個陳述式都會單獨執行。Although this caching strategy saves some time as it avoids recompilations, each statement executes in isolation. 不會執行跨陳述式的最佳化。No cross-statement optimizations are carried out.
  • 序列執行: SQL Server 不允許在叫用 UDF 的查詢中使用內部查詢平行處理原則。Serial execution: SQL Server does not allow intra-query parallelism in queries that invoke UDFs.

自動內嵌純量 UDFAutomatic Inlining of Scalar UDFs

純量 UDF 內嵌功能其目標是改善叫用 T-SQL 純量 UDF 的查詢效能,其中的 UDF 執行是主要瓶頸。The goal of the Scalar UDF inlining feature is to improve performance of queries that invoke T-SQL scalar UDFs, where UDF execution is the main bottleneck.

使用這項新功能,純量 UDF 會自動轉換成純量運算式或純量子查詢,以在呼叫查詢中替代 UDF 運算子。With this new feature, scalar UDFs are automatically transformed into scalar expressions or scalar subqueries that are substituted in the calling query in place of the UDF operator. 接著,系統就會將這些運算式和子查詢最佳化。These expressions and subqueries are then optimized. 因此,查詢計劃不再具有使用者定義函式運算子,但在計劃中將觀察到其效果,例如檢視或內嵌 TVF。As a result, the query plan will no longer have a user-defined function operator, but its effects will be observed in the plan, like views or inline TVFs.

範例 1 - 單一陳述式純量 UDFExample 1 - Single statement scalar UDF

請考慮下列查詢Consider the following query

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (L_EXTENDEDPRICE *(1 - L_DISCOUNT)) 
FROM LINEITEM
INNER JOIN ORDERS
  ON O_ORDERKEY = L_ORDERKEY 
GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE;

此查詢會計算明細項目折扣價格的總和,並顯示依據送貨日期和送貨優先順序分組的結果。This query computes the sum of discounted prices for line items and presents the results grouped by the shipping date and shipping priority. 運算式 L_EXTENDEDPRICE *(1 - L_DISCOUNT) 是指定明細項目折扣價格的公式。The expression L_EXTENDEDPRICE *(1 - L_DISCOUNT) is the formula for the discounted price for a given line item. 這類公式可以擷取至函式,以方便模組化和重複使用。Such formulas can be extracted into functions for the benefit of modularity and reuse.

CREATE FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2)) 
RETURNS DECIMAL (12,2) AS
BEGIN
  RETURN @price * (1 - @discount);
END

現在,您可以修改查詢來叫用此 UDF。Now the query can be modified to invoke this UDF.

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT)) 
FROM LINEITEM
INNER JOIN ORDERS
  ON O_ORDERKEY = L_ORDERKEY 
GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

根據先前所述的原因,具有 UDF 的查詢執行狀況不佳。Due to the reasons outlined earlier, the query with the UDF performs poorly. 現在,透過純量 UDF 內嵌,您可以在查詢中直接替代為 UDF 主體中的純量運算式。Now, with scalar UDF inlining, the scalar expression in the body of the UDF is substituted directly in the query. 執行此查詢的結果會顯示在下表:The results of running this query are shown in the below table:

查詢:Query: 沒有 UDF 的查詢Query without UDF 具有 UDF 的查詢 (不使用內嵌)Query with UDF (without inlining) 使用純量 UDF 內嵌的查詢Query with scalar UDF inlining
執行時間:Execution time: 1.6 秒1.6 seconds 29 分 11 秒29 minutes 11 seconds 1.6 秒1.6 seconds

這些數字是根據 10-GB CCI 資料庫 (使用 TPC-H 結構描述),其在具有雙處理器 (12 個核心)、96 GB RAM 且 SSD 支援的機器上執行。These numbers are based on a 10-GB CCI database (using the TPC-H schema), running on a machine with dual processor (12 core), 96-GB RAM, backed by SSD. 這些數字包含冷程序快取和緩衝集區的編譯及執行時間。The numbers include compilation and execution time with a cold procedure cache and buffer pool. 使用了預設設定,但未建立任何其他的索引。The default configuration was used, and no other indexes were created.

範例 2 - 多重陳述式純量 UDFExample 2 - Multi-statement scalar UDF

使用多個 T-SQL 陳述式 (例如變數指派和條件式分支) 實作的純量 UDF 也可以進行內嵌。Scalar UDFs that are implemented using multiple T-SQL statements such as variable assignments and conditional branching can also be inlined. 請考慮下列純量 UDF,其可根據客戶索引鍵來判斷該客戶的服務類別。Consider the following scalar UDF that, given a customer key, determines the service category for that customer. 它會先使用 SQL 查詢計算客戶所下全部訂單的總價,以到達類別。It arrives at the category by first computing the total price of all orders placed by the customer using a SQL query. 然後,使用 IF-ELSE 邏輯根據總價決定類別。Then, it uses an IF-ELSE logic to decide the category based on the total price.

CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT) 
RETURNS CHAR(10) AS
BEGIN
  DECLARE @total_price DECIMAL(18,2);
  DECLARE @category CHAR(10);

  SELECT @total_price = SUM(O_TOTALPRICE) FROM ORDERS WHERE O_CUSTKEY = @ckey;

  IF @total_price < 500000
    SET @category = 'REGULAR';
  ELSE IF @total_price < 1000000
    SET @category = 'GOLD';
  ELSE 
    SET @category = 'PLATINUM';

  RETURN @category;
END

現在,請考慮叫用此 UDF 的查詢。Now, consider a query that invokes this UDF.

SELECT C_NAME, dbo.customer_category(C_CUSTKEY) FROM CUSTOMER;

SQL Server 2017 (相容性層級 140 及更早版本) 中此查詢的執行計劃如下:The execution plan for this query in SQL Server 2017 (compatibility level 140 and earlier) is as follows:

不使用內嵌的查詢計劃

如計劃所示,SQL Server 在此採用一個簡單的策略:針對 CUSTOMER 資料表中的每個元組,叫用 UDF 並輸出結果。As the plan shows, SQL Server adopts a simple strategy here: for every tuple in the CUSTOMER table, invoke the UDF and output the results. 此策略過於簡易且效率不彰。This strategy is naive and inefficient. 透過內嵌,這類 UDF 可轉換成相等的純量子查詢,這些子查詢會在呼叫查詢中替代 UDF。With inlining, such UDFs are transformed into equivalent scalar subqueries, which are substituted in the calling query in place of the UDF.

針對相同查詢,內嵌 UDF 的計劃如下所示。For the same query, the plan with the UDF inlined looks as below.

使用內嵌的查詢計劃

如先前所述,查詢計劃不再具有使用者定義函式運算子,但目前在計劃中可觀察到其效果,例如檢視或內嵌 TVF。As mentioned earlier, the query plan no longer has a user-defined function operator, but its effects are now observable in the plan, like views or inline TVFs. 以下是上述計劃的一些重要觀察結果:Here are some key observations from the above plan:

  1. SQL Server 已推斷 CUSTOMERORDERS 之間的隱含聯結,並透過聯結運算子使其變成明確聯結。SQL Server has inferred the implicit join between CUSTOMER and ORDERS and made that explicit via a join operator.
  2. SQL Server 還會推斷隱含的 GROUP BY O_CUSTKEY on ORDERS,並使用 IndexSpool + StreamAggregate 來進行實作。SQL Server has also inferred the implicit GROUP BY O_CUSTKEY on ORDERS and has used the IndexSpool + StreamAggregate to implement it.
  3. SQL Server 現在正在所有的運算子之間使用平行處理原則。SQL Server is now using parallelism across all operators.

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一個意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

視 UDF 的邏輯複雜度而定,產生的查詢計劃也可能會變得更大且更複雜。Depending upon the complexity of the logic in the UDF, the resulting query plan might also get bigger and more complex. 如我們所見,UDF 內的作業現在不再是黑盒子,因此查詢最佳化工具能夠估算這些作業的成本,並將其最佳化。As we can see, the operations inside the UDF are now no longer a black box, and hence the query optimizer is able to cost and optimize those operations. 此外,因為計劃中不再有 UDF,所以反覆執行 UDF 叫用會取代為完全避免函式呼叫額外負荷的計劃。Also, since the UDF is no longer in the plan, iterative UDF invocation is replaced by a plan that completely avoids function call overhead.

可內嵌的純量 UDF 需求Inlineable Scalar UDFs requirements

如果符合下列所有條件,則可以內嵌純量 T-SQL UDF:A scalar T-SQL UDF can be inline if all of the following conditions are true:

  • UDF 使用下列建構函式撰寫:The UDF is written using the following constructs:
    • DECLARESET:變數宣告和指派。DECLARE, SET: Variable declaration and assignments.
    • SELECT:包含單一/多個變數指派的 SQL 查詢1SELECT: SQL query with single/multiple variable assignments1.
    • IF/ELSE:使用任意巢狀層級的分支。IF/ELSE: Branching with arbitrary levels of nesting.
    • RETURN:單一或多個傳回陳述式。RETURN: Single or multiple return statements.
    • UDF:巢狀/遞迴函式呼叫2UDF: Nested/recursive function calls2.
    • 其他:EXISTSISNULL 等關聯式作業。Others: Relational operations such as EXISTS, ISNULL.
  • UDF 不會叫用任何與時間相依 (例如 GETDATE()) 或有副作用3 (例如 NEWSEQUENTIALID()) 的內建函式。The UDF does not invoke any intrinsic function that is either time-dependent (such as GETDATE()) or has side effects3 (such as NEWSEQUENTIALID()).
  • UDF 會使用 EXECUTE AS CALLER 子句 (如果未指定 EXECUTE AS 子句,則為預設行為)。The UDF uses the EXECUTE AS CALLER clause (the default behavior if the EXECUTE AS clause is not specified).
  • UDF 不會參考資料表變數或資料表值參數。The UDF does not reference table variables or table-valued parameters.
  • 叫用純量 UDF 的查詢不會在其 GROUP BY 子句中參考純量 UDF 呼叫。The query invoking a scalar UDF does not reference a scalar UDF call in its GROUP BY clause.
  • 在 SELECT 清單中使用 DISTINCT 子句叫用純量 UDF 的查詢,不會在其 ORDER BY 子句中參考純量 UDF 呼叫。The query invoking a scalar UDF in its select list with DISTINCT clause does not reference a scalar UDF call in its ORDER BY clause.
  • 不會以原生方式編譯 UDF (支援 Interop)。The UDF is not natively compiled (interop is supported).
  • UDF 不會用於計算資料行或檢查條件約束定義。The UDF is not used in a computed column or a check constraint definition.
  • UDF 不會參考使用者定義型別。The UDF does not reference user-defined types.
  • UDF 中沒有新增任何簽章。There are no signatures added to the UDF.
  • UDF 不是資料分割函式。The UDF is not a partition function.

1 內嵌不支援具有變數累積/彙總的 SELECT (例如 SELECT @val += col1 FROM table1)。1 SELECT with variable accumulation/aggregation (for example, SELECT @val += col1 FROM table1) is not supported for inlining.

2 遞迴 UDF 只能內嵌到特定深度。2 Recursive UDFs will be inlined to a certain depth only.

3 其結果取決於目前系統時間的內建函式具有時間相依性。3 Intrinsic functions whose results depend upon the current system time are time-dependent. 可能會更新某個內部全域狀態之內建函式為具有副作用的函式範例。An intrinsic function that may update some internal global state is an example of a function with side effects. 這類函式會在每次呼叫時,根據內部狀態傳回不同的結果。Such functions return different results each time they are called, based on the internal state.

檢查是否可以內嵌 UDFChecking whether or not a UDF can be inlined

針對每個 T-SQL 純量 UDF,sys.sql_modules 目錄檢視包含一個稱為 is_inlineable 的屬性,其表示是否可內嵌 UDF。For every T-SQL scalar UDF, the sys.sql_modules catalog view includes a property called is_inlineable, which indicates whether a UDF is inlineable or not. 值 1 表示可內嵌,而 0 表示不可以。A value of 1 indicates that it is inlineable, and 0 indicates otherwise. 對於所有內嵌 TVF,此屬性的值均為 1。This property will have a value of 1 for all inline TVFs as well. 對於其他所有模組,值會是 0。For all other modules, the value will be 0.

注意

如果純量 UDF 可內嵌,並不表示它一律都會內嵌。If a scalar UDF is inlineable, it does not imply that it will always be inlined. SQL Server 將決定 (在每個查詢上,根據每個 UDF) 是否要內嵌 UDF。SQL Server will decide (on a per-query, per-UDF basis) whether to inline a UDF or not. 比方說,如果 UDF 定義達到數千行程式碼時,SQL Server 可能會選擇不要加以內嵌。For instance, if the UDF definition runs into thousands of lines of code, SQL Server might choose not to inline it. 另一個範例是 GROUP BY 子句中不會內嵌的 UDF。Another example is a UDF in a GROUP BY clause - which will not be inlined. 這項決定會在編譯參考純量 UDF 的查詢時進行。This decision is made when the query referencing a scalar UDF is compiled.

檢查是否已進行內嵌Checking whether inlining has happened or not

如果符合所有先決條件,且 SQL Server 決定執行內嵌,則它會將 UDF 轉換成關聯運算式。If all the preconditions are satisfied and SQL Server decides to perform inlining, it transforms the UDF into a relational expression. 您可以從查詢計劃輕鬆地看出是否已進行內嵌:From the query plan, it is easy to figure out whether inlining has happened or not:

  • 針對已成功內嵌的 UDF,計劃 XML不會有 <UserDefinedFunction> XML 節點。The plan xml will not have a <UserDefinedFunction> xml node for a UDF that has been inlined successfully.
  • 會發出特定 XEvent。Certain XEvents are emitted.

啟用純量 UDF 內嵌Enabling scalar UDF inlining

您可以啟用資料庫的相容性層級 150,讓工作負載自動符合純量 UDF 內嵌的資格。You can make workloads automatically eligible for scalar UDF inlining by enabling compatibility level 150 for the database.? 您可以使用 Transact-SQL 設定此項目。例如:You can set this using Transact-SQL.?For example:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

除此之外,不需要對 UDF 或查詢進行任何其他變更,就能夠利用這項功能。Apart from this, there are no other changes required to be made to UDFs or queries to take advantage of this feature.

停用純量 UDF 內嵌而不變更相容性層級Disabling Scalar UDF inlining without changing the compatibility level

您可以在資料庫、陳述式或 UDF 的範圍停用純量 UDF 內嵌,同時仍將資料庫相容性層級維持在 150 以上。Scalar UDF inlining can be disabled at the database, statement, or UDF scope while still maintaining database compatibility level 150 and higher. 若要在資料庫範圍停用純量 UDF 內嵌,請在適用資料庫的內容中執行下列陳述式:To disable scalar UDF inlining at the database scope, execute the following statement within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

若要為資料庫重新啟用純量 UDF 內嵌,請在適用資料庫的內容中執行下列陳述式:To re-enable scalar UDF inlining for the database, execute the following statement within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

當設為 ON 時,此設定會在 sys.database_scoped_configurations 中顯示為已啟用。When ON, this setting will appear as enabled in sys.database_scoped_configurations. 您也可以將 DISABLE_TSQL_SCALAR_UDF_INLINING 指定為 USE HINT 查詢提示,以停用特定查詢的純量 UDF 內嵌。You can also disable scalar UDF inlining for a specific query by designating DISABLE_TSQL_SCALAR_UDF_INLINING as a USE HINT query hint. 例如:For example:

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT)) 
FROM LINEITEM
INNER JOIN ORDERS
  ON O_ORDERKEY = L_ORDERKEY 
GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

USE HINT 查詢提示的優先順序高於資料庫範圍設定或相容性層級設定。A USE HINT query hint takes precedence over the database scoped configuration or compatibility level setting.

您也可以使用 CREATE FUNCTIONALTER FUNCTION 陳述式中的 INLINE 子句來停用特定 UDF 的純量 UDF 內嵌。Scalar UDF inlining can also be disabled for a specific UDF using the INLINE clause in the CREATE FUNCTION or ALTER FUNCTION statement. 例如:For example:

CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))
RETURNS DECIMAL (12,2)
WITH INLINE = OFF
AS
BEGIN
    RETURN @price * (1 - @discount);
END

一旦執行上述陳述式,絕對不會將此 UDF 內嵌至叫用它的任何查詢中。Once the above statement is executed, this UDF will never be inlined into any query that invokes it. 若要重新啟用內嵌此 UDF 的功能,請執行下列陳述式:To re-enable inlining for this UDF, execute the following statement:

CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))
RETURNS DECIMAL (12,2)
WITH INLINE = ON
AS
BEGIN
    RETURN @price * (1 - @discount);
END

注意

INLINE 子句非為強制性。The INLINE clause is not mandatory. 如果未指定 INLINE 子句,它會根據是否可以內嵌 UDF 自動設為 ON/OFFIf INLINE clause is not specified, it is automatically set to ON/OFF based on whether the UDF can be inlined. 如果指定了 INLINE=ON,但發現 UDF 不適合進行內嵌,則會擲回錯誤。If INLINE=ON is specified but the UDF is found ineligible for inlining, an error will be thrown.

重要事項Important Notes

如本文所述,純量 UDF 內嵌會將具有純量 UDF 的查詢轉換成具有對等純量子查詢的查詢。As described in this article, scalar UDF inlining transforms a query with scalar UDFs into a query with an equivalent scalar subquery. 由於此轉換之故,使用者可能會注意到下列案例中的一些行為差異:Due to this transformation, users may notice some differences in behavior in the following scenarios:

  1. 內嵌會導致相同的查詢文字產生不同查詢雜湊。Inlining will result in a different query hash for the same query text.
  2. UDF 內先前可能已隱藏的陳述式特定警告 (例如除以零等等),可能會因內嵌而顯示出來。Certain warnings in statements inside the UDF (such as divide by zero etc.) which might have been hidden earlier, might show up due to inlining.
  3. 查詢層級的聯結提示可能不再有效,因為內嵌可能會引進新的聯結。Query level join hints might not be valid anymore, as inlining may introduce new joins. 必須改為使用本機聯結提示。Local join hints will have to be used instead.
  4. 參考內嵌純量 UDF 的檢視無法編製索引。Views that reference inline scalar UDFs cannot be indexed. 如果您需要在這類檢視上建立索引,請停用所參考 UDF 的內嵌功能。If you need to create an index on such views, disable inlining for the referenced UDFs.
  5. 動態資料遮罩與內嵌 UDF 的行為可能有一些差異。There might be some differences in the behavior of Dynamic Data masking with UDF inlining. 在某些情況下 (視 UDF 中的邏輯而定),對於遮罩輸出資料行,內嵌可能更為保守。In certain situations (depending upon the logic in the UDF), inlining might be more conservative w.r.t masking output columns. 在 UDF 中所參考資料行不是輸出資料行的情況下,它們不會被遮罩。In scenarios where the columns referenced in a UDF are not output columns, they will not be masked.
  6. 如果 UDF 參考內建函式 (例如 SCOPE_IDENTITY()),則內建函式所傳回的值會隨著內嵌而變更。If a UDF references built-in functions such as SCOPE_IDENTITY(), the value returned by the built-in function will change with inlining. 此行為變更是因為內嵌變更了陳述式在 UDF 內的範圍。This change in behavior is because inlining changes the scope of statements inside the UDF.

另請參閱See Also

SQL Server Database Engine 和 Azure SQL Database 的效能中心Performance Center for SQL Server Database Engine and Azure SQL Database

查詢處理架構指南Query Processing Architecture Guide

執行程序邏輯和實體運算子參考Showplan Logical and Physical Operators Reference

聯結Joins

示範彈性查詢處理Demonstrating Adaptive Query Processing