Amélioration des performances du flux de données

La propriété MaxConcurrentExecutables est une propriété du package lui-même. Cette propriété définit le nombre de tâches pouvant s'exécuter simultanément. La valeur par défaut est -1, ce qui correspond au nombre de processeurs physiques ou logiques plus 2.

Pour comprendre comment cette propriété fonctionne, imaginez un package composé de trois tâches de flux de données. Si vous attribuez à MaxConcurrentExecutables la valeur 3, les trois tâches de flux de données peuvent s'exécuter simultanément. Toutefois, supposez que chaque tâche de flux de données comporte 10 arborescences d'exécution de la source vers la destination. Le fait d'attribuer à MaxConcurrentExecutables la valeur 3 ne garantit pas que les arborescences d'exécution à l'intérieur de chaque tâche de flux de données s'exécuteront en parallèle.

La propriété EngineThreads est une propriété de chaque tâche de flux de données. Cette propriété définit le nombre de threads que le moteur de flux de données peut créer et exécuter en parallèle. La propriété EngineThreads s'applique aussi bien aux threads sources que le moteur de flux de données crée pour les sources qu'aux threads de travail que le moteur crée pour les transformations et les destinations. Par conséquent, si vous attribuez à EngineThreads la valeur 10, le moteur pourra créer jusqu'à dix threads sources et dix threads de travail.

Pour comprendre comment cette propriété fonctionne, reprenez l'exemple de package composé de trois tâches de flux de données. Chaque tâche de flux de données contient dix arborescences d'exécution de la source vers la destination. Si vous attribuez à EngineThreads la valeur 10 sur chaque tâche de flux de données, les 30 arborescences d'exécution pourront potentiellement s'exécuter simultanément.

De nombreux composants de flux de données utilisent des requêtes, soit au cours de l'extraction de données à partir de sources, soit au cours d'opérations de recherche dans le but de créer des tables de référence. La requête par défaut utilise la syntaxe SELECT * FROM <NomTable>. Ce type de requête retourne toutes les colonnes dans la table source. Le fait de disposer de toutes les colonnes au moment de la conception permet de choisir n'importe quelle colonne comme colonne de recherche, comme colonne SQL directe ou comme colonne source. Cependant, après avoir sélectionné les colonnes à utiliser, vous devez vérifier la requête et vous assurer qu'elle contient uniquement les colonnes utilisées. La suppression de colonnes superflues permet de créer une ligne plus petite et donc d'accroître l'efficacité du flux de données dans un package. Avec des lignes plus petites, vous pouvez faire tenir plus de lignes dans un tampon et, de ce fait, réduire la charge de travail nécessaire pour traiter toutes les lignes dans le dataset.

Pour construire une requête, vous pouvez taper la requête ou utiliser le générateur de requêtes.

Le tri est, par essence, une opération lente et la décision d'éviter un tri inutile peut améliorer les performances du flux de données du package.

Parfois, les données sources ont déjà été triées avant d'être utilisées par un composant en aval. Ce pré-triage peut avoir lieu soit parce que la requête SELECT utilise une clause ORDER BY, soit parce que les données ont été insérées dans la source par ordre de tri. Pour de telles données sources pré-triées, vous pouvez fournir un indicateur qui précise que les données sont triées afin d'éviter l'utilisation d'une transformation de tri pour satisfaire aux spécifications de tri de certaines transformations en aval. (Par exemple, les transformations de fusion et de jointure de fusion nécessitent des entrées triées). Pour fournir un indicateur qui précise que les données sont triées, vous devez effectuer les tâches suivantes :

• attribuer à la propriété IsSorted sur la sortie d'un composant de flux de données en amont la valeur True ;

• spécifier les colonnes de clé de tri sur lesquelles les données sont triées.

Pour plus d'informations, consultez Procédure : trier des données pour les transformations de fusion et de jointure de fusion.

Si vous devez trier les données dans le flux de données, vous pouvez améliorer les performances en concevant le flux de données de façon à utiliser aussi peu d'opérations de tri que possible. Par exemple, le flux de données utilise une transformation de multidiffusion pour copier le dataset. Triez le dataset une fois avant que la transformation de multidiffusion ne s'exécute au lieu de trier plusieurs sorties après la transformation.

