SSIS vs TPL.Dataflow

Publié par Fabrice Michellonet sous le(s) label(s) , , , le 31 octobre 2013

Il y a quelques jours je tweetais de joie, après être tombé sur la msdn concernant TPL.Dataflow
L'API propose un système de pipeline que l'on peut aisément imaginer utiliser dans un process d'ETL.

Ni une ni deux, je m'imagine délaisser SSII lors de mes prochaines alims de base de données et enfin pouvoir unit tester mon ETL.
L'accueil fait par mes collègues purement BI fut plutôt frileux... n'est ce pas David :)


Au rayon des réponses constructives, Fred Brossard, me fait revenir très vite sur terre, en demandant à voir les perfs du framework.

C'est ce que je me propose de présenter ici avec un exemple simple.
L'idée est d'importer le snapshot de septembre des posts de stackoverflow; d'abord avec notre bon vieux SSIS puis avec TPL.Dataflow.

En entrée on a donc un bon gros fichier XML de 170MO correspondant aux nouveaux posts du mois.

Commençons par SSIS, rien de sorcier, une XML source qui se déverse directement dans une SQL Destination pointant vers ma jolie table.



Ce qui nous donne env. 150K lignes intégrées en env. 20 sec



Ok, passons à la version C#. Ce que je vous livre ici est une version non optimisée et naive d'un algo d'import avec TPL.Dataflow.

Commencons par créer nos taches :

// XML datasource 
            var inputBlock = new BufferBlock<XDocument>();

            // Split xml, one for each <row> element
            var xDocumentToXmlRow = new TransformManyBlock<XDocument, XElement>(
                x => x.Root.DescendantNodes().OfType<XElement>());

            // Convert xml <row> element to a Post object.
            var xmlRowToObjectBlock = new TransformBlock<XElement, Post>(
                x => x.FromXElement<Post>(),
                new ExecutionDataflowBlockOptions {MaxDegreeOfParallelism = DataflowBlockOptions.Unbounded});

            // a buffer of 10000 elements
            var batch = new BatchBlock<Post>(10000);

            // SQL destination, using bulk insert.
            var sqlBulk = new ActionBlock<Post[]>(x =>
                {
                    using (SqlBulkCopy sbc = new SqlBulkCopy("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Stackoverflow;Data Source=(local)", SqlBulkCopyOptions.TableLock))
                    {
                        sbc.DestinationTableName = "Posts";

                        // Number of records to be processed in one go
                        sbc.BatchSize = 10000;

                        // commit to the server
                        sbc.WriteToServer(x.ToList().ToDataTable());
                        sbc.Close();
                    }
                },
            new ExecutionDataflowBlockOptions { MaxDegreeOfParallelism = DataflowBlockOptions.Unbounded });

Il nous faut maintenant ordonnancer ces taches, comme nous le faisons dans SSIS en reliant les taches entre elles.

using (inputBlock.LinkTo(xDocumentToXmlRow))
            using (xDocumentToXmlRow.LinkTo(xmlRowToObjectBlock))
            using (xmlRowToObjectBlock.LinkTo(batch))
            using (batch.LinkTo(sqlBulk))
            {
                inputBlock.Completion.ContinueWith(t => xDocumentToXmlRow.Complete());
                xDocumentToXmlRow.Completion.ContinueWith(t => xmlRowToObjectBlock.Complete());
                xmlRowToObjectBlock.Completion.ContinueWith(t => batch.Complete());
                batch.Completion.ContinueWith(t => sqlBulk.Complete());

                var inputfile = XDocument.Load(@"C:\!PoC\Stackoverflow\Sourcefiles\Posts.xml");
                inputBlock.Post(inputfile);

                inputBlock.Complete();
                sqlBulk.Completion.Wait();
            }


Aller, sans plus attendre, voici le résultat de l’exécution :



Je suis sur que vous êtes aussi étonnés que moi... TPL est plus rapide (de quelques 15%) par rapport à SSIS dans ce cas simple.
Pour être tout à fait franc, je ne m'y attendais pas du tout. Je pensais qu'il y aurait vraisemblablement une 20 aine de % d'écart, mais en faveur de SSIS.

Bien que cet exemple simplissime ne puisse pas permettre de généraliser, TPL.Dataflow semble apporter des performances suffisantes, si non meilleures qu'SSIS;
mais surtout, cet API va nous permettre d’écrire des tests unitaires sur nos jobs d'intégration. C'est ce que je vous présenterai dans un prochain article.


Et vous, avez vous déjà utilisé TPL.Dataflow? Pourriez-vous l'utiliser dans vos projets? A la lumière de ce post, que pensez vous de ces performances?

