Inserción de UDF escalares

Artículo
09/03/2023

Se aplica a: SQL Server 2019 (15.x) Azure SQL Database Azure SQL Managed Instance

En este artículo se presenta Inserción de UDF escalar, una característica del conjunto de características de procesamiento de consultas inteligentes. Esta característica mejora el rendimiento de las consultas que llaman a UDF escalares en SQL Server (a partir de SQL Server 2019 (15.x)).

Funciones escalares definidas por el usuario de T-SQL

Las funciones definidas por el usuario (UDF) que se implementan en Transact-SQL y que devuelven un único valor de datos se conocen como funciones escalares definidas por el usuario de T-SQL. Las UDF de T-SQL son una forma elegante de lograr la reutilización y modularidad del código en todas las consultas de Transact-SQL. Algunos cálculos (como las reglas de negocios complejas) son más fáciles de expresar en forma de UDF imperativa. Las UDF ayudan a crear una lógica compleja, sin necesidad de tener experiencia en escribir consultas de SQL complejas. Para obtener más información sobre las UDF, vea Creación de funciones definidas por el usuario (motor de base de datos).

Rendimiento de las UDF escalares

Las UDF escalares suelen tener un rendimiento deficiente debido a las razones siguientes:

Invocación iterativa. las UDF se invocan de forma iterativa, una vez por cada tupla certificada. Esto supone costos adicionales de cambio de contexto repetido debido a la invocación de funciones. En concreto, las UDF que ejecutan consultas de Transact-SQL en su definición se ven gravemente afectadas.
Falta de costos: Durante la optimización, solo se calcula el costo de los operadores relacionales, mientras que el de los operadores escalares no. Antes de la introducción de las UDF escalares, otros operadores escalares eran generalmente baratos y no requerían una estimación de los costos. Un pequeño costo de CPU agregado para una operación escalar era suficiente. Hay escenarios donde el costo real es significativo y, aun así, se sigue representando de forma insuficiente.
Ejecución interpretada: las UDF se evalúan como un lote de instrucciones, y se ejecutan instrucción por instrucción. Se compila cada instrucción y el plan compilado se almacena en caché. Aunque esta estrategia de almacenamiento en caché ahorra algo de tiempo porque evita las recompilaciones, cada instrucción se ejecuta de forma aislada. No se realizan optimizaciones entre instrucciones.
Ejecución en serie: SQL Server no admite el paralelismo entre consultas en las consultas que invocan las UDF.

Inserción automática de UDF escalares

El objetivo de la característica Inserción de UDF escalar es mejorar el rendimiento de las consultas que invocan a UDF escalares de T-SQL, donde la ejecución de la UDF es el principal cuello de botella.

Con esta nueva característica, las UDF escalares se transforman automáticamente en expresiones o subconsultas escalares que se sustituyen en la consulta que realiza la llamada en lugar del operador de UDF. Después, estas expresiones y subconsultas se optimizan. Como resultado, el plan de consulta dejará de tener un operador de función definida por el usuario, pero sus efectos se tendrán en cuenta en el plan, como las vistas o las funciones con valores de tabla insertadas.

Ejemplo 1: UDF escalar de una sola instrucción

Considere la consulta siguiente.

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;

Esta consulta calcula la suma de los precios con descuento para los artículos de línea y presenta los resultados agrupados por fecha de envío y prioridad de envío. La expresión L_EXTENDEDPRICE *(1 - L_DISCOUNT) es la fórmula para el precio con descuento para un determinado artículo de línea. Estas fórmulas se pueden extraer en funciones para el beneficio de la modularidad y la reutilización.

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

Ahora se puede modificar la consulta para invocar esta 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

Debido a las razones descritas anteriormente, la consulta con la UDF tiene un rendimiento bajo. Ahora, con Inserción de UDF escalar, la expresión escalar en el cuerpo de la UDF se sustituye directamente en la consulta. Los resultados de ejecutar esta consulta se muestran en la tabla siguiente:

Consulta:	Consulta sin UDF	Consulta con UDF (sin inserción)	Consulta con Inserción de UDF escalar
Tiempo de ejecución:	1,6 segundos	29 minutos 11 segundos	1,6 segundos

