Les opérateurs de spool de SQL Server peuvent avoir des impacts positifs et négatifs sur les performances. Bien qu’ils permettent la mise en scène temporaire de jeux de résultats filtrés et transformés, ils peuvent également avoir un impact négatif sur les performances lors de l’écriture des données sur le disque dans tempdb. Dans certains cas, SQL Server peut même décider de ne pas utiliser de spool, ce qui entraîne un traitement de données répété.

Dans cet article, nous explorerons une technique appelée “Diviser pour mieux régner” qui peut aider à améliorer les performances sans ajouter d’index permanents. Cette technique consiste à utiliser des tables temporaires de mise en scène pour imiter les opérations de spool et optimiser l’exécution des requêtes.

Prenons l’exemple d’une requête qui récupère les résultats de décalage pour différents badges à partir d’un mois spécifique de données :

AVEC January2010Badges AS (
    SELECT UserId, Name, Date
    FROM dbo.Badges
    WHERE Date >= '2010-01-01' AND Date <= '2010-02-01'
), Next10PopularQuestions AS (
    SELECT TOP 10 *
    FROM (
        SELECT UserId, Name, Date
        FROM January2010Badges
        WHERE Name = 'Popular Question'
        ORDER BY Date
        OFFSET 10 ROWS
    ) t
), Next10NotableQuestions AS (
    SELECT TOP 10 *
    FROM (
        SELECT UserId, Name, Date
        FROM January2010Badges
        WHERE Name = 'Notable Question'
        ORDER BY Date
        OFFSET 10 ROWS
    ) t
), Next10StellarQuestions AS (
    SELECT TOP 10 *
    FROM (
        SELECT UserId, Name, Date
        FROM January2010Badges
        WHERE Name = 'Stellar Question'
        ORDER BY Date
        OFFSET 10 ROWS
    ) t
)
SELECT UserId, Name
FROM Next10PopularQuestions
UNION ALL
SELECT UserId, Name
FROM Next10NotableQuestions
UNION ALL
SELECT UserId, Name
FROM Next10StellarQuestions

Alors que cette requête utilise une expression de table commune (CTE) pour filtrer les données et calculer les décalages, SQL Server ne sauvegarde pas les résultats de la CTE dans tempdb pour une réutilisation ultérieure. Par conséquent, SQL Server doit analyser à plusieurs reprises l’index clusterisé complet de la table dbo.Badges, ce qui entraîne un grand nombre de lectures logiques.

Pour optimiser cette requête sans ajouter d’index permanents, nous pouvons utiliser des tables temporaires de mise en scène. Voici comment :

DROP TABLE IF EXISTS #January2010Badges;

CREATE TABLE #January2010Badges (
    UserId int,
    Name nvarchar(40),
    Date datetime,
    CONSTRAINT PK_NameDateUserId PRIMARY KEY CLUSTERED (Name, Date, UserId)
);

INSERT INTO #January2010Badges
SELECT UserId, Name, Date
FROM dbo.Badges
WHERE Date >= '2010-01-01' AND Date <= '2010-02-01';

WITH Next10PopularQuestions AS (
    SELECT TOP 10 *
    FROM (
        SELECT UserId, Name, Date
        FROM #January2010Badges
        WHERE Name = 'Popular Question'
        ORDER BY Date
        OFFSET 10 ROWS
    ) t
), Next10NotableQuestions AS (
    SELECT TOP 10 *
    FROM (
        SELECT UserId, Name, Date
        FROM #January2010Badges
        WHERE Name = 'Notable Question'
        ORDER BY Date
        OFFSET 10 ROWS
    ) t
), Next10StellarQuestions AS (
    SELECT TOP 10 *
    FROM (
        SELECT UserId, Name, Date
        FROM #January2010Badges
        WHERE Name = 'Stellar Question'
        ORDER BY Date
        OFFSET 10 ROWS
    ) t
)
SELECT UserId, Name
FROM Next10PopularQuestions
UNION ALL
SELECT UserId, Name
FROM Next10NotableQuestions
UNION ALL
SELECT UserId, Name
FROM Next10StellarQuestions

Dans cette requête modifiée, nous créons une table temporaire appelée #January2010Badges et nous y insérons les données filtrées de janvier 2010. Nous définissons également une clé primaire clusterisée sur cette table temporaire pour optimiser le filtrage.

Les instructions SELECT suivantes lisent ensuite à partir de cette table temporaire au lieu de scanner à plusieurs reprises l’index clusterisé de la table dbo.Badges. Cela réduit le nombre total de lectures logiques et améliore les performances de la requête.

Il est important de noter que cette technique doit être utilisée avec parcimonie et pour de bonnes raisons. Écrire et lire trop de données depuis tempdb peut causer des problèmes de contention. Cependant, lorsqu’elle est mise en œuvre correctement, les tables temporaires de mise en scène peuvent grandement améliorer les performances de certaines requêtes.

En créant explicitement des tables temporaires de mise en scène, vous avez plus de contrôle sur la mise en cache et la réutilisation des données, ce qui se traduit par de meilleures performances de requête. Cette technique vous permet d’optimiser les requêtes sans ajouter d’index permanents, ce qui peut être bénéfique dans des scénarios où l’ajout ou la modification d’index n’est pas réalisable.

La prochaine fois que vous rencontrerez une requête qui pourrait bénéficier d’une mise en scène temporaire, envisagez d’utiliser la technique “Diviser pour mieux régner” pour améliorer les performances.