Envoyé par
pcaboche
Non. Les fonctions avec fenêtrage (toutes celles avec la clause OVER dedans), c'est relativement lent.
Relativement par rapport à quoi ? à une auto jointure ? j'en doute...
Envoyé par
pcaboche
Donc la solution de al1_24 reste la meilleure.
Je ne serai pas aussi péremptoire.
Déjà, pour qu'elles soient équivalentes, il faudrait faire une jointure externe pour conserver la date la plus ancienne (qui n'a donc pas de correspondance dans la jointure)
Testons !
Création et peuplement de la table avec 10 000 lignes :
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| --creation de la table
CREATE TABLE matable (
id INT NOT NULL PRIMARY KEY IDENTITY
,heure DATETIME2(0)
)
--ajout de 10 000 lignes
;WITH cte AS (
select top(10000) row_number() over(order by a.object_id) as n
from sys.objects A, sys.objects B, sys.objects C, sys.objects D
)
INSERT INTO matable(heure)
SELECT dateadd(s, n, '1970-01-01')
FROM cte |
requete avec jointure (externe) :
Table 'Worktable'. Nombre d'analyses 1, lectures logiques 320110, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'Worktable'. Nombre d'analyses 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'matable'. Nombre d'analyses 4, lectures logiques 99, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
SQL Server \endash Temps d'exécution*:
, Temps UC = 23900*ms, temps écoulé = 25043*ms.
requete avec fonction fenêtrée :
Table 'Worktable'. Nombre d'analyses 6, lectures logiques 80006, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'matable'. Nombre d'analyses 1, lectures logiques 26, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
SQL Server \endash Temps d'exécution*:
, Temps UC = 125*ms, temps écoulé = 204*ms.
Soit environ 120 fois plus rapide, avec 4 fois moins d'IO...
Avec un index :
CREATE INDEX IX_HEURE ON matable(heure )
La requete avec jointure s'en sort mieux :
Table 'matable'. Nombre d'analyses 10003, lectures logiques 123921, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'Worktable'. Nombre d'analyses 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
SQL Server \endash Temps d'exécution*:
, Temps UC = 13027*ms, temps écoulé = 7813*ms.
Mais reste encore à la traine par rapport à la requete avec fonctions fenêtrées :
Table 'Worktable'. Nombre d'analyses 6, lectures logiques 80006, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'matable'. Nombre d'analyses 1, lectures logiques 22, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
SQL Server \endash Temps d'exécution*:
, Temps UC = 109*ms, temps écoulé = 222*ms.
Envoyé par
pcaboche
Plutôt moins (pertinent) que plus !
Le cas d'utilisation des fonctions fenêtrées dans cet article n'est pas le cas le plus pertinent. Il est facile de démontrer qu'un tracteur est plus rapide qu'une Ferrari, si l'on effectue les tests au milieu d'un champs après trois jours de pluie !
D'ailleurs cet article est presque de la désinformation... Pourquoi un CROSS APPLY alors qu'une simple jointure sur une table dérivée aurait suffit :
- heureusement, l'optimiseur "corrige" l'erreur et fourni le même plan d’exécution qu'avec une jointure
- malheureusement, le lecteur peu averti (et/ou peu critique) conclut trop rapidement "vive cross apply" comme on peu le voir dans les commentaires.
Malgré tout, il contient un (trop) petit paragraphe spécifique aux fonctions de classement (donc celles que nous utilisons ici), lequel se termine par un
Kudos to Microsoft on this one!
Envoyé par
pcaboche
Donc au final, il n'y a pas de secret : il faut tester.
Ça veut dire :
- regarder le plan d'exécution (pour voir s'il y a beaucoup d'opérations coûteuses)
- vérifier s'il existe une solution avec les méthodes "traditionelles"
- si oui, se demander :
-> est-ce qu'elle est facile à implémenter ?
-> a-t-on besoin d'un éventuel gain de performance dans le contexte en question ?
- si oui, est-ce qu'il y a un gain de performance ? (plan d'exécution encore)
- faire un choix (vaut-il mieux une requête plus rapide ou plus lisible ?)
Là par contre, on est bien d'accord !
Attention toutefois aux comparaisons des plan d’exécution effectuées par SSMS (même les plans réels) : les pourcentages relatifs indiqués ne correspondent pas toujours à la réalité. Il convient de vérifier également les temps d’exécution et le IO des requêtes comparées.
Partager