Estos números se basan en una base de datos de CCI de 10 GB (con el esquema de TPC-H), que se ejecuta en un equipo con procesador dual (12 núcleos), 96 GB de RAM, respaldado por SSD. Los números incluyen el tiempo de compilación y ejecución con un procedimiento pasivo de almacenamiento en caché y un grupo de búferes. Se ha usado la configuración predeterminada y no se han creado otros índices.

Ejemplo 2: UDF escalar de varias instrucciones

Las UDF escalares que se implementan mediante varias instrucciones de T-SQL, como las asignaciones de variables y las bifurcaciones condicionales, también se pueden insertar. Observe la siguiente UDF escalar que, dada una clave de cliente, determina la categoría de servicio para ese cliente. Para llegar a la categoría, primero calcula el precio total de todos los pedidos realizados por el cliente mediante una consulta SQL. Después, usa una instrucción IF (...) ELSE lógica para decidir la categoría en función del precio total.

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

Ahora, considere la posibilidad de una consulta que invoca esta UDF.

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

El plan de ejecución para esta consulta en SQL Server 2017 (14.x) (nivel de compatibilidad 140 y versiones anteriores) es el siguiente:

Query Plan without inlining.

Como se muestra en el plan, SQL Server adopta aquí una estrategia sencilla: para cada tupla de la tabla CUSTOMER, se invoca la UDF y se muestran los resultados. Esta estrategia es ingenua e ineficaz. Con la inserción, esas UDF se transforman en subconsultas escalares equivalentes, que se sustituyen en la consulta que realiza la llamada en lugar de la UDF.

Para la misma consulta, el plan con la UDF insertada tiene este aspecto.

Query Plan with inlining.

Como se ha mencionado antes, el plan de consulta ya no tiene un operador de función definida por el usuario, pero sus efectos se tienen en cuenta en el plan, como las vistas o las funciones con valores de tabla insertadas. Estas son algunas observaciones clave del plan anterior:

SQL Server ha inferido la combinación implícita entre CUSTOMER y ORDERS, y la ha convertido en explícita a través de un operador de combinación.
SQL Server también ha inferido la cláusula GROUP BY O_CUSTKEY on ORDERS implícita y ha usado IndexSpool y StreamAggregate para implementarla.
Ahora SQL Server usa el paralelismo de todos los operadores.

Según la complejidad de la lógica de la UDF, es posible que el plan de consulta resultante también aumente de tamaño y complejidad. Como se puede ver, las operaciones dentro de la UDF ahora ya no son opacas y, por tanto, el optimizador de consultas es capaz de calcular los costos de esas operaciones y optimizarlas. Además, como la UDF ya no está en el plan, la invocación iterativa de UDF se sustituye por un plan que evita totalmente la sobrecarga de la llamada de función.

Requisitos de las UDF escalares insertables

Una UDF escalar de T-SQL se puede insertar si se cumplen todas las condiciones siguientes:

La UDF se escribe con las construcciones siguientes:
- DECLARE, SET: declaración de variables y asignaciones.
- SELECT: consulta SQL con asignaciones de una o múltiples variables ¹.
- IF/ELSE: bifurcación con niveles de anidamiento arbitrarios.
- RETURN: una o varias instrucciones RETURN. A partir de SQL Server 2019 (15.x) CU5, la UDF solo puede contener una única instrucción RETURN que se debe considerar para la inserción ⁶.
- UDF: llamadas de función anidadas o recursivas ².
- Otros: operaciones relacionales como EXISTS, IS NULL.
La UDF no invoca ninguna función intrínseca dependiente del tiempo (como GETDATE()) o tiene efectos secundarios ³ (como NEWSEQUENTIALID()).
La UDF usa la cláusula EXECUTE AS CALLER (comportamiento predeterminado si no se especifica la cláusula EXECUTE AS).
La UDF no hace referencia a variables de tabla ni parámetros con valores de tabla.
La consulta que invoca una UDF escalar no hace referencia a una llamada de UDF escalar en su cláusula GROUP BY.
La consulta que invoca una UDF escalar en su lista de selección con cláusula DISTINCT no tiene una cláusula ORDER BY.
La UDF no se usa en la cláusula ORDER BY.
La UDF no se compila de forma nativa (se admite la interoperabilidad).
La UDF no se usa en una columna calculada ni en una definición de restricción check.
La UDF no hace referencia a tipos definidos por el usuario.
No se agrega ninguna firma a la UDF.
La UDF no es una función de partición.
La UDF no contiene referencias a expresiones de tabla comunes (CTE).
La UDF no contiene referencias a funciones intrínsecas que pueden modificar los resultados al insertar (como @@ROWCOUNT) ⁴.
La UDF no contiene funciones de agregado que se pasan como parámetros a una UDF escalar ⁴.
La UDF no hace referencia a vistas integradas (como OBJECT_ID) ⁴.
La UDF no hace referencia a los métodos XML ⁵.
La UDF no contiene una instrucción SELECT con ORDER BY sin una cláusula TOP 1⁵.
La UDF no contiene una consulta SELECT que realiza una asignación con la cláusula ORDER BY (como SELECT @x = @x + 1 FROM table1 ORDER BY col1) ⁵.
La UDF no contiene varias instrucciones RETURN ⁶.
No se llama a la UDF desde una instrucción RETURN ⁶.
La UDF no hace referencia a la función STRING_AGG⁶.
La UDF no hace referencia a las tablas remotas ⁷.
La consulta de llamada ADF no usa GROUPING SETS, CUBE o ROLLUP⁷.
La consulta de llamada a UDF no contiene una variable que se usa como parámetro UDF para la asignación (por ejemplo, SELECT @y = 2, @x = UDF(@y)) ⁷.
La UDF no hace referencia a las columnas cifradas ⁸.
La UDF no contiene referencias a WITH XMLNAMESPACES⁸.
La consulta que invoca la UDF no tiene expresiones de tabla comunes (CTE) ⁸.

