• 08/04/2020
• 12 minutos para leer
• • s
  • M
  • M
  • p
  • j
  • +6

Se aplica a: SQL Server (todas las versiones soportadas)

Este artículo presenta Scalar UDF Inlining, una característica bajo el conjunto de características de Intelligent Query Processing. Esta característica mejora el rendimiento de las consultas que invocan UDF escalares en SQL Server (a partir de SQL Server 2019 (15.x)).

Funciones definidas por el usuario escalares T-SQL

Las funciones definidas por el usuario (UDF) que se implementan en Transact-SQL y devuelven un único valor de datos se denominan funciones definidas por el usuario escalares T-SQL. Las UDFs de T-SQL son una forma elegante de lograr la reutilización del código y la modularidad a través de las consultas Transact-SQL. Algunos cálculos (como las reglas de negocio complejas) son más fáciles de expresar en forma UDF imperativa. Los UDFs ayudan a construir una lógica compleja sin requerir experiencia en la escritura de consultas SQL complejas. Para obtener más información sobre las UDF, consulte Crear 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 siguientes razones:

• Invocación iterativa: Los UDFs son invocados de manera iterativa, una vez por cada tupla calificada. Esto incurre en costes adicionales de cambio de contexto repetido debido a la invocación de funciones. Especialmente, las UDFs que ejecutan consultas Transact-SQL en su definición se ven gravemente afectadas.

• Falta de costes: Durante la optimización, sólo se calcula el coste de los operadores relacionales, mientras que los operadores escalares no. Antes de la introducción de los UDFs escalares, otros operadores escalares eran generalmente baratos y no requerían un coste. Un pequeño coste de CPU añadido para una operación escalar era suficiente. Hay escenarios en los que el coste real es significativo y, sin embargo, sigue estando poco representado.

• Ejecución interpretada: Los UDFs se evalúan como un lote de sentencias, ejecutadas sentencia por sentencia. Cada sentencia se compila y el plan compilado se almacena en caché. Aunque esta estrategia de almacenamiento en caché ahorra algo de tiempo, ya que evita las recompilaciones, cada sentencia se ejecuta de forma aislada. No se llevan a cabo optimizaciones entre sentencias.

• Ejecución en serie: SQL Server no permite el paralelismo intraconsulta en las consultas que invocan UDFs.

Inlining automático de UDFs escalares

El objetivo de la función de inlining de UDFs escalares es mejorar el rendimiento de las consultas que invocan UDFs escalares T-SQL, donde la ejecución de UDFs es el principal cuello de botella.

Con esta nueva función, las UDF escalares se transforman automáticamente en expresiones escalares o subconsultas escalares que se sustituyen en la consulta de llamada en lugar del operador UDF. Estas expresiones y subconsultas se optimizan a continuación. Como resultado, el plan de consulta ya no tendrá un operador de función definida por el usuario, pero sus efectos se observarán en el plan, como las vistas o las TVF en línea.

Ejemplo 1 – UDF escalar de una sola sentencia

Considere la siguiente consulta.

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (L_EXTENDEDPRICE *(1 - L_DISCOUNT)) FROM LINEITEMINNER 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 de los artículos de línea y presenta los resultados agrupados por la fecha de envío y la prioridad de envío. La expresión L_EXTENDEDPRICE *(1 - L_DISCOUNT) es la fórmula del precio con descuento para una partida determinada. Este tipo de fórmulas pueden extraerse en funciones en 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) ASBEGIN RETURN @price * (1 - @discount);END

Ahora la consulta puede modificarse para invocar este UDF.

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

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

Estos números se basan en una base de datos CCI de 10 GB (utilizando el esquema TPC-H), que se ejecuta en una máquina con doble procesador (12 núcleos), 96 GB de RAM, respaldada por SSD. Las cifras incluyen el tiempo de compilación y ejecución con una caché de procedimientos en frío y un grupo de búferes. Se utilizó la configuración por defecto, y no se crearon otros índices.

Ejemplo 2 – UDF escalar de múltiples sentencias

Las UDF escalares que se implementan utilizando múltiples sentencias T-SQL, como las asignaciones de variables y las bifurcaciones condicionales, también pueden ser inline. Considere la siguiente UDF escalar que, dada una clave de cliente, determina la categoría de servicio para ese cliente. Llega a la categoría calculando primero el precio total de todos los pedidos realizados por el cliente mediante una consulta SQL. A continuación, utiliza una 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) ASBEGIN 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 una consulta que invoca este UDF.

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

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

Como muestra el plan, SQL Server adopta aquí una estrategia simple: por cada tupla de la tabla CUSTOMER, invoca el UDF y emite los resultados. Esta estrategia es ingenua e ineficiente. Con el inlining, estos UDFs se transforman en subconsultas escalares equivalentes, que se sustituyen en la consulta de llamada en lugar del UDF.

Para la misma consulta, el plan con el UDF inlined tiene el siguiente aspecto.

Como se mencionó anteriormente, el plan de consulta ya no tiene un operador de función definida por el usuario, pero sus efectos son ahora observables en el plan, como las vistas o los TVF inline. He aquí algunas observaciones clave del plan anterior:

