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 :

01 methode1 running resized 600
Figure 1 : Exécution d’un dataflow utilisant le composant OLE DB Command

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 :

02 methode1 update resized 600
Figure 2 : Modification du salaire

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 :

03 methode1 running time resized 600

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.

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 :

04 methode2 running df resized 600
Figure 3 : Remplacement du OLE DB Command par OLE DB Destination

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 :

05 requête resized 600

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 :

06 methode2 running cf resized 600
Figure 4 : Control Flow

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

07 methode2 running time resized 600

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.

Partager cet article
  


CONTACT

agileDSS Inc.
407, rue McGill, bureau 500.
Montréal (QC) H2Y 2G3.

info@agiledss.com
(514) 788-1337