i

Description

Un chronomètre

Source

Syced · page d’origine

Licence

CC0 Domaine public

Photo d’origine, non retouchée. L’arrière-plan en est une version stylisée par le site.

Mesurer sérieusement

Comparer deux façons d'écrire un traitement sans se raconter d'histoires.

En bref

• Mesurer CPU et lectures, pas seulement la durée.
• Répéter, alterner, ne pas comparer chaud et froid.
• Faire varier le volume et regarder la forme de la croissance.
• Vérifier l'égalité des résultats (EXCEPT dans les deux sens).

Sur cette page

1. Méthode
2. Script de mesure : cumul curseur contre fenêtre
   1. 1. Jeu de données
   2. 2. Version procédurale
   3. 3. Version ensembliste
   4. 4. Vérification et nettoyage
3. Lire les résultats
4. Sources et vérification

« C'est plus rapide » n'est pas une mesure. Cette page décrit une méthode de comparaison reproductible, et fournit un script que vous pouvez exécuter chez vous. Aucun chiffre n'est donné ici : les résultats dépendent de votre machine, de votre version de SQL Server et de vos données.

Méthode#

1. Un environnement de test, de préférence isolé et stable ; les ressources partagées ou élastiques font varier les durées.
2. Des données réalistes : volume, distribution, index. Les petits jeux d'essai mentent.
3. Mesurer le temps CPU (SET STATISTICS TIME ON) et les lectures logiques (SET STATISTICS IO ON), pas seulement la durée écoulée.
4. Répéter et alterner les variantes ; ne jamais comparer une exécution à froid avec une exécution à chaud. Éviter de lancer les deux en parallèle.
5. Faire varier le volume (10 000, 100 000, 1 000 000) : la forme de la croissance est plus parlante qu'une seule valeur.
6. Vérifier l'égalité des résultats avant toute conclusion : une variante plus rapide qui donne un autre résultat n'est pas une amélioration.
7. Lire le plan d'exécution réel : écarts d'estimation, déversements, conversions implicites, parallélisme.

Script de mesure : cumul curseur contre fenêtre#

Quatre blocs à exécuter dans l'ordre sur une base de test. Les blocs 2, 3 et 4 doivent s'exécuter dans le même lot (mêmes variables de table).

1. Jeu de données#

SET NOCOUNT ON;
-- 0) Paramètre : nombre de lignes. Commencer petit (10 000), puis 100 000, puis davantage.
DECLARE @N int = 100000;

-- 1) Jeu de données reproductible
IF OBJECT_ID(N'dbo.Bench_Ventes') IS NOT NULL DROP TABLE dbo.Bench_Ventes;
CREATE TABLE dbo.Bench_Ventes
(
    VenteId   int           NOT NULL PRIMARY KEY,
    DateVente date          NOT NULL,
    Montant   decimal(18,2) NOT NULL
);

INSERT INTO dbo.Bench_Ventes (VenteId, DateVente, Montant)
SELECT TOP (@N)
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
       DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 3650, CAST('2016-01-01' AS date)),
       CAST(ABS(CHECKSUM(NEWID())) % 100000 AS decimal(18,2)) / 100
FROM   sys.all_columns AS a
CROSS JOIN sys.all_columns AS b;   -- ajouter un troisième CROSS JOIN si le produit est insuffisant pour @N

2. Version procédurale#

-- 2) Version procédurale (curseur)
DECLARE @Proc TABLE (VenteId int PRIMARY KEY, Cumul decimal(18,2));
DECLARE @Id int, @M decimal(18,2), @Cumul decimal(18,2) = 0;

SET STATISTICS TIME ON;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT VenteId, Montant FROM dbo.Bench_Ventes ORDER BY DateVente, VenteId;
OPEN c;
FETCH NEXT FROM c INTO @Id, @M;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Cumul += @M;
    INSERT INTO @Proc (VenteId, Cumul) VALUES (@Id, @Cumul);
    FETCH NEXT FROM c INTO @Id, @M;
END;
CLOSE c; DEALLOCATE c;
SET STATISTICS TIME OFF;

3. Version ensembliste#

-- 3) Version ensembliste (fenêtre)
DECLARE @Ens TABLE (VenteId int PRIMARY KEY, Cumul decimal(18,2));

SET STATISTICS TIME ON;
INSERT INTO @Ens (VenteId, Cumul)
SELECT VenteId,
       SUM(Montant) OVER (ORDER BY DateVente, VenteId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM   dbo.Bench_Ventes;
SET STATISTICS TIME OFF;

4. Vérification et nettoyage#

-- 4) Vérifier l'égalité des résultats, dans les deux sens (à exécuter dans le même lot que 2 et 3)
SELECT 'Proc moins Ens' AS Sens, * FROM (SELECT * FROM @Proc EXCEPT SELECT * FROM @Ens) AS x
UNION ALL
SELECT 'Ens moins Proc',        * FROM (SELECT * FROM @Ens  EXCEPT SELECT * FROM @Proc) AS y;
-- Résultat attendu : aucune ligne.

-- 5) Nettoyage
DROP TABLE dbo.Bench_Ventes;

Lire les résultats#

• Comparez le temps CPU des deux variantes pour chaque volume.
• Reportez les résultats dans un tableau (volume, CPU, lectures) et observez la croissance : quasi linéaire ou plus que linéaire ?
• Notez la version de SQL Server, le niveau de compatibilité et la configuration, pour pouvoir rejouer la mesure.

Sources et vérification#

• Microsoft Learn — SET STATISTICS TIME — mesure du temps CPU et écoulé
• Microsoft Learn — SET STATISTICS IO — lectures logiques et physiques