• SQL Server ha inferido la unión implícita entre CUSTOMER y ORDERS y la ha hecho explícita mediante un operador de unión.
• SQL Server también ha inferido el GROUP BY O_CUSTKEY on ORDERS implícito y ha utilizado el IndexSpool + StreamAggregate para implementarlo.
• SQL Server está utilizando ahora el paralelismo a través de todos los operadores.

Dependiendo de la complejidad de la lógica en el UDF, el plan de consulta resultante también podría hacerse más grande y complejo. Como podemos ver, las operaciones dentro del UDF ya no son una caja opaca y, por lo tanto, el optimizador de consultas es capaz de calcular el coste y optimizar esas operaciones. Además, como el UDF ya no está en el plan, la invocación iterativa del UDF se sustituye por un plan que evita completamente la sobrecarga de las llamadas a funciones.

Requisitos de las UDFs escalares inlineables

Una UDF T-SQL escalar puede ser inlineada si se cumplen todas las condiciones siguientes:

• La UDF está escrita utilizando las siguientes construcciones:
  • DECLARESET: Declaración y asignaciones de variables.
  • SELECT: Consulta SQL con asignaciones de variables simples/múltiples 1.
  • IFELSE: Bifurcación con niveles arbitrarios de anidamiento.
  • RETURN: Sentencias de retorno simples o múltiples.
  • UDF: Llamadas a funciones anidadas/recursivas 2.
  • Otros: Operaciones relacionales como EXISTSISNULL.
• El UDF no invoca ninguna función intrínseca que dependa del tiempo (como GETDATE()) o que tenga efectos secundarios 3 (como NEWSEQUENTIALID()).
• El UDF utiliza la cláusula EXECUTE AS CALLER (comportamiento por defecto si no se especifica la cláusula EXECUTE AS).
• La UDF no hace referencia a variables de tabla ni a parámetros con valores de tabla.
• La consulta que invoca una UDF escalar no hace referencia a una llamada a una UDF escalar en su cláusula GROUP BY.
• La consulta que invoca una UDF escalar en su lista select con la cláusula DISTINCT no tiene la cláusula ORDER BY.
• El UDF no se utiliza en la cláusula ORDER BY.
• El UDF no se compila de forma nativa (se admite la interoperabilidad).
• El UDF no se utiliza en una columna computada o en una definición de restricción de verificación.
• El UDF no hace referencia a tipos definidos por el usuario.
• No hay firmas añadidas al UDF.
• El UDF no es una función de partición.
• El UDF no contiene referencias a expresiones de tabla comunes (CTE).
• El UDF no contiene referencias a funciones intrínsecas que puedan alterar los resultados cuando se inlinean (como @@ROWCOUNT) 4.
• El UDF no contiene funciones agregadas que se pasen como parámetros a un UDF escalar 4.
• El UDF no hace referencia a vistas incorporadas (como OBJECT_ID) 4.
• El UDF no hace referencia a métodos XML 5.
• El UDF no contiene un SELECT con ORDER BY sin una cláusula TOP 1 5.
• La UDF no contiene una consulta SELECT que realice una asignación junto con la cláusula ORDER BY (como SELECT @x = @x + 1 FROM table1 ORDER BY col1) 5.
• El UDF no contiene múltiples declaraciones RETURN 6.
• El UDF no se llama desde una declaración RETURN 6.
• El UDF no hace referencia a la función STRING_AGG 6.
• La UDF no hace referencia a tablas remotas 7.
• La consulta que llama al UDF no utiliza GROUPING SETSCUBE, o ROLLUP
• La consulta que llama a la UDF no contiene una variable que se utilice como parámetro de la UDF para la asignación (por ejemplo, SELECT @y = 2@x = UDF(@y))7.

1 SELECT con acumulación/agregación de variables no se admite para inlining (como SELECT @val += col1 FROM table1).

2 Los UDFs recursivos se inlinearán sólo hasta una cierta profundidad.

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

4 Restricción añadida en SQL Server 2019 (15.x) CU2

5 Restricción añadida en SQL Server 2019 (15.x) CU4

6 Restricción añadida en SQL Server 2019 (15.x) CU5

7 Restricción añadida en SQL Server 2019 (15.x) CU6

Nota

Para obtener información sobre las últimas correcciones de T-SQL Scalar UDF Inlining y los cambios en los escenarios de elegibilidad de inlining, consulte el artículo de la Base de conocimientos: FIX: Problemas de inlining de UDF escalares en SQL Server 2019.

Comprobación de si un UDF puede ser inlinado o no

Para cada UDF escalar T-SQL, la vista del catálogo sys.sql_modules incluye una propiedad llamada is_inlineable, que indica si un UDF es inlineable o no.

Nota

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

Un valor de 1 indica que es inlineable, y 0 indica lo contrario. Esta propiedad tendrá un valor de 1 para todos los TVFs inline también. Para todos los demás módulos, el valor será 0.

