Artículo de SQL Magazine 7 – SQL Server: Turbina tus consultas con vistas indexadas

admin 0 Comments Articles

Artículo de SQL Magazine 7 – SQL Server: Turbina tus consultas con vistas indexadas

Toma tu pregunta Marcar como completada Anotar

El propósito de este artículo es introducir el concepto de vistas indexadas de SQL Server y mostrar cómo implementar y utilizar este tipo de vistas para optimizar las consultas.

Concepto de vista

Las vistas también se conocen como «tablas virtuales», ya que presentan una alternativa al uso de tablas para acceder a los datos. Una vista no es más que una sentencia SELECT encapsulada en un objeto. La sintaxis para crear una vista es la que se muestra en el Listado 1.

CREATE VIEW nome_da_visão ...) ] AS subconsulta;

Vea un ejemplo de creación y uso de la vista en el Listado 1.

Use NorthWind go create view vi_vendas_mes As Select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity) from Orders o inner join od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate), datepart(mm,o.OrderDate) go select * from vi_vendas_mês go ano mes qtde_total contador ----------- ----------- ----------- -------------------- 1996 7 1462 59 1996 8 1322 69 1996 9 1124 57 1996 10 1738 73 1996 11 1735 66

Entre las ventajas de usar vistas, podemos mencionar :

• Simplificación del código: se pueden escribir SELECTs complejos una vez, encapsularlos en la vista y lanzarlos desde ella, como si se tratara de cualquier tabla;
• Cuestiones de seguridad: supongamos que tenemos información confidencial en algunas tablas y, por tanto, queremos que sólo algunos usuarios tengan acceso a ellas. Sin embargo, algunas columnas de estas tablas deben ser accesibles para todos los usuarios. Una forma eficaz de resolver este problema es crear una vista y ocultar las columnas sensibles. De esta forma, se pueden suprimir los derechos de acceso a la tabla original y liberar el acceso a la vista;
• Posibilidad de optimización de consultas mediante la implementación de vistas indexadas.

Vistas indexadas en la práctica

Las vistas encapsulan sentencias SELECT, lo que significa que siempre que se lanzan, se ejecutan las sentencias SELECT asociadas a ellas. Las vistas no crean repositorios para los datos que devuelven (como hace la tabla). Pues bien, sería genial poder «materializar» en una tabla el resultado del comando SELECT encontrado en la vista, creando índices para facilitar el acceso a la misma. Pues bien, las vistas indexadas hacen precisamente eso. Ejecutar un SELECT en una vista indexada tiene el mismo efecto que ejecutar un select en una tabla convencional.

El propósito principal de las vistas indexadas es aumentar el rendimiento, y la ventaja de SQL Server es permitir que los planes de ejecución consideren la vista indexada como medio de acceso a los datos, incluso si el nombre de la vista no fue explícito en la consulta. Esto es posible en la edición Enterprise de SQL Server 2000, donde el optimizador de comandos puede seleccionar datos directamente en la vista indexada (en lugar de seleccionar los datos brutos existentes en la tabla), como veremos a continuación.

Creando una vista indexada paso a paso

1. Configurando el entorno, el primer paso es establecer el estado de algunos parámetros en la sesión en la que se quiere crear y utilizar la vista, ya que al estar la vista indexada «materializada» en una tabla, nada puede interferir en su resultado. Imagínese, por ejemplo, el siguiente escenario:
2. Un determinado ajuste, que afecta al resultado de un SELECT (por ejemplo, concat_null_yelds_null), se establece antes de crear la vista indexada;
3. Se crea la vista indexada; tenga en cuenta que el resultado de la vista se «materializará» en el disco de acuerdo con el ajuste establecido en el paso anterior;
4. Después se desactiva el ajuste y el usuario ejecuta la vista indexada. Dado que la vista ha sido materializada, obtendremos un resultado inconsistente con la configuración actual.

Por ejemplo, el Listado 2 muestra la diferencia en el resultado de un comando cuando se cambia la propiedad concat_null_yields_null.

set concat_null_yields_null ON print null + 'abc' -------------------------------------- Set concat_null_yields_null OFF print null + 'abc' -------------------------------------- abc

Imagina lo que ocurriría si la vista indexada se creara con la propiedad concat_null_yields_null activada, pero la sesión actual estuviera con esa propiedad desactivada: ¡el mismo SELECT daría resultados diferentes!

Este problema se solucionó de forma sencilla – para crear y utilizar vistas indexadas, es obligatorio configurar el entorno según una lista de valores por defecto. De esta manera, es imposible obtener resultados diferentes, porque la vista simplemente no funcionará si cualquiera de los ajustes se establece en un valor no estándar.

