Partager via

  • Article

Optimisation des performances des requêtes d'entrepôt de données grâce au filtrage Bitmap

La plupart des requêtes d'entrepôt de données sont conçues pour suivre un schéma en étoile et peuvent traiter des centaines de millions de lignes dans une requête unique. Par défaut, l'optimiseur de requête détecte les requêtes correspondant à des schémas en étoile et crée des plans de requête efficaces pour ces requêtes. Une méthode que l'optimiseur peut utiliser pour générer un plan efficace consiste à utiliser le filtrage Bitmap. Un filtre Bitmap utilise une représentation compacte d'un ensemble de valeurs d'une table dans une partie de l'arborescence de l'opérateur pour filtrer les lignes d'une seconde table dans une autre partie de l'arborescence. Le filtre effectue principalement une réduction de semi-jointure ; cela signifie que seules les lignes de la seconde table éligibles pour la jointure avec la première table sont traitées.

Dans SQL Server 2008, le filtrage Bitmap peut être introduit dans le plan de requête après l'optimisation, comme dans SQL Server 2005, ou introduit dynamiquement par l'optimiseur de requête pendant la génération du plan de requête. Lorsque le filtre est introduit dynamiquement, il est appelé filtre Bitmap optimisé. Le filtrage Bitmap optimisé peut améliorer considérablement les performances des requêtes d'entrepôt de données qui utilisent des schémas en étoile, en supprimant les lignes non éligibles de la table de faits tôt dans le plan de requête. Sans le filtrage Bitmap optimisé, toutes les lignes de la table de faits sont traitées dans le cadre d'une partie de l'arborescence de l'opérateur avant que l'opération de jointure avec les tables de dimension supprime les lignes non éligibles. Lorsque le filtrage Bitmap optimisé est appliqué, les lignes non éligibles dans la table de faits sont éliminées immédiatement.

Le filtrage Bitmap optimisé est uniquement disponible dans les éditions Enterprise, Developer et Evaluation de SQL Server.

Présentation du filtrage Bitmap

Le filtre Bitmap effectue une comparaison favorable avec l'index de bitmap. Un index de bitmap correspond à un autre type de représentation de listes d'ID de ligne (RID) dans un index de liste de valeurs, à l'aide d'un ou de plusieurs vecteurs de bits qui indiquent quelle ligne dans une table contient une certaine valeur de colonne. Les deux peuvent être très efficaces pour supprimer les lignes inutiles du traitement des résultats ; toutefois, il existe des différences importantes entre un filtre Bitmap et un index de bitmap. En premier lieu, les filtres Bitmap sont des structures en mémoire, qui suppriment ainsi toute charge de maintenance d'index liée à des opérations de langage de manipulation de données (DML) effectuées sur la table sous-jacente. De plus, les filtres Bitmap sont très petits et, contrairement aux index sur disque existants, qui dépendent habituellement de la taille de la table sur laquelle ils sont générés, les filtres Bitmap peuvent être créés dynamiquement avec un impact minime sur le temps de traitement des requêtes.

Comparaison du filtrage Bitmap et du filtrage Bitmap optimisé

Le filtrage Bitmap et le filtrage Bitmap optimisé sont implémentés dans le plan de requête à l'aide de l'opérateur Bitmap Showplan. Le filtrage Bitmap est appliqué uniquement dans les plans de requête parallèles qui utilisent des jointures de hachage ou de fusion. Le filtrage Bitmap optimisé est utilisable uniquement dans les plans de requête parallèles qui utilisent des jointures de hachage. Dans les deux cas, le filtre Bitmap est créé côté entrée de génération (table de dimension) d'une jointure de hachage ; toutefois, le filtrage lui-même est effectué en général dans l'opérateur Parallelism, qui se trouve côté entrée de test (table de faits) de la jointure de hachage. Lorsque la jointure est basée sur une colonne d'entiers, le filtre peut être appliqué directement à l'opération initiale d'analyse de table ou d'index plutôt qu'à l'opérateur Parallelism. Cette technique est appelée optimisation par ligne.

Lorsque le filtrage Bitmap est introduit dans le plan de requête après l'optimisation, la durée de compilation des requêtes est réduite ; toutefois, les plans de requête que l'optimiseur peut prendre en compte sont limités et les estimations de cardinalité et de coût ne sont pas prises en considération.

