Es posible que hayas oído que las funciones definidas por el usuario de SQL Server son el diablo. Eso es porque alguien se quemó. Pusieron una función escalar en una cláusula WHERE de una tabla grande y le dieron un nombre de columna. Y pasaron cosas malas.
En este caso, «malo» significa lento. Realmente lento.
Sarga-qué-ahora
Para que las consultas se realicen bien, SQL Server tiene que ser capaz de encontrar los datos rápidamente, y eso normalmente significa utilizar un índice. Un índice es una lista de datos clave de una tabla, organizados para que la búsqueda sea más rápida. Al igual que en un libro, en lugar de escudriñar cada página en busca de la mención de una palabra, puede utilizar el índice, donde el autor -o su programa informático- ha hecho ese trabajo por usted como una práctica referencia.
Un índice es más rápido cuando puede utilizarlo directamente. Si buscas páginas que mencionan «SQL», encuentras «SQL» en el índice y te indica los números de página que debes consultar. ¿Pero qué pasa si buscas todas las palabras que terminan en «SQL», como «T-SQL»? Ahora no puedes ir directamente a la parte del índice que quieres. Tienes que buscar en todo el índice. Ya no tienes un único argumento de búsqueda («SQL»); tienes una expresión de búsqueda («termina en SQL»). El término industrial que utilizamos es que tu búsqueda ya no es «sargable» («Search ARGument ABLE»).
La moraleja de la historia es que si quieres que un índice se utilice de forma más efectiva, asegúrate de que estás buscando exactamente en lo que se está indexando. Si estás buscando por una fecha, y quieres encontrar todo en un año determinado, no uses WHERE Year() = 2018". En su lugar, usa WHERE >= '20180101' AND . As a human, you can understand these as the same thing, but often the optimizer just sees "arbitrary function on indexed column" and figures it can't use the index the normal way but instead has to slog through the whole thing and run the function to see what it does.
Y si el optimizador tiene este problema con la función incorporada «Año», ¿qué crees que va a hacer con tu escalar «Complejo_2000_Línea_Monstruosa»? Lo va a tratar como la caja negra que es y va a recorrer cada entrada del índice para probarlo. Y eso supone que hay un índice. Una gran función definida por el usuario a menudo necesita varias columnas diferentes de datos, algunos de los cuales no están en un índice. Así que ahora estás escaneando toda la tabla. Tómate una taza de café. Estarás ahí un rato.
Puedes ver a través de ellas.
Mientras que las funciones escalares son opacas para el optimizador, las funciones inline son transparentes. En lugar de ver una función en línea como una caja negra, el optimizador de consultas la ve más bien como una vista parametrizada, es decir, una vista que recibe parámetros. Las vistas y las funciones en línea son esencialmente copiadas y pegadas en la consulta que las llama, como si no fueran una construcción separada.
| Tipo de función definida por el usuario | Resultados | MúltiplesStatement | Opaque |
|---|---|---|---|
| Scalar | Single Value | Yes | Yes |
| Multi-Statement Table-Valued | Table | Yes | Yes |
| Inline Table-Valued | Table | No |
Ahora bien, que los límites de tu función se desvanezcan en la tierra del optimizador no significa que vayas a encontrar tu búsqueda. Usted no necesita una función para destruir saragability. Simplemente reordenando su lógica de criterios puede manejar eso por sí mismo. Por ejemplo, no obtendrá el beneficio de su índice en Column1 si utiliza WHERE 1 = CASE WHEN Column1 = @value THEN 1 ELSE 0 END en lugar de WHERE Column1 = @value. De nuevo, los humanos podemos ver que es lo mismo, pero SQL Server no.
Pero si se utiliza una función inline que actúa directamente sobre un campo indexado, sin un cálculo ofuscado, el optimizador seguirá utilizando una búsqueda indexada. La forma de hacerlo parece más un arte que una ciencia.
Pudding
Considera una tabla Orders con una columna OrderID, una columna OrderStatusID y una columna OrderDate. Las columnas OrderID y OrderDate son irrelevantes para nuestros ejemplos, excepto para proporcionar algunos datos adicionales relacionados con el orden. La columna clave aquí es OrderStatusID. Digamos que tenemos muchos estados en los que puede estar un pedido (Nuevo, Procesando, Enviando, Pagado, etc.).
Ahora digamos que tenemos una regla de negocio que se aplica sólo en algunos estados. Tal vez un pedido es manejado por un determinado departamento sólo si es Nuevo o Procesando pero no cuando está en cualquier otro estado. Si estás poniendo esta lógica de negocio en la base de datos -como deberías- tu código de base de datos tendrá que entender qué estados son especiales.
La forma más eficiente en cuanto a rendimiento para consultar la tabla Pedidos para los pedidos de estado especial es listarlos directamente en cada consulta relevante:
SELECTo.*FROM#Orders oWHEREo.OrderStatusID IN (3,8);
Con un índice en el campo OrderStatusID, el optimizador probablemente utilizaría una acción de búsqueda de índice y encontraría sus filas rápidamente. Sin embargo, probablemente necesitará que la base de datos entienda ese concepto en muchos lugares diferentes. En lugar de repetir esta lista de «estados especiales», usted decide que quiere una función para calcular si un estado es especial. De esta manera tu lógica de negocio está centralizada:
CREATE FUNCTION IsOrderStatusSpecialScalar(@OrderStatusID int)RETURNS bit ASBEGINRETURN(CASEWHEN @OrderStatusID IN (3,8) THEN1ELSE0END);
Ahora, cuando quieras encontrar estos estados especiales, sólo tienes que usar la función.
SELECTo.*FROM#Orders oWHEREdbo.IsOrderStatusSpecialScalar(o.OrderStatusID) = 1;
Por desgracia, te encuentras con un escaneo lento del índice porque el optimizador no sabe lo que pasa dentro de la función. Simplemente la llama una y otra vez para cada fila.
Podrías pensar que puedes rescatar tu rendimiento utilizando una función inline.
CREATE FUNCTION IsOrderStatusSpecial(@OrderStatusID int)RETURNS TABLE ASRETURNSELECTOrderStatusIsSpecial =CASEWHEN @OrderStatusID IN (3,8) THEN1ELSE0END;
Tienes que cambiar un poco el uso pero es igualmente conciso.
SELECTo.*FROM#Orders oCROSS APPLYIsOrderStatusSpecial(o.OrderStatusID) sWHEREs.OrderStatusIsSpecial = 1;
Desgraciadamente, te encuentras con el mismo problema de rendimiento del escaneo de índices. Sin embargo, no puedes culpar a la función inline. Después de todo, obtienes el mismo pobre rendimiento usando la lógica de la función CASEWHEN directamente:
SELECTo.*FROM#Orders oWHERE1 = CASEWHEN o.OrderStatusID IN (3,8) THEN1ELSE0END;
Así que tienes que encontrar una forma de usar una función inline donde el optimizador vea un uso más directo del campo indexado. Así que creas una función inline que no utiliza ningún cálculo para producir su bit.
CREATE FUNCTION IsOrderStatusSpecialLimiter(@OrderStatusID int)RETURNS TABLE ASRETURNSELECTOrderIsSpecial = CONVERT(bit,1)WHERE@OrderStatusID IN (3,8);
Y ni siquiera utilizas el bit que hace cuando llamas a la función. Dejas que los resultados de la función impacten en las filas devueltas directamente, vía filtrado.
SELECTo.*FROM#Orders oCROSS APPLYIsOrderStatusSpecialLimiter(o.OrderStatusID) s;
Y ¡voilá! Tienes tu pastel de encapsulación lógica y te comes el rendimiento de la búsqueda de índices también.
Pero esa no es la única forma de hacer que esto funcione. Y aquí es donde se empieza a sentir un poco más como un arte. En lugar de hacer que la función inline realice un cálculo o incluso limitar filas con una cláusula WHERE con una lista IN, podrías construir una tabla virtual de estados especiales en tu función inline.
CREATE FUNCTION GetSpecialOrderStatusIDs()RETURNS TABLE ASRETURNSELECTs.OrderStatusIDFROM(SELECT3UNION ALLSELECT8) AS s(OrderStatusID);
Ni siquiera estás pasando nada a esta función. De hecho, podrías incluso utilizar una vista simple. Y la usarías de una manera más tradicional, sin ese novedoso operador APPLY.
SELECTo.*FROM#Orders oWHEREEXISTS(SELECT*FROMGetSpecialOrderStatusIDs() sWHEREs.OrderStatusID = o.OrderStatusID);
Este es otro ejemplo de uso de una función inline sin sacrificar el rendimiento.
Ocultar y no buscar
Así que no, las funciones definidas por el usuario no son el diablo. Las funciones escalares definidas por el usuario pueden causar grandes problemas si se usan mal, pero generalmente las funciones en línea definidas por el usuario no causan problemas.
La verdadera regla general no es evitar las funciones, sino evitar adornar tus campos de índice con lógica o funciones. Porque cuando ocultas tus intenciones al optimizador con una sintaxis compleja, te arriesgas a no conseguir la búsqueda de índices de mejor rendimiento.
0 comentarios