Las columnas calculadas pueden causar problemas de rendimiento difíciles de diagnosticar. Este artículo analiza una serie de problemas y algunas formas de resolverlos.
Las columnas calculadas son una forma conveniente de incrustar cálculos en definiciones de tablas. Pero pueden causar problemas de rendimiento, especialmente a medida que las expresiones se vuelven más complejas, las aplicaciones se vuelven más exigentes y los volúmenes de datos continúan creciendo.
Una columna calculada es una columna virtual cuyo valor se calcula en función de los valores de otras columnas de la tabla. De forma predeterminada, el valor calculado no se almacena físicamente, sino que SQL Server lo calcula en cada solicitud de columna. Esto aumenta la carga en el procesador, pero reduce la cantidad de datos que deben retenerse cuando cambia la tabla.
A menudo, las columnas calculadas no persistentes consumen mucha CPU, ralentizan las consultas y congelan las aplicaciones. Afortunadamente, SQL Server proporciona varias formas de mejorar el rendimiento de las columnas calculadas. Puede crear columnas calculadas persistentes, indexarlas o hacer ambas cosas.
Para fines de demostración, creé cuatro tablas similares y las llené con datos idénticos de la base de datos de demostración de WideWorldImporters. Cada tabla tiene la misma columna calculada, pero dos tablas la tienen persistente y dos tienen un índice. El resultado son las siguientes opciones:
- Una tabla
Orders1es una columna calculada no persistente. - La tabla
Orders2es una columna calculada persistente. - La tabla
Orders3es una columna calculada no persistente con un índice. - La tabla
Orders4es una columna calculada persistente con un índice.
La expresión calculada es bastante simple y el conjunto de datos es muy pequeño. Sin embargo, debería ser suficiente demostrar los principios de las columnas calculadas persistentes e indexadas y cómo esto ayuda a resolver problemas de rendimiento.
Columna calculada sin guardar
Quizás, en su situación, podría querer columnas calculadas no persistentes para evitar almacenar datos, crear índices o para usar con una columna no determinista. Por ejemplo, SQL Server tratará una UDF escalar como no determinista si WITH SCHEMABINDING falta en la definición de función. Si intenta crear una columna calculada persistente con esta función, obtendrá un error que indica que no se puede crear la columna persistente.
Sin embargo, cabe señalar que las funciones personalizadas pueden crear sus propios problemas de rendimiento. Si la tabla contiene una columna calculada con una función, el motor de consultas no usará la simultaneidad (a menos que esté usando SQL Server 2019). Incluso en una situación en la que la columna calculada no se especifica en la consulta. Para un gran conjunto de datos, esto puede tener un gran impacto en el rendimiento. Las funciones también pueden ralentizar la ejecución de ACTUALIZACIONES y afectar la forma en que el optimizador calcula el costo de una consulta en una columna calculada. Esto no significa que nunca deba usar funciones en una columna calculada, pero definitivamente debe tratarse con precaución.
Ya sea que use funciones o no, crear una columna calculada no persistente es bastante sencillo. Siguiente instrucción
CREATE TABLEdefine una tabla Orders1que incluye una columna calculada Cost.
USE WideWorldImporters;
GO
DROP TABLE IF EXISTS Orders1;
GO
CREATE TABLE Orders1(
LineID int IDENTITY PRIMARY KEY,
ItemID int NOT NULL,
Quantity int NOT NULL,
Price decimal(18, 2) NOT NULL,
Profit decimal(18, 2) NOT NULL,
Cost AS (Quantity * Price - Profit));
INSERT INTO Orders1 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;Para definir una columna calculada, especifique su nombre seguido de la palabra clave AS y la expresión. En nuestro ejemplo, estamos multiplicando
Quantitypor Pricey restando Profit. Después de crear la tabla, la llenamos con INSERT usando datos de Sales.InvoiceLinesla tabla de la base de datos WideWorldImporters. A continuación, ejecutamos SELECT.
SELECT ItemID, Cost FROM Orders1 WHERE Cost >= 1000;Esta consulta debería devolver 22,973 filas, o todas las filas que tiene en la base de datos WideWorldImporters. El plan de ejecución para esta consulta se muestra en la Figura 1.
Figura 1. El plan de ejecución para la consulta contra la tabla Orders1
Lo primero que se debe tener en cuenta es el análisis de índice agrupado, que no es una forma eficiente de obtener los datos. Pero este no es el único problema. Echemos un vistazo a la cantidad de lecturas lógicas (lecturas lógicas reales) en las propiedades del análisis de índice agrupado (consulte la figura 2).
Figura 2. Lecturas lógicas para consultar la tabla Orders1
El número de lecturas lógicas (en este caso 1108) es el número de páginas que se han leído del caché de datos. El objetivo es intentar reducir este número tanto como sea posible. Por tanto, es útil recordarlo y compararlo con otras opciones.
El número de lecturas lógicas también se puede obtener ejecutando la instrucción
SET STATISTICS IO ONantes de ejecutar SELECT. Para ver la CPU y el tiempo total, SET STATISTICS TIME ONo ver las propiedades de la instrucción SELECT en el plan de ejecución de la consulta.
Otro punto que vale la pena señalar es que hay dos instrucciones Compute Scalar en el plan de ejecución. El primero (el de la derecha) es el cálculo del valor de columna calculado para cada fila devuelta. Dado que los valores de las columnas se calculan sobre la marcha, no puede evitar este paso con columnas calculadas no persistentes a menos que cree un índice en esa columna.
En algunos casos, una columna calculada no persistente proporciona el rendimiento necesario sin almacenarla ni utilizar un índice. Esto no solo ahorra espacio de almacenamiento, sino que también evita la sobrecarga de actualizar los valores calculados en una tabla o índice. Sin embargo, la mayoría de las veces, una columna calculada no persistente conduce a problemas de rendimiento, por lo que debería empezar a buscar una alternativa.
Columna calculada persistente
Una técnica que se utiliza a menudo para resolver problemas de rendimiento es definir una columna calculada como persistente. Con este enfoque, la expresión se calcula de antemano y el resultado se almacena junto con el resto de los datos de la tabla.
Para que una columna sea persistente, debe ser determinista, es decir, la expresión siempre debe devolver el mismo resultado para la misma entrada. Por ejemplo, no puede usar la función GETDATE en una expresión de columna, porque el valor de retorno siempre cambia.
Para crear una columna calculada persistente, debe agregar una palabra clave a la definición de columna
PERSISTED, como se muestra en el siguiente ejemplo.
DROP TABLE IF EXISTS Orders2;
GO
CREATE TABLE Orders2(
LineID int IDENTITY PRIMARY KEY,
ItemID int NOT NULL,
Quantity int NOT NULL,
Price decimal(18, 2) NOT NULL,
Profit decimal(18, 2) NOT NULL,
Cost AS (Quantity * Price - Profit) PERSISTED);
INSERT INTO Orders2 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;La tabla es
Orders2casi idéntica a la tabla Orders1, excepto que la columna Costcontiene la palabra clave PERSISTED. SQL Server completa automáticamente esta columna cuando se agregan o modifican filas. Por supuesto, esto significa que la mesa Orders2ocupará más espacio que la mesa Orders1. Esto se puede verificar mediante un procedimiento almacenado sp_spaceused.
sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GOLa figura 3 muestra el resultado de este procedimiento almacenado. El tamaño de los datos en la tabla
Orders1es 8.824 KB y en la tabla Orders2, 12.936 KB. 4 112 KB más para almacenar los valores calculados.
Figura 3. Comparación del tamaño de las tablas Orders1 y Orders2
Aunque estos ejemplos se basan en un conjunto de datos bastante pequeño, puede ver cómo la cantidad de datos almacenados puede crecer rápidamente. Sin embargo, esto puede ser una compensación si mejora el rendimiento.
Para ver la diferencia en el rendimiento, haga lo siguiente SELECT.
SELECT ItemID, Cost FROM Orders2 WHERE Cost >= 1000;Este es el mismo SELECT que usé para la tabla Orders1 (excepto por el cambio de nombre). La Figura 4 muestra el plan de ejecución.
Figura 4. Plan de ejecución para una consulta a la tabla Pedidos 2.
Esto también comienza con el Análisis de índice agrupado. Pero esta vez, solo hay una instrucción Compute Scalar porque las columnas calculadas ya no necesitan calcularse en tiempo de ejecución. En general, cuantos menos pasos mejor. Aunque no siempre es así.
La segunda consulta generó 1593 lecturas lógicas, que son 485 más de 1108 lecturas para la primera tabla. A pesar de esto, corre más rápido que el primero. Aunque solo unos 100 ms, y a veces mucho menos. El tiempo del procesador también ha disminuido, pero tampoco mucho. Lo más probable es que la diferencia sea mucho mayor en volúmenes más grandes y cálculos más complejos.
Índice en columna calculada no persistente
Otra técnica que se usa comúnmente para mejorar el rendimiento de una columna calculada es la indexación. Para poder crear un índice, la columna debe ser determinista y precisa, lo que significa que la expresión no puede usar los tipos flotante y real (si la columna no es persistente). También existen restricciones en otros tipos de datos, así como en los parámetros SET. Para obtener una lista completa de restricciones, consulte la documentación de SQL Server, Índices en columnas calculadas .
Puede comprobar si una columna calculada no persistente es adecuada para indexar a través de sus propiedades. Usemos la función para ver las propiedades
COLUMNPROPERTY. Las propiedades IsDeterministic, IsIndexable e IsPrecise son importantes para nosotros.
DECLARE @id int = OBJECT_ID('dbo.Orders1')
SELECT
COLUMNPROPERTY(@id,'Cost','IsDeterministic') AS 'Deterministic',
COLUMNPROPERTY(@id,'Cost','IsIndexable') AS 'Indexable',
COLUMNPROPERTY(@id,'Cost','IsPrecise') AS 'Precise';La instrucción SELECT debe devolver 1 para cada propiedad para que la columna calculada pueda indexarse (consulte la Figura 5).
Figura 5. Verificación de que
se puede crear el índice Después de la verificación, puede crear un índice no agrupado. En lugar de modificar la tabla,
Orders1creé una tercera tabla ( Orders3) e incluí el índice en la definición de la tabla.
DROP TABLE IF EXISTS Orders3;
GO
CREATE TABLE Orders3(
LineID int IDENTITY PRIMARY KEY,
ItemID int NOT NULL,
Quantity int NOT NULL,
Price decimal(18, 2) NOT NULL,
Profit decimal(18, 2) NOT NULL,
Cost AS (Quantity * Price - Profit),
INDEX ix_cost3 NONCLUSTERED (Cost, ItemID));
INSERT INTO Orders3 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;Creé un índice de cobertura no agrupado que incluye ambas columnas desde
ItemIDy Costhacia una consulta SELECT. Después de crear y completar la tabla y el índice, puede ejecutar la siguiente instrucción SELECT similar a los ejemplos anteriores.
SELECT ItemID, Cost FROM Orders3 WHERE Cost >= 1000;La Figura 6 muestra el plan de ejecución para esta consulta, que ahora usa el índice no agrupado ix_cost3 (Index Seek) en lugar de realizar un escaneo de índice agrupado.
Figura 6. Plan de ejecución para una consulta en la tabla Orders3
Si observa las propiedades de la instrucción Index Seek, encontrará que la consulta ahora solo realiza 92 lecturas lógicas, y en las propiedades de la instrucción SELECT, verá que la CPU y el tiempo total han disminuido. La diferencia no es significativa, pero nuevamente, este es un pequeño conjunto de datos.
También debe tenerse en cuenta que solo hay una instrucción Compute Scalar en el plan de ejecución, no dos como en la primera consulta. Dado que la columna calculada está indexada, los valores ya se han calculado. Esto elimina la necesidad de calcular valores en tiempo de ejecución, incluso si la columna no se ha definido para ser persistente.
Índice en la columna almacenada
También puede crear un índice en la columna calculada que está guardando. Si bien esto dará como resultado que se almacenen datos adicionales y datos de índice, puede ser útil en algunos casos. Por ejemplo, puede crear un índice en una columna calculada persistente, incluso si usa los tipos de datos flotantes o reales. Este enfoque también puede ser útil cuando se trabaja con funciones CLR y cuando no se puede verificar si las funciones son deterministas.
La siguiente declaración
CREATE TABLEcrea una tabla Orders4. La definición de la tabla incluye una columna persistente Costy un índice de cobertura no agrupado ix_cost4.
DROP TABLE IF EXISTS Orders4;
GO
CREATE TABLE Orders4(
LineID int IDENTITY PRIMARY KEY,
ItemID int NOT NULL,
Quantity int NOT NULL,
Price decimal(18, 2) NOT NULL,
Profit decimal(18, 2) NOT NULL,
Cost AS (Quantity * Price - Profit) PERSISTED,
INDEX ix_cost4 NONCLUSTERED (Cost, ItemID));
INSERT INTO Orders4 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;Después de crear y completar la tabla y el índice, ejecute SELECT.
SELECT ItemID, Cost FROM Orders4 WHERE Cost >= 1000;La figura 7 muestra el plan de ejecución. Como en el ejemplo anterior, la consulta comienza con una búsqueda de índice no agrupada (Búsqueda de índice).
Figura 7. Plan de ejecución de una consulta en la tabla Orders4
Esta consulta también realiza solo 92 lecturas lógicas que la anterior, lo que da como resultado aproximadamente el mismo rendimiento. La principal diferencia entre estas dos columnas calculadas y entre las columnas indexadas y no indexadas es la cantidad de espacio utilizado. Comprobemos esto ejecutando el procedimiento almacenado
sp_spaceused.
sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GO
sp_spaceused 'Orders3';
GO
sp_spaceused 'Orders4';
GOLos resultados se muestran en la Figura 8. Como se esperaba, las columnas calculadas almacenadas tienen más datos y las columnas indexadas tienen más índices.
Figura 8. Comparación de la utilización del espacio para las cuatro tablas
Lo más probable es que no necesite indexar las columnas calculadas almacenadas sin una buena razón. Al igual que con otras preguntas relacionadas con la base de datos, su elección debe basarse en su situación específica: sus consultas y la naturaleza de sus datos.
Trabajar con columnas calculadas en SQL Server
La columna calculada no es una columna de tabla normal y debe manejarse con cuidado para evitar degradar el rendimiento. La mayoría de los problemas de rendimiento se pueden resolver almacenando o indexando la columna, pero ambos enfoques deben considerar el espacio extra en disco y cómo cambian los datos. Cuando cambian los datos, los valores de la columna calculada deben actualizarse en la tabla o índice, o en ambos, si indexó la columna calculada persistente. Solo puede decidir cuál de las opciones es mejor para su caso específico. Y lo más probable es que tengas que utilizar todas las opciones.
Lee mas