SSIS et les mises à jour de masse

SSIS dispose d’un composant de destination OLE DB permettant les chargements rapides. Celui-ci est très efficace pour les insertions de masse.
Par contre, il n’existe pas de composant de « mises à jour à chargement rapide » de même type. La méthode standard pour réaliser les update, qui est généralement présentée dans la documentation officielle et les différents blogs est la suivante : utilisation du composant « Commande OLE DB » dans le flux de données pour la mise à jour. L’inconvénient majeur de cette méthode, est qu’un appel à la méthode « update » est réalisé à chaque ligne traitée. Cela peut être très pénalisant sur des volumes importants de données. Une méthode beaucoup plus efficace peut être employée, que je vais illustrer ici.
L’exemple est le suivant : une table « customers » contient les champs nom, prénom, date de naissance et un identifiant clé primaire. Cette table est correctement indexée sur le nom+prénom , et bien sûr sur la clé primaire. Nous allons mettre à jour la date de naissance à partir d’un fichier CSV contenant le nom, prénom et la date de naissance, et ce pour 150 000 personnes.
La méthode standard est d’avoir un flux de contrôle contenant juste un appel à un flux de données de cette forme-ci :

img1.JPG

Le flux de données est composé ainsi :

img2.JPG

Le composant « Recherche » fait une recherche sur la table Customers pour remonter la clé primaire à partir des champs nom et prénom. Le composant « Mise à jour » réalise l’update suivant : « update Customers set birthdate=? where rowkey=? » avec le mappage suivant (les colonnes de droite représentent les données provenant du flux de données, c’est-à-dire du fichier, ou bien issue de la recherche pour rowkey) :

img3.JPG

Le temps de traitement sur mon portable est de 19 secondes 391 millièmes.
Maintenant, passons à la méthode optimisée. Celle-ci consiste à insérer dans un premier temps les données avec le chargement rapide dans une table temporaire (dans un flux de données), puis dans un deuxième temps à réaliser une mise à jour de la table customers avec une jointure (dans une « tâche d’exécution de requête SQL »). Voici le flux de contrôle reprenant les deux étapes :

img4.JPG

Voici le flux de données :

img5.JPG

On retrouve les deux premières étapes déjà vues au dessus (source du fichier plat, et Recherche). Par contre, la mise à jour est remplacée par un composant de « Destination OLE DB ». Celui-ci est paramétré de la façon suivante :

img6.JPG
Enfin, la « tâche d’exécution de requête SQL » est paramétrée ainsi :
update customers set birthdate=x.birthdate from customers c,tempcustomers x where x.rowkey=c.rowkey
GO
truncate table tempcustomers
GO

Il s’agit d’une syntaxe SQL Server (dans le cas présent, SQL Server 2005, mais fonctionne également sous SQL Server 2000). Il existe des syntaxes équivalentes en Oracle.
Le temps de traitement est passé à 5 secondes 422 millièmes, soit près de 4 fois plus rapide. L’ordre de grandeur peut d’ailleurs être parfois encore bien plus important, et il est étonnant que cette méthode ne soit pas plus souvent préconisée.

CatégoriesETL

7 réponses sur “SSIS et les mises à jour de masse”

  1. Bonjour
    Sachez tout d’abord que vos articles me sont d’une grande aide.

    J’ai un petit probleme cependant, l’utilisateur de mon lot n’aura pas les droits pour créer une table dans la base, rendant impossible l’insertion en table temporaire.

    J’essaie de la remplacer par un recordset mais j’avoue que je galere un peu pour faire l’update (ma solution n’est pas tres performante) ^^’

    Quel serait selon vous le moyen le plus performant pour réaliser les updates mais sans créer de table temporaire ?

    Merci

  2. Bonjour,
    Dans quel composant SSIS s’exécute la requête SQL : j’utilise le composant Recherche mais quand j’exécute je rencontre une erreur (aucune ligne trouvée pour la recherche).
    D’avance merci

  3. Bonjour,
    La requête SQL pour les recherches est écrite dans le composant « Recherche ».
    Si vous souhaitez ignorer les erreurs, c’est à dire dans le cas où une recherche n’a pas permis de trouver la donnée en entrée, il faut explicitement le préciser dans la gestion des erreurs du composant

  4. Bonjour,

    merci pour l’article, ça m’aide bcp car je debute en SSIS.
    par contre, je voudrais savoir si c t possible de faire une Update à partir de deux ou plusieurs tables.

    si oui, comment je code à la fin dans la partie ‘truncate’
    update …
    set….
    Go
    Truncate table TableTemp1, tableTemp2 (comment je dois faire ou ce n’est pas possible)
    GO

    Merci d’avance

  5. Bonjour Annick,

    Rien ne vous empèche de faire deux update du type

    update customers set birthdate=x.birthdate from customers c,tempcustomers x where x.rowkey=c.rowkey
    update customers set birthdate=x.birthdate from customers c,tempcustomers2 x where x.rowkey=c.rowkey

    GO
    truncate table tempcustomers
    truncate table tempcustomers2
    GO

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.