¹SELECT con acumulación o agregación variable no se admite para la inserción (por SELECT @val += col1 FROM table1).

² Las UDF recursivas solo se insertan hasta una profundidad concreta.

³ Las funciones intrínsecas cuyos resultados dependen de la hora actual del sistema son dependientes de la hora. Una función intrínseca que puede actualizar algún estado global interno es un ejemplo de una función con efectos secundarios. Estas funciones devuelven resultados diferentes cada vez que se llaman, en función del estado interno.

⁴ Restricción agregada en SQL Server 2019 (15.x) CU2

⁵ Restricción agregada en SQL Server 2019 (15.x) CU4

⁶ Restricción agregada en SQL Server 2019 (15.x) CU5

⁷ Restricción agregada en SQL Server 2019 (15.x) CU6

⁸ Restricción agregada en SQL Server 2019 (15.x) CU11

Para obtener información sobre las correcciones más recientes de la inserción de UDF escalares de T-SQL y los cambios en escenarios de elegibilidad de inserción, vea el artículo de Knowledge Base: FIX: Problemas de inserción de UDF escalares en SQL Server 2019.

Compruebe si una UDF se puede insertar o no

Para todas las UDF escalares de T-SQL, la vista de catálogo sys.sql_modules incluye una propiedad denominada is_inlineable, que indica si una UDF se puede insertar o no.

La propiedad is_inlineable se deriva de las construcciones que se encuentran dentro de la definición de UDF. No comprueba si la UDF es de hecho inlineable en tiempo de compilación. Para más información, vea las condiciones para la inserción.

Un valor de 1 indica que se puede insertar y 0 indica lo contrario. Esta propiedad también tendrá un valor de 1 para todas las funciones con valores de tabla insertadas. Para todos los demás módulos, el valor será 0.

Si una UDF escalar es insertable, no implica que siempre se inserte. SQL Server decidirá (por cada consulta y UDF), si una UDF se inserta o no. Algunos ejemplos de cuándo una UDF no se puede insertar incluyen:

Si la definición de UDF se ejecuta en miles de líneas de código, es posible que SQL Server decida no insertarla.
No se insertará una invocación de UDF en una cláusula GROUP BY. Esta decisión se toma cuando se compila la consulta que hace referencia a una UDF escalar.
Si la UDF está firmada con un certificado. Dado que las firmas se pueden agregar y quitar después de crear una UDF, la decisión de si se va a insertar o no se toma cuando se compila la consulta que hace referencia a una UDF escalar. Por ejemplo, las funciones del sistema suelen estar firmadas con un certificado. Puede usar sys.crypt_properties para encontrar los objetos que están firmados.
```
SELECT *
FROM sys.crypt_properties AS cp
INNER JOIN sys.objects AS o ON cp.major_id = o.object_id;
```

Compruebe si la inserción se ha producido o no

Si se cumplen todas las condiciones previas y SQL Server decide realizar la inserción, transforma la UDF en una expresión relacional. Desde el plan de consulta, es fácil averiguar si la esquematización se ha producido o no:

