Catégories
ETL SQL Server

Top 10 SSIS performance advices

  • Sorts
  • SSIS sort component is a first-class bottleneck as soon as the number of rows to sort needs a disk swap (which is very early). It is then very advisable to use the database engine to sort your data :
    • Make a dataflow to insert data in a temporary table in the database
    • Create an index on the rows to sort of the temporary table
    • Read the data in an other dataflow with an « order by » clause, and force the properties « IsSorted » and « SortKeyPosition »

  • Joins
  • SSIS has a decent join component. Prior to using this component, the rows of both input flows must be sorted. You should sort these rows in previous dataflows (see the point above about sorts)

  • Transfering data between dataflows
  • It is sometimes advisable to split a dataflow, in order to reduce the execution tree size (execution tree :Microsoft Technet : Performance Tuning Techniques). The more performant way to to this is to use row files (data source, and data destination as row file). These files have an SSIS binary format, optimized for this kind of usage, and keep all datatype and sorting meta-data.
     

  • upsert and mass-update
  • See SSIS et les mises à jour de masse and SSIS et les UPSERTs

  • Lookups
  • Lookups are a fundamental part of any ETL. SSIS allows a « memory restriction » parameter (in the advanced tab). It is very advisable to disallow « memory restriction », it will the load all data in memory, and perform the match with no roundtrip to the database. You should be carefull when writing the query, and avoid any « SELECT * ». If the dataset is to large to be kept in memory, you could check the « memory restriction », and create temporarily a covering index on the query before the dataflow (you drop the covering index after).

  • BufferTempStoragePath
  • If you have several disks on your machine, check this dataflow property is located on a rapid disk.

  • Enginethreads
  • This dataflow property is a hint for SSIS. Generaly speaking, this should have a value of twice the number of cores of your CPUs. If you have very few cores, consider splitting your dataflows to have small execution trees.

  • DefaultBufferSize
  • I usually use 15 000 000 on a 32 bit machine with 4GB.

  • DefaulBufferMaxRows
  • The value of 10 000 is usualy a good measure.

  • simple recovery mode
  • If your SSIS package does a lot of update/insert : put your database in simple recovery mode before your package execution, and then back to complete recovery mode after.

    Catégories
    ETL SQL Server

    Script SSIS sur une connexion SMO

    Ayant eu récemment besoin d’écrire un package SSIS, pour une procédure d’exploitation SQL Server, j’ai eu besoin de réutiliser des connexions de type SMO dans des scripts du package. Les connexions SMO sont utilisées notamment par les tâches de transfert d’objets entre bases de données (transfert de tables, de procédures stoquées, …). J’utilisais une de ces tâches, et j’avais également besoin de faire quelques traitements spécifiques en script .NET sur ces même connexions. Ce type d’utilisation étant mal documenté, voici comment j’ai procédé :

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports Microsoft.SqlServer.Management.Smo
    Public Class ScriptMain

    Public Sub Main()
    Dim server As Server
    Dim db As Database

    server = CType(Dts.Connections("destination").AcquireConnection(vbNull), Server)
    db = server.Databases("MYDB")
    db.ExecuteNonQuery(CStr(Dts.Variables("myQuery").Value))

    Dts.TaskResult = Dts.Results.Success
    End Sub

    End Class

    Catégories
    ETL SQL Server

    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 :

    img1.JPG

    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.

    Catégories
    ETL

    SSIS et les UPSERTs

    L’UPSERT est une fonctionnalité couramment utilisée dans les ETL à mis chemin entre l’instruction INSERT et UPDATE. Elle consiste à insérer une ligne de données si celle-ci n’existe pas en base, ou sinon à la mettre à jour. Le SQL « officiel », ainsi que SQL Server ne proposent pas d’opérateur dédié à cette opération. Il semblerait que SQL Server 2008 ait ce type d’opérateur en gestation, mais ce n’est pas l’objet de ce post.
    Les méthodes de réalisation d’UPSERT font régulièrement l’objet d’analyse sur le web. Après moult lectures, et essais, voici ma méthode, efficace dans la grande majorité des cas.
    L’exemple fait référence à mon précédent article sur « SSIS et les mises à jour de masse ».
    Le principe est de disposer d’un flux de données réalisant
    • l’insertion de masse des lignes adéquates,
    • l’insertion en table temporaire des lignes à modifier
    Puis une requête SQL pour réaliser la mise à jour par jointure (voir mon article « SSIS et les mises à jour de masse).
    Voici le flux de contrôle :
    img1.jpg

    Voici le flux de données associé :

    img2.jpg

    On retrouve la « recherche » du nom et prénom. Il est important de noter que la gestion des erreurs de cette recherche est configurée sur « Ignorer l’échec » de la façon suivante:

    img3.jpg

    Cela signifie que si la recherche de la clé primaire n’aboutie pas, celle-ci est positionnée à la valeur NULL dans le flux de données.
    Dès lors, il est possible de départager les lignes à insérer ou à mettre à jour avec un composant de « Fractionnement conditionnel » de la sorte :

    img4.jpg

    Catégories
    ETL

    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.