Pour plus d'informations, consultez Transformation de tri, Transformation de fusion, Transformation de jointure de fusion et Transformation de multidiffusion.

Lorsque vous utilisez une source OLE DB pour extraire les données d'une vue, sélectionnez « Commande SQL » comme mode d'accès aux données et entrez une instruction SELECT. L'utilisation d'une instruction SELECT pour accéder aux données offre de meilleures performances que le mode d'accès aux données « Table ou vue ».

La transformation d'agrégation inclut les propriétés Keys, KeysScale, CountDistinctKeys et CountDistinctScale. Ces propriétés améliorent les performances en permettant à la transformation de préallouer la quantité de mémoire dont la transformation a besoin pour les données que la transformation met en cache. Si vous savez le nombre exact ou approximatif des groupes attendus d'une opération Group by, définissez les propriétés Keys et KeysScale, respectivement. Si vous savez le nombre exact ou approximatif des valeurs distinctes attendues d'une opération Distinct count, définissez les propriétés CountDistinctKeys et CountDistinctScale, respectivement.

Si vous devez créer plusieurs agrégations dans un flux de données, songez à créer plusieurs agrégations qui utilisent une transformation d'agrégation au lieu de créer plusieurs transformations. Cette approche améliore les performances lorsqu'une agrégation est un sous-ensemble d'une autre agrégation, car la transformation peut optimiser le stockage interne et analyser une seule fois les données entrantes. Par exemple, si une agrégation utilise une clause GROUP BY et une agrégation AVG, le fait de les combiner en une seule transformation peut améliorer les performances. Toutefois, du fait que la réalisation de plusieurs agrégations au sein d'une transformation d'agrégation sérialise les opérations d'agrégation, il est possible que les performances ne s'améliorent pas lorsque plusieurs agrégations doivent être calculées indépendamment.

Pour plus d'informations, consultez Transformation d'agrégation.

Pour plus d'informations sur l'optimisation des performances des transformations de recherche floue et de regroupement probable, consultez le livre blanc Présentation des transformations Fuzzy Lookup (recherche approximative) et Fuzzy Grouping (regroupement approximatif) dans les services DTS (Data Transformation Services) de SQL Server 2005.

Réduisez la taille des données de référence en mémoire en entrant une instruction SELECT qui recherche uniquement les colonnes dont vous avez besoin. Cette approche est plus performante que la sélection d'une table ou d'une vue entière qui retourne une quantité importante de données inutiles.

La transformation de jointure de fusion inclut la propriété MaxBuffersPerInput, qui spécifie le nombre maximal de tampons qui peuvent être actifs pour chaque entrée à un moment donné. Vous pouvez utiliser cette propriété pour régler la quantité de mémoire que les tampons consomment et par la même occasion améliorer les performances de la transformation. Plus le nombre de tampons est important, plus la transformation utilise de mémoire et plus les performances sont élevées. La valeur par défaut de la propriété MaxBuffersPerInput est 5, ce qui est le nombre de tampons qui fonctionne bien dans la plupart des scénarios. Pour régler les performances, vous voudrez peut-être essayer d'utiliser un nombre de mémoires tampons légèrement différent, comme 4 ou 6. Si possible, évitez d'utiliser un très petit nombre de mémoires tampons. Par exemple, le réglage de la propriété MaxBuffersPerInput à 1 au lieu de 5 a un impact significatif sur les performances. N'attribuez pas non plus une valeur égale ou inférieure à 0 à MaxBuffersPerInput. Cette plage de valeurs signifie en effet l'absence de toute limitation, et il se peut que le package ne s'exécute pas selon le chargement des données et la quantité de mémoire disponible.

Pour éviter tout blocage, la transformation de jointure de fusion peut augmenter provisoirement le nombre de tampons qu'elle utilise au-delà de la valeur de MaxBuffersPerInput. Lorsque la condition de blocage est résolue, la propriété MaxBuffersPerInput reprend sa valeur de configuration.

