El uso de vistas indizadas es un recurso que puede aumentar en un porcentaje importante el rendimiento de una base de datos, veamos un ejemplo:
Supongamos que tenemos que optimizar esta query, lanzada sobre la base de datos AdventureWorksDW2012:
select productkey, spanishproductname,DimProduct.ProductSubcategoryKey, SpanishProductsubCategoryName,DimProductSubcategory .ProductcategoryKey,SpanishProductCategoryName
from dbo.DimProduct
inner join DimProductSubcategory on dimproduct.ProductSubcategoryKey=DimProductSubcategory.ProductSubcategoryKey
inner join DimProductCategory on DimProductSubcategory .ProductcategoryKey = DimProductCategory .ProductCategoryKey
Veamos ahora su plan de ejecución:
Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
Tabla 'DimProduct'. Recuento de exámenes 1, lecturas lógicas 477, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
Tabla 'DimProductSubcategory'. Recuento de exámenes 1, lecturas lógicas 2, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
Tabla 'DimProductCategory'. Recuento de exámenes 1, lecturas lógicas 2, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
Tiempos de ejecución de SQL Server:Tiempo de CPU = 0 ms, tiempo transcurrido = 290 ms.
Vemos ahora que el tiempo de ejecucion es de 290 ms y se han producido 477 lecturas lógicas en la tabla DimProduct.
Creamos una vista indizada con la opción schemabinding y creamos un indice sobre ella:
create view dbo.vproductkey with schemabinding
as select productkey, spanishproductname,DimProduct.ProductSubcategoryKey, SpanishProductsubCategoryName,DimProductSubcategory .ProductcategoryKey,SpanishProductCategoryName
from dbo.DimProduct
inner join dbo.DimProductSubcategory on dimproduct.ProductSubcategoryKey=DimProductSubcategory.ProductSubcategoryKey
inner join dbo.DimProductCategory on DimProductSubcategory .ProductcategoryKey = DimProductCategory .ProductCategoryKey
go
create unique clustered index ixvprodctkey on dbo.vproductkey (productkey,ProductSubcategoryKey,ProductcategoryKey)
y el plan de ejecución resultante:
Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
Tabla 'DimProduct'. Recuento de exámenes 1, lecturas lógicas 477, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
Tabla 'DimProductSubcategory'. Recuento de exámenes 1, lecturas lógicas 2, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
Tabla 'DimProductCategory'. Recuento de exámenes 1, lecturas lógicas 2, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
(1 filas afectadas)
Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 148 ms.
hemos conseguido reducir el tiempo de ejecucion a la mitad, pero el plan de ejecución sigue siendo el mismo. Vamos a ver si podemos mejorarlo:
Ejecutamos lo siguiente:
select * from dbo.vproductkey with(noexpand)
y voilà!
Tabla 'vproductkey'. Recuento de exámenes 1, lecturas lógicas 9, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
(1 filas afectadas)
Tiempos de ejecución de SQL Server:
Tiempo de CPU = 0 ms, tiempo transcurrido = 95 ms
Ahora si hemos reducido el tiempo de ejecución considerablemente y el plan de ejecución realiza un index seek 100%.Además hemos reducido las lecturas lógicas a 9!!
La clave está en la clausula NOEXPAND
NOEXPAND sólo se aplica a las vistas indizadas. Como ya hemos visto una vista indizada es una vista con un índice agrupado único creado en él. Si una consulta contiene referencias a columnas que estén presentes tanto en una vista indizada y en tablas de base, y el optimizador de consultas determina que el uso de la vista indizada ofrece el mejor método para ejecutar la consulta, el optimizador de consultas utiliza el índice de la vista. Esta funcionalidad se denomina coincidencia de vista indizada. El uso automático de una vista indizada por el optimizador de consultas sólo se admite en las ediciones específicas de SQL Server. Para obtener una lista de características que son compatibles con las ediciones de SQL Server, vea Características compatibles con las ediciones de SQL Server 2012.
Sin embargo, para que el optimizador considere el uso de las vistas indizadas o utilice una vista indizada que se referencia con una sugerencia NOEXPAND, las siguientes opciones SET deben establecerse en ON.
ANSI_NULLS ANSI_WARNINGS CONCAT_NULL_YIELDS_NULL
ANSI_PADDING ARITHABORT1 QUOTED_IDENTIFIERS
|
|