Inserción de UDF escalar

Artículo
10/15/2024

Se aplica a: SQL Server 2019 (15.x) Base de datos de Azure SQL Azure SQL Managed Instance

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

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 un coste extra 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 costes. 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 llaman 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 ya no tiene un operador de función definido por el usuario, pero sus efectos se observan en el plan, como vistas o funciones con valores de tabla insertados (TVF).

Ejemplos

En los ejemplos de esta sección se usa la base de datos comparativa TPC-H. Para obtener más información, consulte la página de inicio de TPC-H.

A UDF escalar con una 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;

La consulta con la UDF tiene un rendimiento bajo debido a las razones descritas anteriormente. Con la 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.

B. UDF escalar con 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 AS DECIMAL (18, 2);
    DECLARE @category AS 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:

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.

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 deduce la combinación implícita entre CUSTOMER y ORDERS la convierte en explícita a través de un operador de combinación.
SQL Server también infiere la cláusula GROUP BY O_CUSTKEY on ORDERS implícita y usa 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

Se puede insertar una UDF de T-SQL escalar si la definición de función usa construcciones permitidas y la función se usa en un contexto que habilita la inserción:

Todas las condiciones siguientes de la definición de UDF deben ser verdaderas:

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 UDF no se compila de forma nativa (se admite la interoperabilidad).
La UDF no hace referencia a tipos definidos por el usuario.
No hay firmas agregadas 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 podrían 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 ⁶.
La UDF no hace referencia a la función STRING_AGG ⁶.
La UDF no hace referencia a las tablas remotas ⁷.
La UDF no hace referencia a las columnas cifradas ⁸.
La UDF no contiene referencias a WITH XMLNAMESPACES ⁸.
Si la definición de UDF se ejecuta en miles de líneas de código, es posible que SQL Server decida no insertarla.

¹ SELECT con acumulación o agregación variable no se admite para la inserción (como 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 pueda 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) CU 2

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

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

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

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

⁹ Dado que las firmas se pueden agregar y quitar después de crear una UDF, la decisión de si se realiza en línea 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.

Todo el requisito siguiente del contexto de ejecución debe ser true:

La UDF no se usa en la cláusula ORDER BY.
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.
No se llama a la UDF desde una instrucción ^{RETURN 1}.
La consulta que invoca la UDF no tiene expresiones de tabla comunes (CTE) ³.
La consulta de llamada a UDF no usa GROUPING SETS, CUBEo 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 se usa en una columna calculada ni en una definición de restricción check.

¹ Restricción agregada en SQL Server 2019 (15.x) CU 5

² Restricción agregada en SQL Server 2019 (15.x) CU 6

³ Restricción agregada en SQL Server 2019 (15.x) CU 11

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.

Comprobar si se puede insertar una UDF

Para cada UDF escalar de T-SQL, la vista de catálogo sys.sql_modules incluye una propiedad denominada is_inlineable, que indica si una UDF es insertable.

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 la UDF es insertable e 0 indica lo contrario. Esta propiedad también tiene un valor de 1 para todas las funciones con valores de tabla insertadas. Para todos los demás módulos, el valor es 0.

Si una UDF escalar es insertable, no implica que siempre se inserte. SQL Server decide (por consulta, por UDF) si se debe insertar una UDF. Consulte las listas de requisitos anteriores en este artículo.

SELECT *
FROM sys.crypt_properties AS cp
     INNER JOIN sys.objects AS o
         ON cp.major_id = o.object_id;

Comprobar si se ha producido la inserción

Si se cumplen todas las condiciones previas y SQL Server decide realizar la inserción, transforma la UDF en una expresión relacional. En el plan de consulta, puede averiguar si se produjo la esquematización:

El XML del plan no tiene un nodo XML <UserDefinedFunction> para una UDF que se inserte correctamente.
Se emiten determinados eventos extendidos.

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 este padp, 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 escalares se puede deshabilitar en el ámbito de la base de datos, la instrucción o la UDF mientras se mantiene el nivel de compatibilidad de base de datos 150 o superior. Para deshabilitar la inserción de UDF escalares en el ámbito de 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 = 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 ON, esta configuració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 inserta en ninguna consulta que la invoque. 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 apta, se producirá un error.

Comentarios

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 da 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 habrá que 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 enmascaran.
Si una UDF hace referencia a funciones integradas como SCOPE_IDENTITY(), @@ROWCOUNT o @@ERROR, con la inserción se cambia 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 FORCESEEK Sugerencias de consulta, se produce el error 8622, lo que indica que el procesador de consultas no pudo generar un plan de consulta debido a las sugerencias definidas en la consulta.

Compartir a través de

Inserción de UDF escalar

Funciones escalares definidas por el usuario de T-SQL

Rendimiento de las UDF escalares

Inserción automática de UDF escalares

Ejemplos

A UDF escalar con una instrucción

B. UDF escalar con varias instrucciones

Requisitos de las UDF escalares insertables

Comprobar si se puede insertar una UDF

Comprobar si se ha producido la inserción

Habilitación de la inserción de UDF escalar

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

Comentarios

Comentarios

Recursos adicionales

Compartir a través de

Inserción de UDF escalar

Funciones escalares definidas por el usuario de T-SQL

Rendimiento de las UDF escalares

Inserción automática de UDF escalares

Ejemplos

A UDF escalar con una instrucción

B. UDF escalar con varias instrucciones

Requisitos de las UDF escalares insertables

Comprobar si se puede insertar una UDF

Comprobar si se ha producido la inserción

Habilitación de la inserción de UDF escalar

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

Comentarios

Contenido relacionado

Comentarios

Recursos adicionales