8 commentaires:

Michel Perfetti a dit… @ 31 octobre 2013 à 19:46

Faut pas oublier que SSIS a une couche technique qui nous permet de gérer facilement les lignes d'erreurs et une gestion mémoire intégrée, pas besoin d'installer une appli pour faire tourner le bouzin. Bref ca coute, mais le service vaut bien quelques secondes de plus.

FabriceMICHELLONET a dit… @ 31 octobre 2013 à 22:23

Tu as tout à fait raison Michel; Attention, le but de ce post, n'est pas de dire que TPL c'est mieux que SSIS... pas du tout!

L'idée dans cette série d'articles c'est de démontrer que l'on peut tester son ETL en passant par du code (TPL ou autre); ce qui est littéralement impossible avec SSIS. Et c'est vraiment ça qui m'intéresse. Je voudrais pouvoir être aussi serein quand je fais de l'intégration de données, que lorsque je code en .NET. Et selon moi, ça passe par du Test Unitaire.

En fait si j'ai évoqué cette notion de perf, c'est uniquement parce que je ne m'y attendais pas... mais alors pas du tout...

Jean-Pierre Riehl a dit… @ 1 novembre 2013 à 11:31

Il y a un coût de lancement du package avec SSIS, tu es sur une fonction affine.
Il faudrait voir à lui donner un peu plus à manger pour voir si l'écart s'amenuise.
Sinon, je partage le commentaire de Michel.
Concernant les tests unitaires, ton problème est d'avoir l'exécution en synchrone ? Ce n'est pas réglé avec 2012 ?

FabriceMICHELLONET a dit… @ 1 novembre 2013 à 18:26

Jean-Pierre, je te ferais le même genre de réponse qu'a Michel, je n'ai aucun soucis avec SSIS, ses perfs et je suis plutôt content des fonctionnalités qu'il apporte.
Ce qui m’embête, c'est qu'il ne me permet pas de tester les règles business que j'implémente dans mon ETL.
Par exemple, j'aimerais pouvoir écrire un test qui vérifie que les produits que je suis en train d'importer ont un prix de référence supérieur à 0.
Même sur cet exemple simplissime, je suis incapable avec un DTSX de vérifier que la règle est bien implémentée.
Avec du code c'est beaucoup plus simple.
Et c'est sur cet aspect des tests unitaires, qui dois-je le rappeler sont un des fondements de l'agilité, que je vais continuer cette série d'articles.

Jean-Pierre Riehl a dit… @ 2 novembre 2013 à 10:23

Package.Execute()
Assert.AreEqual(0, [...]("Select count(*) From Product where Price <= 0")
Non, ça ne te va pas ?
Sinon, tu injectes un mauvais prix et tu regardes s'il ressort bien dans tes rejets pour valider que la règle est bien implémentée

FabriceMICHELLONET a dit… @ 3 novembre 2013 à 14:35

Ce que tu proposes ici Jean-Pierre est un test d'intégration encore appelé test de bout en bout. En gros tu exécute tout le processus, et tu vérifie que la sortie correspond à ce que tu attends.

C'est mieux que rien du tout, mais ça ne t'assure pas que ta règle business soit correctement implémentée. Il est par exemple envisageable qu'une ligne ai été rejetée, non pas parce que son prix était inférieur ou égal à 0, mais parce que le lookup vers la "Catégorie de produit" n'a rien ramené.

Avec uniquement un test d'intégration, tu pourrais penser que la règle sur le prix est bien implémentée.... mais il n'en est rien.

Par définition, un test unitaire ne doit tester qu'une seule chose et s'abstraire de tout environnement technique (base de données, système de fichier, réseau etc...). Michael Feathers donne ici (http://www.artima.com/weblogs/viewpost.jsp?thread=126923) une bonne définition de ce que n'est pas un test unitaire.

Pour pouvoir tester cette règle business avec SSIS, il me faudrait pouvoir exécuter mon composant Conditional Split (implémentation de ma règle business) en lui donnant une ligne de donnée arbitraire et utiliser Assert.AreEquals sur la sortie du composant.

Jean-Pierre Riehl a dit… @ 4 novembre 2013 à 07:50

J'envisageais autant d'exécutions et de jeux de données d'entrée que de tests unitaires.
Mais je te trouve trop puriste quand même.

Frank a dit… @ 22 janvier 2014 à 10:56

Interesting. I wonder how long it will be before someone combines TPL Dataflow with WF4.5 to make an alternative to SSIS. Apart from testing, large SSIS projects have problems with structuring packages, source control, finding skilled people, etc...

Publier un commentaire