Optimisation des requêtes SQL Server - lenteur inattendue d'une requête simple

Question

Une explication possible se trouve ici dans le commentaire

Dans SQL Server 2014 Enterprise Edition (64-bit) - j'essaie de lire à partir d'une vue. Une requête standard contient juste un ORDER BY et OFFSET-FETCH clause comme celle-ci.

Approche 1

SELECT

    *

FROM Metadata
ORDER BY
    AgeInHours      ASC, 
    RankingPoint    DESC, 
    PublishDate     DESC
OFFSET 150000 ROWS
FETCH NEXT 40 ROWS ONLY

Cependant, cette requête assez simple donne des résultats presque identiques à ceux de l'année précédente. 9 fois plus lent (visible lorsque l'on saute un grand nombre de lignes comme 150k) que la requête suivante qui renvoie le même résultat.

Dans ce cas, je lis d'abord la clé primaire, puis je l'utilise comme paramètre pour la commande WHERE...IN fonction

Approche 2

SELECT 
    * 
FROM Metadata
WHERE NewsId IN (
    SELECT

        NewsId

    FROM Metadata
    ORDER BY
        AgeInHours      ASC, 
        RankingPoint    DESC, 
        PublishDate     DESC
    OFFSET 150000 ROWS
    FETCH NEXT 40 ROWS ONLY
)
ORDER BY
    AgeInHours      ASC, 
    RankingPoint    DESC, 
    PublishDate     DESC

L'analyse comparative de ces deux éléments fait apparaître la différence suivante

(40 row(s) affected)

 SQL Server Execution Times:
   CPU time = 14748 ms,  elapsed time = 3329 ms.

(40 row(s) affected)

 SQL Server Execution Times:
   CPU time = 3828 ms,  elapsed time = 469 ms.

J'ai des index sur la clé primaire, PubilshDate et leur fragmentation est très faible. J'ai également essayé d'exécuter des requêtes similaires sur la table de la base de données, mais dans tous les cas, la deuxième approche permet d'obtenir des gains de performance importants. J'ai également testé cette méthode sur SQL Server 2012.

Quelqu'un peut-il nous expliquer ce qui se passe ?

Schéma

Approche 1 : Plan d'exécution

Approche 2 : Plan d'exécution (partie gauche)

Approche 2 : Plan d'exécution (partie droite)

Answer 1

Pour des requêtes structurées différemment avec le même ensemble de résultats, vous obtenez des plans de requête différents avec une approche et un coût de requête différents. C'est un phénomène courant dans les diverses implémentations de SGBDR SQL.

En fait, dans l'exemple ci-dessus, lorsqu'on sélectionne une petite partie des données d'un grand tableau, il est bon de commencer par réduire et minimiser le nombre de lignes dans le résultat, puis de sélectionner les lignes complètes avec toutes les colonnes, comme dans votre deuxième requête.

Une autre approche consiste à créer un index exact et approprié pour réduire l'ensemble des résultats dans un premier temps. Dans la requête ci-dessus, la solution pourrait consister à regrouper les colonnes de la clause ORDER BY dans la même colonne et dans le même ordre de tri.

(Vous n'avez pas mentionné la structure des index dans les plans de requête, je peux donc imaginer ce qui se cache derrière leurs noms).

Vous pouvez également utiliser l'indexation SQL pour diriger l'optimiseur SQL vers l'index spécifique que vous considérez comme le meilleur pour la tâche au cas où l'optimiseur SQL ne ferait pas le travail.

Answer 2

Lorsque vous exécutez une requête, le moteur recherche un index qui pourrait être utilisé afin d'obtenir les meilleures performances. Votre approche 1 consiste à utiliser un index qui n'inclut pas toutes les colonnes de l'instruction SELECT, ce qui provoque la recherche de clés dans le plan de requête. D'après mon expérience, cette approche est toujours moins performante que l'utilisation de colonnes indexées dans l'instruction SELECT.

Vous pouvez voir la différence si vous créez un index pour AgeInHours, RankingPoint, PublishDate et INCLURE toutes les colonnes (recommandé uniquement à des fins de test).

Pour votre deuxième approche, vous pouvez même obtenir de meilleures performances en utilisant un CTE et en faisant un JOIN au lieu d'un WHERE avec IN ou une table temporaire avec index si vous avez des millions de lignes.