El xml del plan no tendrá un nodo xml <UserDefinedFunction> para una UDF que se haya insertado correctamente.
Se emiten determinados XEvents.

Habilitación de la inserción de UDF escalar

Puede hacer que las cargas de trabajo sean aptas automáticamente para la inserción de UDF escalar si habilita el nivel de compatibilidad 150 para la base de datos. Puede establecerlo con Transact-SQL. Por ejemplo:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

Aparte de esto, no hay que realizar ningún otro cambio en las UDF o las consultas para aprovechar esta característica.

Deshabilitación de la inserción de UDF escalar sin cambiar el nivel de compatibilidad

La inserción de UDF escalar se puede deshabilitar en la base de datos, la instrucción o el ámbito de UDF, al tiempo que se mantiene el nivel de compatibilidad de la base de datos 150 y versiones posteriores. Para deshabilitar la inserción de UDF escalar en el ámbito de la base de datos, ejecute la siguiente instrucción en el contexto de la base de datos aplicable:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Para volver a habilitar la inserción de UDF escalares para la base de datos, ejecute la instrucción siguiente en el contexto de la base de datos aplicable:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Cuando es ON, esta opción aparece como habilitada en sys.database_scoped_configurations. También puede deshabilitar la inserción de UDF escalares para una consulta específica mediante la designación de DISABLE_TSQL_SCALAR_UDF_INLINING como una sugerencia de consulta USE HINT.

Una sugerencia de consulta USE HINT tiene prioridad sobre una configuración de ámbito de base de datos o una opción de nivel de compatibilidad.

Por ejemplo:

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

La inserción de UDF escalares también se puede deshabilitar para una UDF específica mediante la cláusula INLINE en la instrucción CREATE FUNCTION o ALTER FUNCTION. Por ejemplo:

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;

Una vez que se ejecuta la instrucción anterior, esta UDF nunca se insertará en ninguna consulta que la invoca. Para volver a habilitar la inserción para esta UDF, ejecute la instrucción siguiente:

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

La cláusula INLINE no es obligatoria. Si no se especifica la cláusula INLINE, se establece en ON/OFF automáticamente en función de si la UDF se puede insertar. Si se especifica INLINE = ON pero se detecta que la UDF no es insertable, se producirá un error.

Notas importantes

Como se describe en este artículo, la inserción de UDF escalar transforma una consulta con UDF escalares en una consulta con una subconsulta escalar equivalente. Debido a esta transformación, es posible que los usuarios observen algunas diferencias de comportamiento en los escenarios siguientes:

La inserción dará como resultado otro valor de hash de consulta para el mismo texto de consulta.
Es posible que, debido a la inserción, aparezcan algunas advertencias en las instrucciones dentro de la UDF (como la división por cero, etc.) que antes podrían estar ocultas.
Es posible que las sugerencias de combinación de nivel de consulta ya no sean válidas, ya que la inserción puede introducir nuevas combinaciones. En su lugar será necesario usar sugerencias de combinación locales.
Las vistas que hacen referencia a UDF escalares insertadas no se pueden indexar. Si tiene que crear un índice en esas vistas, deshabilite la inserción para las UDF a las que se hace referencia.
Puede haber algunas diferencias en el comportamiento del enmascaramiento dinámico de datos con la inserción de UDF.

En determinadas situaciones (dependiendo de la lógica de la UDF), la inserción podría ser más conservadora con respecto al enmascaramiento de columnas de salida. En escenarios en los que las columnas a las que se hace referencia en una UDF no son columnas de salida, no se enmascararán.
Si una UDF hace referencia a funciones integradas como SCOPE_IDENTITY(), @@ROWCOUNT o @@ERROR, con la inserción se cambiará el valor devuelto por la función integrada. Este cambio de comportamiento se debe a que la inserción modifica el ámbito de las instrucciones dentro de la UDF. A partir de SQL Server 2019 (15.x) CU2, la inserción se bloquea si la UDF hace referencia a determinadas funciones intrínsecas (por ejemplo, @@ROWCOUNT).
Si se asigna una variable con el resultado de una UDF insertada y también se usa como index_column_name en la sugerencia de consulta FORCESEEK, se producirá el error Msg 8622 que indica que el procesador de consultas no pudo generar un plan de consulta debido a las sugerencias definidas en la consulta.