;
chevron-bottomchevron-leftchevron-rightdownloadfacebookinstagramlink-outlinkedinminusplus
Accessibilité Tous les articles

Optimiser la performance de chargement incrémentale avec Microsoft SSIS

Publication

Janvier 2013

Publié par

Amael Laurier

Dans le cadre d’un chargement incrémental avec Microsoft SSIS nous avons plusieurs possibilités pour la modification des lignes. Néanmoins, toutes les méthodes ne sont pas aussi performantes et il peut s’avérer judicieux d’explorer d’autres façons de faire quand on estime que le temps de chargement est trop long.

Je compare ici la performance pour deux méthodes:

  • La première étant très souvent utilisée et restant efficace pour un petit nombre de lignes (environ 1k)
  • La deuxième étant plus intéressante pour de plus grands volumes.

Pour expliquer ces méthodes, nous utiliserons une simple dimension client d’environ 37000 lignes.


Méthode 1 : La modification de lignes via le composant OLE DB Command

Voici à quoi peut ressembler la structure d’un tel dataflow :

Les clients qui n’existent pas (Lookup No Match Output) sont directement insérés dans la table de destination. Ceux qui existent (Lookup Match Output) sont d’abord filtrés (Conditional Split) puis la requête suivante est exécutée avec le composant OLE DB Command :

J’ai manuellement modifié tous les salaires des clients (YearlyIncome) afin de provoquer une modification de toutes les lignes de la table destination.Cette méthode est fonctionnelle, mais a pris plus de 20 minutes dans mon cas !

Le log SSIS suivant nous l’indique clairement :

Que se passe-t-il exactement ici ? Pourquoi est-ce si long pour modifier ces 37000 lignes ? La réponse est simple : la commande update avec un composant OLE DB Command est effectuée ligne par ligne, ce qui est vraiment lent avec SQL Server.

Restez à l’affût des dernières tendances analytiques

Méthode 2 : Table de staging et requête manuelle

L’idée ici est de stocker toutes les lignes à modifier dans une table intermédiaire (staging), puis d’effectuer une requête SQL pour appliquer toutes les modifications. Le nouveau dataflow ressemble à cela :

Le bouton ‘New…’ à côté du nom des tables dans le composant Load_Customer_Stg nous permet de générer automatiquement la table de staging. Cette petite astuce est disponible depuis Microsoft SSIS 2008. Ensuite, dans le Control Flow nous pouvons ajouter un composant Execute SQL Task et lui passer une requête SQL de ce type :

Ainsi qu’une autre tâche SQL pour vider cette table de staging avant l’exécution du dataflow, ce qui nous donne cette structure finale de Control Flow :

La preuve de l’optimisation de la performance est dans les logs : 23 secondes, nous avons donc une nette amélioration !

Nous avons donc vu deux méthodes pour effectuer les modifications dans le cadre d’un chargement incrémental : l’une assez simple, mais non optimisée, l’autre plus complexe, mais plus performante. Il faut en effet garder à l’esprit qu’un outil comme Microsoft SSIS est très efficace pour aider au développement des ETL, mais ne doit pas nuire à la performance, ce qui implique parfois de complexifier un peu le flot!

Si vous avez d’autres idées ou astuces pour optimiser votre flot, n’hésitez pas à les partager dans les commentaires.