SSIS : « Dimension à variation lente » ou « Recherche » ?

Faisant réponse à mon article précédent « SSIS et les UPSERTs », un lecteur a fait la remarque judicieuse suivante : pourquoi utiliser un composant recherche, puis une mise à jour en passant par une table temporaire intermédiaire, alors que SSIS propose un composant dédié aux UPSERT, appelé « Dimension à variation lente », ou encore en anglais « Slowly Changing Dimension » (SCD) ?

La réponse est : « la performance ». N’étant pas tout à fait sûr de mon fait au prime abord, j’ai voulu prendre le temps de faire des tests. La réponse s’impose d’elle-même. Le scénario est le suivant :

• la table de destination contient au départ 80 000 lignes
• la source de données contient 160 000 lignes,
  • dont 80 000 ont des clés sur des données de la table de destination
    • dont 40 000 nécessitent effectivement des mises à jour
• soit au total 80 000 INSERT, et 40 000 UPDATE

Voici la tâche de flux de données que j’ai utilisé pour le test :

Ma méthode utilisant le composant de recherche réalise l’opération en 8 secondes 375.

La méthode Slowly Changing Dimension réalise la même opération en 1 minute 53 sec. 353.

Les performances de ce composant ne sont vraiment pas à la hauteur. Parcourant les blogs, j’ai pu d’ailleurs lire que celui-ci a été conçu par Microsoft pour permettre la mise en place aisée de type UPSERT, mais que la mise en place de ce type de process performante doit rester plus spécifique.

L’une des raisons en est que les mises à jour réalisées par le SCD sont une « commande OLE DB ». C’est à dire que pour chaque ligne à mettre à jour, une commande « UPDATE » va être envoyée au moteur de base de données. Cela est particulièrement peu performant, comme j’ai pu l’expliquer dans d’autres articles (SSIS et les mises à jour de masse), et il est nettement préférable de faire un chargement rapide (bulk insert) en table temporaire, suivi d’une mise à jour ensembliste en fin de traitement (update … select …). Il est possible de mettre en place manuellement ce design pattern sur le SCP en remplaçant la commande OLE DB générée.

De plus, par défaut, les insertions paramétrées par ce composant ne sont pas des « chargements rapides » (probablement pour des questions de compatibilité avec certains moteurs de SGBD). Cette objection est facilement corrigeable manuellement.

Enfin, le composant lui-même semble particulièrement peu performant. Je ne connait pas son implémentation interne, mais la raison est certainement qu’il a été conçu pour pouvoir réaliser un nombre de choses assez important, qui ne sont pas utiles dans notre cas d’espèce.