La tabla 1 muestra estos ajustes y sus respectivos valores por defecto.

(*) El id se utiliza en el comando sp_configure. Para comprobar qué hace cada ajuste, lea la sección «Ajustes necesarios para las vistas indexadas». Hay dos formas de cambiar el valor de una configuración:

• Directamente en la sesión: ejecutar el comando set ON | OFF
• Cambiar el valor predeterminado del servidor existente: ejecutar sp_Configure ‘opciones de usuario’, . El número de identificación de cada configuración puede verse en la Tabla 1.
  Nota: AritHabort tiene id 64 y Quoted_Identifier tiene id 256. Para enlazar, por ejemplo, Quoted_Identifier + AritHabort, ejecutaríamos sp_cofigure, pasando como parámetro el resultado de 64+256 (=320): sp_configure ‘user options’, 320. Para obtener un listado completo de los ids asignados a cada configuración, vaya a http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_9b1q.asp.

Para confirmar el estado de cada uno de los parámetros de la Tabla 1, utilice la función SessionProperty(‘nombre del parámetro’) o el comando DBCC UserOptions.

De este modo, configure todos los parámetros de acuerdo con la columna ‘estado requerido para las vistas indexadas’ de la Tabla 1 – si no se hace esto, SQL Server no le permitirá crear/ejecutar la vista indexada.

Creación de la vista indexada

Crearemos una vista para totalizar la cantidad diaria vendida en la tabla Detalles del pedido, situada en la base de datos NorthWind. Véase el listado 3.

use NorthWind go create view vi_vendas_mes with SchemaBinding as select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity), contador = count_big(*) from dbo.Orders o inner join dbo. od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate),datepart(mm,o.OrderDate) go

Necesitamos observar algunas particularidades al crear vistas indexadas:

• La vista necesita ser determinista. Una vista será determinista si utilizamos sólo funciones deterministas en su código. El mismo comando SELECT ejecutado repetidamente en una vista indexada (considerando una base estática) no puede dar resultados diferentes. Las funciones deterministas garantizan que el resultado de una función permanecerá inalterado sin importar cuántas veces se ejecute. La función FechaParte, por ejemplo, es determinista, ya que siempre devuelve el mismo resultado para una fecha concreta. La función getdate() devolverá un valor diferente cada vez que se ejecute. Para la lista completa de funciones deterministas de SQL Server 2000, vaya a http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_95v7.asp
• Compruebe que no hay restricciones de sintaxis. Las cláusulas, funciones y tipos de consulta que se enumeran a continuación no pueden integrar el esquema de una vista indexada:
  • MIN, MAX,TOP
  • Varianza,STDEV,AVG
  • Cuenta(*)
  • Suma en columnas que permiten valores nulos
  • DISTINTO
  • Función RWSET
  • Tablas derivadas, self joins, subconsultas, outer joins
  • DISTINCT
  • UNION
  • Float, text, ntext e image
  • COMPUTE y COMPUTE BY
  • HAVING, CUBE y ROLLUP

Es necesario crear las vistas indexadas con SchemaBinding. Para mantener la coherencia del contenido de la vista, no se puede modificar la estructura de las tablas que originaron la vista. Para evitar este tipo de problemas, es obligatorio utilizar SchemaBinding al crear vistas indexadas, ya que esta opción no permite cambiar la estructura de la tabla sin eliminar primero la vista.

Para utilizar la cláusula GROUP BY, es obligatorio incluir la función COUNT_BIG(*). La función count_big(*) hace lo mismo que count(*), pero devuelve un valor de tipo bigint (8 bytes).

Informa siempre al propietario de los objetos referenciados en la vista indexada. Utilice select * from dbo.Orders en lugar de select * from Orders, ya que es posible tener tablas con el mismo nombre pero diferentes propietarios. Dado que la opción schemabinding es obligatoria, SQL Server necesita la especificación exacta del objeto para frenar la alteración del esquema.

Creación de un índice de clúster en la vista (materialización)

La vista creada en el punto 2 aún no se comporta como una vista indexada, ya que el resultado del comando select no se ha materializado en una tabla. Puede confirmar esta afirmación ejecutando el comando sp_spaceused en Query Analyzer, que devuelve el número de filas y el espacio utilizado por las tablas (Listado 4).

sp_SpaceUsed vi_vendas_mes -------------------------------------------------------------------------------------- Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91 Views do not have space allocated.

Listado 4. Uso del comando sp_spaceused en una vista