Les filtres Bitmap optimisés présentent les avantages suivants :

  • Le filtrage à partir de plusieurs tables de dimension est pris en charge.

  • Plusieurs filtres peuvent être appliqués à un opérateur unique.

  • Les filtres Bitmap optimisés peuvent être appliqués à un plus grand nombre de types d'opérateurs. Ces types d'opérateurs incluent des opérateurs d'échange tels que les opérateurs Distribute Streams et Repartition Streams, les opérateurs d'analyse de table ou d'index et les opérateurs de filtre.

  • Le filtrage est applicable aux instructions SELECT et aux opérateurs en lecture seule utilisés dans les instructions INSERT, UPDATE, DELETE et MERGE.

  • Le filtrage est applicable à la création de vues indexées dans les opérateurs utilisés pour remplir l'index.

  • L'optimiseur utilise des estimations de cardinalité et de coût pour déterminer si le filtrage Bitmap optimisé est approprié.

  • L'optimiseur peut prendre en compte un plus grand nombre de plans.

Procédure d'implémentation du filtrage Bitmap optimisé

Un filtre Bitmap est utile seulement s'il est sélectif. L'optimiseur de requête détermine quand un filtre Bitmap optimisé est suffisamment sélectif pour être utile et dans quels opérateurs le filtre est appliqué. L'optimiseur place les filtres Bitmap optimisés sur toutes les branches d'une jointure en étoile et utilise des règles d'estimation de coût pour déterminer si le plan fournit le plus petit coût d'exécution estimé. Lorsque le filtre Bitmap optimisé est non sélectif, le coût estimé est généralement trop élevé et le plan est ignoré. Lorsque vous envisagez où placer les filtres Bitmap optimisés dans le plan, l'optimiseur recherche des variantes de jointure de hachage telles qu'une pile profonde à droite de jointures de hachage. Les jointures avec les tables de dimension sont implémentées pour exécuter la jointure probablement la plus sélective en premier.

L'opérateur dans lequel s'applique le filtre Bitmap optimisé contient un prédicat de bitmap de la forme PROBE([Opt_Bitmap1001], {[nom_colonne]} [, 'IN ROW']). Le prédicat de bitmap signale les informations suivantes :

  • Le nom de bitmap qui correspond au nom introduit dans l'opérateur Bitmap. Le préfixe 'Opt_' indique qu'un filtre Bitmap optimisé est utilisé.

  • La colonne par rapport à laquelle le test est effectué. Il s'agit du point à partir duquel les données filtrées circulent dans l'arborescence.

  • Si le test de bitmap utilise l'optimisation par ligne. Si tel est le cas, le test de bitmap est appelé avec le paramètre IN ROW. Dans le cas contraire, ce paramètre manque.

Exemple

L'exemple ci-dessous représente une requête sur un schéma en étoile simple. Les deux tables de dimension DimProduct et DimCustomer sont jointes à la table de faits FactInternetSales à l'aide d'une jointure clé primaire à clé étrangère sur une colonne d'entiers unique.

USE AdventureWorksDW2008R2;
GO
SELECT * 
FROM dbo.FactInternetSales AS F
INNER JOIN dbo.DimProduct AS D1 ON F.ProductKey = D1.ProductKey
INNER JOIN dbo.DimCustomer AS D2 ON F.CustomerKey = D2.CustomerKey
WHERE D1.StandardCost <= 30 AND D2.YearlyIncome <= 50000;

L'illustration ci-dessous montre le plan d'exécution pour cette requête tel qu'il peut apparaître dans SQL Server 2005. Aux points marqués 1A, les tables de dimension ont été analysées et les informations nécessaires pour éliminer par filtrage les lignes non éligibles de la table de faits (1B) sont connues. Toutefois, les propriétés de l'opérateur Table Scan indiquent qu'aucun prédicat n'est utilisé pour limiter les lignes retournées à partir de la table de faits.

Plan de requête SQL Server sans filtres de bitmaps.

À l'inverse, l'illustration ci-dessous montre le plan d'exécution de la même requête, tel qu'il peut apparaître dans SQL Server 2008. Les opérateurs bitmap optimisés sont utilisés dans les sous-arborescences des deux tables de dimension. Les propriétés de l'opérateur d'analyse de table indiquent que les filtres (tests de bitmap) provenant de ces sous-arborescences sont appliqués directement à l'arborescence de table de faits pour limiter les lignes retournées à partir de la table de faits avant la première opération de jointure.

Plan de requête SQL Server avec filtres de bitmaps.

Conditions requises pour le filtrage Bitmap optimisé

Le filtrage Bitmap optimisé présente les conditions requises suivantes :

  • Les tables de faits sont supposées avoir au moins 100 pages. L'optimiseur considère les tables plus petites comme des tables de dimension.

  • Seules les jointures internes entre une table de faits et une table de dimension sont prises en considération.

  • Le prédicat de jointure entre la table de faits et la table de dimension doit être une jointure de colonne unique, mais ne doit pas être nécessairement une relation clé primaire à clé étrangère. Une colonne basée sur des entiers est préférée.

  • Les jointures avec des dimensions sont prises en considération uniquement lorsque les cardinalités d'entrée des dimensions sont inférieures à la cardinalité d'entrée de la table de faits.