Pour plus d'informations, consultez Transformation de jointure de fusion.

L'Assistant Dimension à variation lente et la transformation de dimension à variation lente sont des outils à caractère général qui répondent aux besoins de la plupart des utilisateurs. Toutefois, le flux de données généré par l'Assistant n'est pas optimisé en termes de performances.

En général, les composants les plus lents de la transformation de dimension à variation lente sont les transformations de commande OLE DB qui effectuent des mises à jour sur une ligne à la fois. Par conséquent, le moyen le plus efficace pour améliorer les performances de la transformation de dimension à variation lente consiste à remplacer les transformations de commande OLE DB. Vous pouvez remplacer ces transformations par des composants de destination qui enregistrent toutes les lignes à mettre à jour dans une table de transit. Ensuite, vous pouvez ajouter une tâche d'exécution SQL qui effectue une opération UPDATE Transact-SQL basée sur un jeu unique sur toutes les lignes en même temps.

Les utilisateurs expérimentés peuvent concevoir un flux de données personnalisé pour le traitement des dimensions à variation lente qui est optimisé pour les grandes dimensions. Pour en savoir plus et obtenir un exemple de cette approche, consultez la section « Unique dimension scenario » dans le livre blanc Project REAL: Business Intelligence ETL Design Practices (en anglais).

Lorsqu'un package charge des données dans une instance de SQL Server sur le même ordinateur, utilisez une destination SQL Server. Cette destination est optimisée pour les chargements en masse à haute vitesse.

L'enregistrement des données sur les destinations peut être plus long que prévu. Pour déterminer si la lenteur provient de l'incapacité de la destination à traiter rapidement des données, vous pouvez provisoirement remplacer la destination par une transformation de calcul du nombre de lignes. Si le débit en sortie s'améliore significativement, il est probable que la destination chargeant les données est la cause du ralentissement.

L'entrée de journal personnalisée la plus utile est peut-être l'événement PipelineComponentTime. Cette entrée de journal signale le nombre de millisecondes que chaque composant dans le flux de données passe sur chacune des cinq étapes de traitement majeures. Le tableau suivante décrit ces étapes de traitement. Les développeurs Integration Services reconnaîtront ces étapes comme les méthodes principales d'un PipelineComponent.

Lorsque vous activez l'événement PipelineComponentTime, Integration Services consigne un message pour chaque étape de traitement effectuée par chaque composant. Les entrées de journal suivantes illustrent un sous-ensemble des messages consignés par l'exemple de package Integration Services CalculatedColumns :

The component "Calculate LineItemTotalCost" (3522) spent 356 milliseconds in ProcessInput.

The component "Sum Quantity and LineItemTotalCost" (3619) spent 79 milliseconds in ProcessInput.

The component "Calculate Average Cost" (3662) spent 16 milliseconds in ProcessInput.

The component "Sort by ProductID" (3717) spent 125 milliseconds in ProcessInput.

The component "Load Data" (3773) spent 0 milliseconds in ProcessInput.

The component "Extract Data" (3869) spent 688 milliseconds in PrimeOutput filling buffers on output "OLE DB Source Output" (3879).

The component "Sum Quantity and LineItemTotalCost" (3619) spent 141 milliseconds in PrimeOutput filling buffers on output "Aggregate Output 1" (3621).

The component "Sort by ProductID" (3717) spent 16 milliseconds in PrimeOutput filling buffers on output "Sort Output" (3719).

Ces entrées de journal montrent que la tâche de flux de données a passé la plupart du temps dans les étapes suivantes, répertoriées ici dans l'ordre décroissant :

• La source OLE DB nommée "Extract Data" a passé 688 ms à charger des données.

• La transformation de colonne dérivée nommée "Calcule LineItemTotalCost" a passé 356 ms à effectuer des calculs sur les lignes entrantes.

• La transformation d'agrégation nommée "Sum Quantity and LineItemTotalCost" a passé un total de 220 ms (141 ms dans PrimeOutput et 79 ms dans ProcessInput) à effectuer des calculs et à passer les données à la transformation suivante.