Observe en el Listado 5 que el procesamiento de la vista es puramente lógico, tanto que el valor de Physical Reads es cero. Observe los valores registrados en Lecturas Lógicas y Lecturas Físicas (1672+0+4+0=1676) – utilizaremos estos valores en nuestras futuras comparaciones.

Set statistics io ON Select * from vi_vendas_mesgo---------------------------------------------------------ano mes qtde_total contador ----------- ----------- ------------- -------------------- 1996 7 1462 591996 8 1322 691996 9 1124 57.....(23 row(s) affected)Table 'Order Details'. Scan count 830, logical reads 1672, physical reads 0, read-ahead reads 0.Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

Listado 5. Total de E/S asociadas a la vista ANTES de crear el índice del cluster

Para comprobar si la vista puede ser indexada (materializada), es decir, si fue creada dentro de las normas y configuraciones requeridas para las vistas indexadas, el resultado del siguiente SELECT debe ser igual a 1.

select ObjectProperty(object_id('vi_vendas_mes'),'IsIndexable')

Confirmados los requisitos previos, ya podemos crear el índice. La sintaxis tiene el mismo formato que se utiliza al crear índices en tablas convencionales:

create unique clustered index pk_ano_mes on vi_vendas_mes (ano,mes)

Nótese que el índice de cluster es necesario porque genera páginas de datos. Puede crear índices no clúster en vistas indexadas sólo después de crear el índice clúster.

Ahora el SELECT encontrado en la vista se ha materializado. que puede probarse con el comando sp_spaceused en Query Analyzer (Listado 6).

sp_SpaceUsed vi_vendas_mêsgo-----------------------------------------------------Name Rows Reserved data index_size Unusedvi_vendas_mes 23 24 KB 8 KB 16 KB 0 KB

Listado 6 . Uso del comando sp_spaceused en una vista indexada

Uso de vistas indexadas

Una forma de acceder a una vista indexada (al igual que a una vista convencional) es hacer referencia a su nombre en el comando SELECT:

select * from vi_vendas_mes

Compare el volumen de páginas movidas en el Listado 5 (1672+4=1676) con el del Listado 7 (2+0=2). La diferencia es bastante significativa: la creación de la vista indexada redujo la E/S total requerida en 1674 páginas.

Set statistics io ON select * from vi_vendas_mes go --------------------------------------------------------- ano mes qtde_total contador ----------- ----------- ------------- -------------------- 1996 7 1462 59 1996 9 1124 57 ..... (23 row(s) affected) Table 'vi_vendas_mes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Listado 7. E/S total asociada a la vista después de crear el cluster de índices

Veamos otro ejemplo. La figura 1 muestra el plan de ejecución de una consulta. Confirmar que la vista fue seleccionada aunque no estaba presente en la fila SELECT.

Durante la construcción del plan de ejecución de la consulta, el optimizador encontró que ya había datos pre-resumidos para la consulta en vi_sales_mes y optó por seleccionar los datos directamente en la vista indexada.

Nótese que la consulta ejecutada en la Figura 1 es idéntica a la que se encuentra en la vista vi_sales_mes. Sin embargo, el acceso a la vista por parte del optimizador es independiente de la similitud entre la consulta ejecutada y la consulta de la vista. La selección de la vista indexada por parte del procesador de consultas sólo tiene en cuenta el coste-beneficio. De este modo, las consultas ejecutadas no tienen por qué ser idénticas a la vista (observe la Figura 3).

Sin embargo, es necesario seguir algunas reglas para que la vista indexada sea considerada por el optimizador de consultas:

• El join presente en la vista tiene que «estar contenido» en la consulta: si la consulta realiza un join entre las tablas A y B, y la vista realiza un join entre A y C, la vista no se activará en el plan de ejecución. Sin embargo, si la consulta realiza un join entre A,B y C, la vista puede activarse.
• Las condiciones establecidas en la consulta tienen que coincidir con las condiciones de la vista: en el SELECT de la Figura 2, la vista indexada vi_sales_meses no se tendrá en cuenta porque la cláusula where no estaba presente en el código de la vista, lo que provocó que se computaran filas con Cantidad <= 5 en el join.

Por otro lado, si la consulta tiene la condición where donde sum(Cantidad) > 5, la vista vi_sales_mes será considerada en el plan de ejecución, porque la condición de la consulta es un subconjunto del SELECT presente en la vista.

Las columnas con funciones de agregación en la consulta necesitan «estar contenidas» en la definición de la vista: si la vista devuelve la columna qtde=suma(Cantidad) y la consulta tiene una columna vlr_unit=suma(PrecioUnitario), la vista no será considerada.

