Redgate Hub (Français)
Les fonctions de la fenêtre T-SQL facilitent l’écriture de nombreuses requêtes et offrent souvent de meilleures performances par rapport aux techniques plus anciennes. Par exemple, l’utilisation de la fonction LAG est tellement mieux que de faire une auto-jointure. Pour obtenir de meilleures performances dans l’ensemble, cependant, vous devez comprendre le concept de cadrage et comment les fonctions de fenêtre s’appuient sur le tri pour fournir les résultats.
REMARQUE: consultez mon nouvel article pour savoir comment les améliorations apportées à l’optimiseur en 2019 affectent les performances!,
la Clause OVER et le tri
Il y a deux options dans la clause OVER qui peuvent provoquer le tri: PARTITION BY et ORDER BY. PARTITION BY est pris en charge par toutes les fonctions de fenêtre, mais il est facultatif. La commande par est requise pour la plupart des fonctions. Selon ce que vous essayez d’accomplir, les données seront triées en fonction de la clause OVER, ce qui pourrait être le goulot d’étranglement des performances de votre requête.
L’option ORDER BY dans la clause OVER est requise pour que le moteur de base de données puisse aligner les lignes, pour ainsi dire, afin d’appliquer la fonction dans le bon ordre., Par exemple, supposons que vous souhaitiez que la fonction ROW_NUMBER soit appliquée dans L’ordre SalesOrderID. Les résultats seront différents que si vous souhaitez que la fonction soit appliquée dans L’ordre de TotalDue dans l’ordre décroissant., Voici l’exemple:
|
1
2
3
4
5
6
7
8
9
10
11
|
UTILISER AdventureWorks2017; –quelle que soit la version, vous devez
ALLER
SÉLECTIONNEZ SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY SalesOrderID) COMME RowNum
DE la Vente.,SalesOrderHeader;
SÉLECTIONNEZ SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY TotalDue DESC) COMME RowNum
DE la Vente.SalesOrderHeader;
|
Depuis la première requête est à l’aide de la clé de cluster comme la COMMANDE EN option, pas de tri est nécessaire.
La deuxième requête a une coûteuse opération de tri.,
la clause ORDER BY dans la clause OVER n’est pas connectée à la clause ORDER BY ajoutée à la requête globale qui pourrait être très différente., 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
COMMANDE PAR SalesOrderID;
|
La clé d’index cluster est SalesOrderID, mais les lignes doivent d’abord être triée par TotalDue dans l’ordre décroissant, et puis de nouveau à SalesOrderID. Jetez un œil au plan d’exécution:
la clause PARTITION BY, prise en charge mais facultative, pour toutes les fonctions de fenêtre T-SQL provoque également un tri. C’est similaire, mais pas exactement comme, la clause GROUP BY pour les requêtes agrégées., 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;
|
Le plan d’exécution de la présente juste une opération de tri, une combinaison de code client et SalesOrderID.
La seule façon de surmonter l’impact sur les performances de tri est de créer un index spécifiquement pour la clause OVER. Dans son livre Microsoft SQL Server 2012 Haute Performance T-SQL en utilisant des fonctions de fenêtre, Itzik Ben-Gan recommande l’indice POC. POC signifie (P)ARTITION par, (O)RDER par, et (c)overing., Il recommande d’ajouter toutes les colonnes utilisées pour le filtrage avant la PARTITION par et L’ordre Par colonnes dans la clé. Ajoutez ensuite toutes les colonnes supplémentaires nécessaires pour créer un index de couverture en tant que colonnes incluses. Comme toute autre chose, vous devrez tester pour voir comment un tel index affecte votre requête et votre charge de travail globale. Bien sûr, vous ne pouvez pas ajouter un index pour chaque requête que vous écrivez, mais si les résultats d’une requête qui utilise une fonction de fenêtre est importante, vous pouvez essayer ce conseil.,
Here is an index that will improve the previous query:
|
1
2
3
|
CREATE NONCLUSTERED INDEX test ON Sales.,SalesOrderHeader
(CustomerID, SalesOrderID)
INCLUDE (TotalDue);
|
lorsque vous réexécutez la requête, l’opération de tri est maintenant supprimée du plan d’exécution:
framing
à mon avis, Le Framing est le concept le plus difficile à comprendre lors de l’apprentissage des fonctions de fenêtre T-SQL. Pour en savoir plus sur la syntaxe, voir introduction aux fonctions de fenêtre T-SQL., Le cadrage est requis pour les éléments suivants:
- agrégats de fenêtres avec L’ordre Par, utilisé pour les totaux ou les moyennes mobiles, par exemple
- FIRST_VALUE
- LAST_VALUE
heureusement, le cadrage n’est pas nécessaire la plupart du temps, mais malheureusement, il est facile de sauter le cadre et La trame par défaut est toujours comprise entre la ligne précédente et la ligne actuelle Non bornée. Bien que vous obteniez les résultats corrects tant que L’option ORDER BY se compose d’une colonne unique ou d’un ensemble de colonnes, vous verrez un succès de performance.,si vous avez besoin d’une carte de crédit, vous pouvez utiliser la carte de crédit ci-dessous pour obtenir une carte de crédit et une carte de crédit.6
Les résultats sont les mêmes, mais les performances sont très différentes. Malheureusement, le plan d’exécution ne vous dit pas la vérité dans ce cas., Il indique que chaque requête a pris 50% des ressources:
Si vous examinez les valeurs IO des statistiques, vous verrez la différence:
L’Utilisation du cadre correct est encore plus importante si votre commande par option n’est pas unique ou si vous utilisez LAST_VALUE. Dans cet exemple, la colonne ORDER BY est OrderDate, mais certains clients ont passé plus d’une commande à une date donnée. Lorsque vous ne spécifiez pas le cadre ou utilisez RANGE, la fonction traite les dates correspondantes dans le cadre de la même fenêtre., »>
La raison de cette différence est que la GAMME voit les données logiquement, alors que les LIGNES voit en position. Il y a deux solutions pour ce problème. L’une consiste à s’assurer que L’option ORDER BY est unique. L’autre option, plus importante, consiste à toujours spécifier le cadre où il est pris en charge.
l’autre endroit où le cadrage cause des problèmes logiques est avec LAST_VALUE., LAST_VALUE renvoie une expression de la dernière ligne du cadre. Étant donné que la trame par défaut (plage entre la ligne précédente et la ligne actuelle Non bornée) ne va que jusqu’à la ligne actuelle, la dernière ligne de la trame est la ligne où le calcul est effectué.,
agrégats de fenêtres
l’une des fonctionnalités les plus pratiques des fonctions de fenêtre T-SQL est la possibilité D’ajouter une expression d’agrégation à une requête non agrégée. Malheureusement, cela peut souvent mal fonctionner. Pour voir le problème, vous devez regarder les résultats des statistiques IO où vous verrez un grand nombre de lectures logiques., Mon conseil lorsque vous devez renvoyer des valeurs à différentes granularités dans la même requête pour un grand nombre de lignes est d’utiliser l’une des techniques les plus anciennes, telles qu’une expression de table commune (CTE), une table temporaire ou même une variable. S’il est possible de pré-agréger avant d’utiliser l’agrégat de fenêtre, c’est une autre option.,ux de la différence entre la fenêtre de l’agrégat et une autre technique:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SÉLECTIONNEZ SalesOrderID,
TotalDue,
SUM(TotalDue) SUR() COMME OverallTotal
DE la Vente.,SalesOrderHeader
WHERE YEAR(OrderDate) =2013;
DECLARE @OverallTotal MONEY;
SELECT @OverallTotal = SUM(TotalDue)
from Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2013;
SELECT SalesOrderID,
TotalDue,
@OverallTotal AS OverallTotal
from Sales.,SalesOrderHeader COMME SOH
LORSQUE l’ANNÉE(date de Commande) = 2013;
|
La première requête ne scanne la table une fois, mais il a 28,823 lectures logiques dans une table de travail. La deuxième méthode analyse la table deux fois, mais elle n’a pas besoin de la table de travail.
l’exemple suivant utilise un agrégat windows appliqué à une expression d’agrégat:
lorsque vous utilisez des fonctions de fenêtre dans une requête d’agrégat, l’expression doit suivre les mêmes règles que les clauses SELECT et ORDER BY., Dans ce cas, la fonction window est appliquée à SUM(TotalDue). Cela ressemble à un agrégat imbriqué, mais c’est vraiment une fonction de fenêtre appliquée à une expression d’agrégat.
étant donné que les données ont été agrégées avant l’application de la fonction window, les performances sont bonnes:
Il y a une autre chose intéressante à savoir sur l’utilisation des agrégats de fenêtres. Si vous utilisez plusieurs expressions qui utilisent la correspondance sur les définitions de clause, vous ne verrez pas de dégradation supplémentaire des performances.
Mon conseil est d’utiliser cette fonction avec précaution., C’est assez pratique mais ne s’adapte pas si bien.
les Comparaisons de Performances
Les exemples présentés jusqu’à présent ont utilisé les petites Ventes.SalesOrderHeader table de AdventureWorks et examiné les plans d’exécution et les lectures logiques. Dans la vraie vie, Vos clients ne se soucieront pas du plan d’exécution ou des lectures logiques; ils se soucieront de la vitesse à laquelle les requêtes s’exécutent. Pour mieux voir la différence dans les temps d’exécution, j’ai utilisé le script Thinking Big (Adventure) d’Adam Machanic avec une torsion.
le script crée une table appelée bigTransactionHistory contenant plus de 30 millions de lignes., Après avoir exécuté le script D’Adam, j’ai créé deux autres copies de sa table, avec respectivement 15 et 7,5 millions de lignes. J’ai également activé la propriété Discard results after execution dans l’éditeur de requête afin que le remplissage de la grille n’affecte pas les temps d’exécution. J’ai exécuté chaque test trois fois et effacé le cache tampon avant chaque exécution.,
I can’t say enough about how important it is to use the frame when it’s supported., Pour voir la différence, j’ai exécuté un test pour calculer les totaux en cours d’exécution en utilisant quatre méthodes:
- solution de curseur
- sous-requête corrélée
- fonction de fenêtre avec cadre par défaut
- fonction de fenêtre avec lignes
j’ai exécuté le test sur les trois nouvelles tables. Voici les résultats dans un format de graphique:
lors de l’exécution avec le cadre de lignes, la table de 7,5 millions de lignes a pris moins d’une seconde pour s’exécuter sur le système que j’utilisais lors du test. La table de 30 millions de lignes a pris environ une minute pour fonctionner., 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., Dans ce cas, j’ai utilisé uniquement la table de 30 millions de lignes, mais j’ai effectué un, deux ou trois calculs en utilisant la même granularité et, par conséquent, la même clause OVER. J’ai comparé les performances de l’agrégat de fenêtre à un CTE et à une sous-requête corrélée.
l’agrégat de fenêtre a été le moins performant, environ 1,75 minute dans chaque cas. Le CTE a obtenu les meilleurs résultats lors de l’augmentation du nombre de calculs, car le tableau n’a été touché qu’une seule fois pour les trois., La sous-requête corrélée a moins bien fonctionné lors de l’augmentation du nombre de calculs, car chaque calcul devait être exécuté séparément, et elle a touché la table quatre fois au total.,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 QUE O
REJOIGNEZ Calcs SUR O. ProductID = Calcs.ProductID;
|
Conclusion
Les fonctions de la fenêtre T-SQL ont été promues comme étant excellentes pour les performances. À mon avis, ils facilitent l’écriture de requêtes, mais vous devez bien les comprendre pour obtenir de bonnes performances. L’indexation peut faire une différence, mais vous ne pouvez pas créer d’index pour chaque requête que vous écrivez., Le cadrage peut ne pas être facile à comprendre, mais il est si important si vous devez évoluer vers de grandes tables.