Si un UDF escalar es inlineable, no implica que siempre se inlinee. SQL Server decidirá (en función de cada consulta y cada UDF) si se inlinea un UDF o no. Algunos ejemplos de cuándo un UDF no se puede alinear son:

• Si la definición del UDF ocupa miles de líneas de código, SQL Server podría decidir no alinearlo.

• Una invocación de UDF en una cláusula GROUP BY no se alineará. Esta decisión se toma cuando se compila la consulta que hace referencia a un UDF escalar.

Si el UDF está firmado con un certificado. Debido a que las firmas podrían añadirse y eliminarse después de que se haya creado un UDF, la decisión de inlinear o no se realiza cuando se compila la consulta que hace referencia a un UDF escalar. Por ejemplo, las funciones del sistema suelen estar firmadas con un certificado. Se puede utilizar sys.crypt_properties para encontrar qué objetos están firmados.

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

Comprobar si se ha realizado inlining o no

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

• El plan xml no tendrá un nodo <UserDefinedFunction> xml para un UDF que se haya inlining con éxito.
• Se emiten ciertos XEvents.

Habilitar Scalar UDF Inlining

Puede hacer que las cargas de trabajo sean automáticamente elegibles para Scalar UDF Inlining habilitando el nivel de compatibilidad 150 para la base de datos. Puede establecer esto utilizando Transact-SQL. Por ejemplo:

ALTER DATABASE SET COMPATIBILITY_LEVEL = 150;

Aparte de esto, no es necesario realizar otros cambios en las UDFs o en las consultas para aprovechar esta característica.

Desactivación de Scalar UDF Inlining sin cambiar el nivel de compatibilidad

Scalar UDF inlining se puede desactivar en el ámbito de la base de datos, de la sentencia o de la UDF mientras se mantiene el nivel de compatibilidad de la base de datos 150 y superior. Para deshabilitar el inlining UDF escalar en el ámbito de la base de datos, ejecute la siguiente sentencia dentro del contexto de la base de datos aplicable:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Para volver a habilitar Scalar UDF Inlining en el ámbito de la base de datos, ejecute la siguiente sentencia dentro del contexto de la base de datos aplicable:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Cuando esté activada, esta configuración aparecerá como habilitada en sys.database_scoped_configurations.También puede desactivar Scalar UDF Inlining para una consulta específica designando DISABLE_TSQL_SCALAR_UDF_INLINING como USE HINT pista de consulta. Por ejemplo:

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

Consejo

Una USE HINT sugerencia de consulta tiene prioridad sobre la configuración de ámbito de la base de datos o el ajuste del nivel de compatibilidad.

También se puede desactivar el inlining de UDFs escalares para un UDF específico utilizando la cláusula INLINE en la sentencia 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 = OFFASBEGIN RETURN @price * (1 - @discount);END;

Una vez ejecutada la sentencia anterior, este UDF nunca se inlizará en ninguna consulta que lo invoque. Para volver a activar el inlining para este UDF, ejecute la siguiente sentencia:

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

Nota

La cláusula INLINE no es obligatoria. Si la cláusula INLINE no se especifica, se establece automáticamente en ONOFF basándose en si el UDF puede ser inline. Si se especifica INLINE = ON pero el UDF no es elegible para ser inlining, se lanzará un error.

Notas importantes

Como se describe en este artículo, el inlining de UDFs escalares transforma una consulta con UDFs escalares en una consulta con una subconsulta escalar equivalente. Debido a esta transformación, los usuarios pueden notar algunas diferencias en el comportamiento en los siguientes escenarios:

1. El inlining resultará en un hash de consulta diferente para el mismo texto de consulta.
2. Ciertas advertencias en las sentencias dentro de la UDF (como dividir por cero, etc.) que podrían haber estado ocultas antes, podrían aparecer debido al inlining.
3. Las sugerencias de join a nivel de consulta podrían dejar de ser válidas, ya que el inlining podría introducir nuevos joins. En su lugar, habrá que utilizar sugerencias de unión locales.
4. Las vistas que hacen referencia a los UDF escalares en línea no pueden indexarse. Si necesita crear un índice en dichas vistas, desactive el inlining para las UDFs referenciadas.
5. Puede haber algunas diferencias en el comportamiento del enmascaramiento de Datos Dinámicos con el inlining de las UDFs.En ciertas situaciones (dependiendo de la lógica en la UDF), el inlining puede ser más conservador con respecto al enmascaramiento de las columnas de salida. En situaciones en las que las columnas a las que se hace referencia en una UDF no son columnas de salida, no se enmascararán.
6. Si una UDF hace referencia a funciones integradas como SCOPE_IDENTITY()@@ROWCOUNT, o @@ERROR, el valor devuelto por la función integrada cambiará con el inlining. Este cambio de comportamiento se debe a que el inlining cambia el ámbito de las sentencias dentro de la UDF. A partir de SQL Server 2019 (15.x) CU2, el inlining se bloquea si el UDF hace referencia a ciertas funciones intrínsecas (por ejemplo @@ROWCOUNT).

Vea también