Catégorie : ETL

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.

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


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.