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 :
Le flux de données est composé ainsi :
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) :
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 :
Voici le flux de données :
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 :
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.
Laisser un commentaire