Redgate Hub (Español)
las funciones de la ventana de T-SQL facilitan la escritura de muchas consultas y, a menudo, también proporcionan un mejor rendimiento sobre técnicas más antiguas. Por ejemplo, usar la función LAG es mucho mejor que hacer un auto-join. Sin embargo, para obtener un mejor rendimiento en general, debe comprender el concepto de encuadre y cómo las funciones de ventana dependen de la clasificación para proporcionar los resultados.
Nota: vea mi nuevo artículo para aprender cómo las mejoras en el optimizador en 2019 afectan el rendimiento.,
la cláusula OVER y ordenación
Hay dos opciones en la cláusula OVER que pueden causar ordenación: PARTITION BY y ORDER BY. La partición BY es soportada por todas las funciones de la ventana, pero es opcional. El orden por es requerido para la mayoría de las funciones. Dependiendo de lo que esté tratando de lograr, los datos se ordenarán en función de la cláusula OVER, y ese podría ser el cuello de botella de rendimiento de su consulta.
la opción ORDER BY en la cláusula OVER es necesaria para que el motor de la base de datos pueda alinear las filas, por así decirlo, con el fin de aplicar la función en el orden correcto., Por ejemplo, supongamos que desea que la función ROW_NUMBER se aplique en orden de SalesOrderID. Los resultados se verán diferentes que si desea que la función se aplique en orden de TotalDue en orden descendente., Aquí está el ejemplo:
|
1
2
3
4
5
6
7
8
9
10
11
|
USO AdventureWorks2017; –o cualquier versión tienes
IR
SELECCIONE SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY SalesOrderID) COMO RowNum
DE las Ventas.,SalesOrderHeader;
SELECCIONE SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY TotalDue DESC) COMO RowNum
DE las Ventas.SalesOrderHeader;
|
dado que la primera consulta está utilizando la clave de clúster como la opción Ordenar por, no es necesario ordenar.
La segunda consulta tiene una costosa operación de ordenación.,
la cláusula ORDER BY en la cláusula OVER no está conectada a la cláusula ORDER BY añadida a la Consulta general que podría ser bastante diferente., Here is an example showing what happens if the two are different:
|
1
2
3
4
5
|
SELECT SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY TotalDue DESC) AS RowNum
FROM Sales.,SalesOrderHeader
ORDER BY SalesOrderID;
|
La clave de índice agrupada es SalesOrderID, pero las filas deben ordenarse primero por totaldue en orden descendente y luego de vuelta a SalesOrderID. Eche un vistazo al plan de ejecución:
la cláusula PARTITION BY, soportada pero opcional, para todas las funciones de ventana de T-SQL también causa la ordenación. Es similar, pero no exactamente igual, a la cláusula GROUP BY para consultas agregadas., This example starts the row numbers over for each customer.
|
1
2
3
4
5
6
|
SELECT CustomerID,
SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SalesOrderID)
AS RowNum
FROM Sales.,SalesOrderHeader;
|
El plan de ejecución de la muestra sólo una operación de ordenación, una combinación de CustomerID y SalesOrderID.
la única manera de superar el impacto en el rendimiento de la ordenación es crear un índice específicamente para la cláusula OVER. En su libro Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions, Itzik Ben-Gan recomienda el índice POC. POC significa (P)ARTITION BY, (o)RDER BY, Y (c)overing., Recomienda agregar cualquier columna utilizada para filtrar antes de las columnas PARTITION BY y ORDER BY en la clave. Luego agregue las columnas adicionales necesarias para crear un índice de cobertura como columnas incluidas. Al igual que cualquier otra cosa, tendrá que probar para ver cómo un índice de este tipo afecta a su consulta y la carga de trabajo en general. Por supuesto, no puede agregar un índice para cada consulta que escriba, pero si el rendimiento de una consulta en particular que utiliza una función de ventana es importante, puede probar este consejo.,
Here is an index that will improve the previous query:
|
1
2
3
|
CREATE NONCLUSTERED INDEX test ON Sales.,SalesOrderHeader
(CustomerID, SalesOrderID)
INCLUDE (TotalDue);
|
cuando vuelve a ejecutar la consulta, la operación de ordenación se ha ido del plan de ejecución:
framing
en mi opinión, el framing es el concepto más difícil de entender cuando se aprende sobre las funciones de ventana de T-SQL. Para obtener más información sobre la sintaxis, consulte Introducción a las funciones de ventana de T-SQL., El encuadre es necesario para lo siguiente:
- agregados de ventana con el orden by, utilizado para ejecutar totales o medias móviles, por ejemplo
- FIRST_VALUE
- LAST_VALUE
afortunadamente, el encuadre no es necesario la mayor parte del tiempo, pero desafortunadamente, es fácil omitir el fotograma y usar el predeterminado. El marco predeterminado es siempre el rango entre la fila anterior y actual sin límites. Si bien obtendrá los resultados correctos, siempre y cuando la opción Ordenar por consista en una columna única o un conjunto de columnas, verá un éxito de rendimiento.,id=»4b64f77d3b»>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SET STATISTICS IO ON;
IR
SELECT Idcliente,
SalesOrderID,
TotalDue,
SUM(TotalDue) OVER(PARTITION BY CustomerID ORDEN POR SalesOrderID)
COMO Totalactualizado
DE las Ventas.,SalesOrderHeader;
SELECT CustomerID,
SalesOrderID,
TotalDue,
SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
de ventas.SalesOrderHeader;
|
los resultados son Los mismos, pero el rendimiento es muy diferente. Desafortunadamente, el plan de ejecución no te dice la verdad en este caso., Informa que cada consulta tomó el 50% de los recursos:
si revisa los valores de IO de estadísticas, verá la diferencia:
usar el marco correcto es aún más importante si su orden por opción no es única o si está utilizando LAST_VALUE. En este ejemplo, la columna ORDER BY es OrderDate, pero algunos clientes han realizado más de un pedido en una fecha determinada. Cuando no se especifica el marco o se utiliza el rango, la función trata las fechas coincidentes como parte de la misma ventana.,»>
el motivo de La discrepancia es que el RANGO ve la lógica de los datos, mientras que las FILAS ve posicionalmente. Hay dos soluciones para este problema. Una de ellas es asegurarse de que la opción ORDER BY sea única. La otra opción, y más importante, es especificar siempre el marco en el que se admite.
el otro lugar donde el framing causa problemas lógicos es con LAST_VALUE., LAST_VALUE devuelve una expresión de la última fila del marco. Dado que el marco predeterminado (rango entre la fila anterior y la actual sin límites) solo sube a la fila actual, la última fila del marco es la fila donde se realiza el cálculo.,
agregados de ventana
Una de las características más prácticas de las funciones de ventana de T-SQL es la capacidad de agregar una expresión agregada a una consulta no Agregada. Desafortunadamente, esto a menudo puede funcionar mal. Para ver el problema, debe mirar los resultados de statistics IO donde verá un gran número de lecturas lógicas., Mi consejo cuando necesite devolver valores en diferentes granularidades dentro de la misma consulta para un gran número de filas es usar una de las técnicas más antiguas, como una expresión de tabla común (CTE), una tabla temporal o incluso una variable. Si es posible agregar previamente antes de usar el agregado de ventana, esa es otra opción.,los ujos de la diferencia entre la ventana de agregado y otra técnica:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SELECCIONE SalesOrderID,
TotalDue,
SUM(TotalDue) MÁS() COMO OverallTotal
DE las Ventas.,SalesOrderHeader
WHERE YEAR(OrderDate) =2013;
DECLARE @OverallTotal MONEY;
seleccione @OverallTotal = SUM (TotalDue)
de Sales.SalesOrderHeader
WHERE YEAR(Fechapedido) = 2013;
SELECCIONE SalesOrderID,
TotalDue,
@OverallTotal COMO OverallTotal
DE las Ventas.,SalesOrderHeader COMO SOH
WHERE YEAR(Fechapedido) = 2013;
|
La primera consulta sólo examina la tabla una vez, pero tiene 28,823 lecturas lógicas en una mesa de trabajo. El segundo método escanea la tabla dos veces, pero no necesita la mesa de trabajo.
el siguiente ejemplo utiliza un agregado de windows aplicado a una expresión de agregado:
Cuando se utilizan funciones de ventana en una consulta de agregado, la expresión debe seguir las mismas reglas que las cláusulas SELECT y ORDER BY., En este caso, la función window se aplica a SUM (TotalDue). Parece un agregado anidado, pero en realidad es una función de ventana aplicada a una expresión agregada.
dado que los datos se agregaron antes de que se aplicara la función de ventana, el rendimiento es bueno:
hay una cosa más interesante que debe saber sobre el uso de agregados de ventana. Si utiliza varias expresiones que utilizan coincidencias sobre definiciones de cláusula, no verá una degradación adicional en el rendimiento.
mi consejo es usar esta funcionalidad con precaución., Es bastante práctico, pero no se escala tan bien.
comparaciones de rendimiento
los ejemplos presentados hasta ahora han utilizado las pequeñas ventas.SalesOrderHeader table de AdventureWorks y revisó los planes de ejecución y las lecturas lógicas. En la vida real, sus clientes no se preocuparán por el plan de ejecución o las lecturas lógicas; se preocuparán por la rapidez con la que se ejecutan las consultas. Para ver mejor la diferencia en los tiempos de ejecución, utilicé el guion de Thinking Big (aventura) de Adam Machanic con un giro.
el script crea una tabla llamada bigTransactionHistory que contiene más de 30 millones de filas., Después de ejecutar el guión de Adam, creé dos copias más de su tabla, con 15 y 7,5 millones de filas respectivamente. También activé la propiedad descartar resultados después de la ejecución en el Editor de consultas para que rellenar la cuadrícula no afectara a los tiempos de ejecución. Ejecuté cada prueba tres veces y borré la caché del búfer antes de cada ejecución.,
I can’t say enough about how important it is to use the frame when it’s supported., Para ver la diferencia, ejecuté una prueba para calcular los totales en ejecución utilizando cuatro métodos:
- Solución de Cursor
- sub-consulta correlacionada
- Función de ventana con marco predeterminado
- Función de ventana con filas
ejecuté la prueba en las tres tablas nuevas. Aquí están los resultados en un formato de gráfico:
Cuando se ejecuta con el marco de filas, la tabla de 7,5 millones de filas tardó menos de un segundo en ejecutarse en el sistema que estaba utilizando al realizar la prueba. La tabla de 30 millones de filas tardó aproximadamente un minuto en correr., id=»4b64f77d3b»>
|
1
2
3
4
5
|
SELECT ProductID, SUM(ActualCost) OVER(PARTITION BY ProductID
ORDER BY TransactionDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
FROM bigTransactionHistory;
|
I also performed a test to see how window aggregates performed compared to traditional techniques., En este caso, utilicé solo la tabla de 30 millones de filas, pero realicé uno, dos o tres cálculos usando la misma granularidad y, por lo tanto, la misma cláusula OVER. Comparé el rendimiento agregado de la ventana con un CTE y con una subconsulta correlacionada.
el agregado de ventana tuvo el peor desempeño, Aproximadamente 1.75 minutos en cada caso. El CTE tuvo el mejor rendimiento al aumentar el número de cálculos, ya que la tabla se tocó una sola vez para los tres., La subconsulta correlacionada se desempeñaba peor al aumentar el número de cálculos ya que cada cálculo tenía que ejecutarse por separado, y tocaba la tabla un total de cuatro veces.,v>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
WITH Calcs AS (
SELECT ProductID,
AVG(ActualCost) AS AvgCost,
MIN(ActualCost) AS MinCost,
MAX(ActualCost) AS MaxCost
FROM bigTransactionHistory
GROUP BY ProductID)
SELECT O.,ProductID,
ActualCost,
AvgCost,
MinCost,
MaxCost
DE bigTransactionHistory COMO O
UNIRSE a Calcs O. ProductID = Calc.ProductID;
|
conclusión
las funciones de la ventana de T-SQL se han promocionado como excelentes para el rendimiento. En mi opinión, hacen que escribir consultas sea más fácil, pero necesitas entenderlas bien para obtener un buen rendimiento. La indexación puede marcar la diferencia, pero no puedes crear un índice para cada consulta que escribas., El encuadre puede no ser fácil de entender, pero es muy importante si necesita escalar a tablas grandes.