La figura 3 muestra un comando SELECT que permite probar la inteligencia del optimizador de comandos – el cálculo de AVG(Cantidad) ha sido sustituido por la división entre SUM(Cantidad) / Count_Big(*), representada por el icono de Compute Scalar. También se considera el predicado where sum(Quantity) > 1500 (representado por el icono Filtro).

Consideraciones generales sobre el uso de vistas indexadas:

• Las vistas indexadas se pueden crear en cualquier versión de SQL Server 2000. Sin embargo, sólo en la versión Enterprise Edition serán seleccionadas automáticamente por el optimizador de consultas.
• En las versiones distintas de Enterprise, debe utilizar la sugerencia NoExpand para acceder a la vista indexada como una tabla convencional. Si no se emplea NoExpand, la vista indexada se considerará una vista «normal».
  Nota: La sugerencia Expand hace lo contrario de NoExpand: trata la vista indexada como una vista «normal», forzando a SQL Server a ejecutar la sentencia SELECT durante la fase de ejecución.
• El mantenimiento de una vista indexada es automático (como en los índices); no requiere ninguna sincronización adicional. Por su propia característica (suele almacenar datos pre-resumidos), su actualización suele ser un poco más lenta que la de los índices convencionales.
• El uso de vistas indexadas en bases OLTP requiere precaución, pues aunque presentan un gran rendimiento en las consultas, provocan sobrecarga en los procesos que modifican las tablas relacionadas en la vista. En situaciones en las que se requiere un alto rendimiento de escritura, con actualizaciones frecuentes, no se recomienda la creación de vistas indexadas.
• Al igual que hace con los índices, el optimizador analizará el código de la vista en las versiones Enterprise como parte del proceso de elección del mejor plan de ejecución para una consulta. Sin embargo, si hay muchas vistas indexadas susceptibles de ser ejecutadas para la misma consulta, puede haber un aumento sustancial de este tiempo de elección, ya que se analizarán todas las vistas. Por lo tanto, utilice el sentido común cuando implemente las vistas.

Configuraciones requeridas para las vistas indexadas

ANSI_NULLS

Define cómo se realizan las comparaciones con valores nulos (Listado 8).

set ANSI_NULLS ON declare @var char(10) set @var = null if @var = null print 'VERDADEIRO' else print 'FALSO' ------------------------------------- FALSO set ANSI_NULLS OFF declare @var char(10) set @var = null if @var = null print 'VERDADEIRO' else print 'FALSO' -------------------------------------- VERDADEIRO

Listado 8. Ejemplos del ansi_null

ANSI_PADDING

Determina cómo deben almacenarse las columnas char, varchar, binary y varbinary cuando su contenido es menor que el tamaño definido en la estructura de la tabla. SQL Server 2000 por defecto mantiene el ansi_padding activado (=ON); en esta condición se aplican las siguientes reglas:

• Cuando se actualicen columnas char, se añadirán espacios en blanco al final de la cadena si es menor que el tamaño definido en la estructura de la columna. La misma regla se aplica a las columnas binarias (en ese caso, el espacio se rellena con una secuencia de ceros)
• Las columnas varchar o varbinary no siguen la regla anterior: siempre mantienen su tamaño original.

ARITHABORT

Cuando está habilitado, termina la ejecución de la consulta al encontrar una división por cero o algún tipo de desbordamiento.

QUOTED_IDENTIFIER

Cuando está habilitado, permite el uso de comillas dobles para especificar nombres de tablas, columnas, etc. – Así, esos nombres pueden tener espacios y/o caracteres especiales.

CONCAT_NULL_YELDS_NULL

Controlan el resultado de concatenar cadenas con valores nulos. Cuando está habilitado, determina que este join debe devolver un valor nulo; en caso contrario, devolverá la propia cadena.

ANSI_WARNINGS

Cuando está habilitado, determina la generación de mensajes de error cuando:

1. se utilizan funciones de integración y se encuentran valores nulos en el rango de consulta;
2. se encuentran divisiones por cero o desbordamiento aritmético.

NUMERIC_ROUNDABORT

Controlan cómo debe proceder SQL Server cuando se encuentra con una pérdida de precisión numérica en operaciones aritméticas. Si el parámetro está activado y una variable con precisión de dos decimales recibe un valor con tres decimales, la operación se abortará. Si el parámetro está desactivado, el valor se truncará a dos decimales.

Conclusión

Cuando se trata de optimizar las consultas, las vistas indexadas son una buena opción para aprovechar el rendimiento. Por lo tanto, evalúe minuciosamente las consultas que tienen que ver con resúmenes y que se ejecutan con cierta frecuencia y opte por crear vistas indexadas. ¡El resultado vale la pena!