vendredi 10 mai 2013

[SSIS] Lookup paramétré en mode Full Cache

La non équi-jointure est-elle possible en mode ETL pure avec SSIS ?

Nombreux sont les cas d'utilisation où la jointure peut être un peu plus complexe qu'une égalité entre les colonnes de 2 tables. Dans le cas simple et répandu de la récupération d'un ID d'une dimension SCD2 dans le chargement d'une table de fait, il est nécessaire de recourir à une non équi-jointure. Et pourtant cette simple tâche parait complexe à faire de manière performante à l'aide de SSIS.

Rappel: La non équi-jointure consiste en l'utilisation d'opérateur de jointure différent de l'égalité comme par exemple : <, <=, >, >=, LIKE, BETWEEN ... AND ..., etc ...

Comment met-on en place le lookup paramétré dans SSIS

Prenons un exemple simple de la récupération d'un seul ID sur une dimension que l'on appellera Produit.

tables SQL Server
Prenons 2 tables simples pour illutrer le lookup paramétré
dataflow
Créons un dataflow simple avec un lookup pour récupérer l'ID d'une dimension
lookup nocache
Commençons par choisir le mode nocache pour l'exemple
lookup connection
On écrit une requête avec dans la sélection uniquement les colonnes dont on a besoin, l'ID et les colonnes impliqués dans la paramétrisation. Le but est d'avoir des metadata identique avec la requête custom
lookup columns
On map les colonnes sources qui seront les paramètres de la requête custom
lookup custom query
On utilise le ? pour signifier un paramètre
lookup parametres
Et on map les paramètres dans l'ordre d'apparition des ?

ATTENTION: Vous l'avez sans doute remarqué. Les performances de cette méthode ne sont pas satisfaisante. Mais pourquoi me direz-vous ? C'est ce que l'on va tenter d'éclaircir.

Je vous propose d'allumer notre SQL Server Profiler et lancer une trace standard pour capturer les classes d'évenement RPC:Completed, SQL:BatchStarting et SQL:BatchCompleted

sql server profiler
Voilà le résultat de la trace

L'intégralité des traces seraient trop long et indigeste. Je vais en choisir quelques extraits pour les commenter.

  • La trace commence par une phase préparatoire
    exec sp_prepare @p1 output,NULL,N'SELECT [IdProduit]
          ,[CodeProduit]
          ,[Valid_From]
    FROM [dbo].[DimProduit] dp',1
    select @p1
    go
    exec [sys].sp_describe_first_result_set N'SELECT [IdProduit]
          ,[CodeProduit]
          ,[Valid_From]
    FROM [dbo].[DimProduit] dp',NULL,1
    go
    exec sp_unprepare 3
    go
    exec [sys].sp_describe_undeclared_parameters N'SELECT [IdProduit]
          ,[CodeProduit]
          ,[Valid_From]
    FROM [dbo].[DimProduit] dp
    WHERE
     @P1 >=  dp.[Valid_From]
    and @P2 <= ISNULL(dp.[Valid_To],''9999-12-31'')
    and   dp.[CodeProduit]=@P3'
    
  • Puis elle termine par un appel de procédure par ligne
    exec sp_executesql N'SELECT [IdProduit]
          ,[CodeProduit]
          ,[Valid_From]
    FROM [dbo].[DimProduit] dp
    WHERE
     @P1 >=  dp.[Valid_From]
    and @P2 <= ISNULL(dp.[Valid_To],''9999-12-31'')
    and   dp.[CodeProduit]=@P3',N'@P1 date,@P2 date,@P3 char(10)','2013-05-02','2013-05-02','FR00000001'
    go
    

Un appel par ligne ? Un traitement séquentiel sur un moteur relationnel ? On est loin de l'état de l'art de l'ETL et même de l'état de l'art du relationnel. Vous me direz : Oui mais ... là nous avons paramétré le lookup en mode NoCache.

    Il existe effectivement 2 autres modes :
  • le mode Partial Cache
  • et le mode Full Cache

Ce que va changer le mode Partial Cache par rapport au mode NoCache, c'est qu'en début d'exécution le cache sera vide et au fur et à mesure des lectures, les lignes seront ajoutés au cache, ce qui permettra aux nouvelles lignes identiques d'être servies par le cache. Ce mode est très légerement plus performant que le mode NoCache, mais reste très peu performant et loin de la philosophie ETL.

Le mode FullCache permet de monter en cache l'intégralité des données et de traiter les jointures en mémoire de manière performante. Cependant, SSIS ne permet faire des non équi-jointure dans le cache.

A savoir: L'interface empêche de paramétrer des requêtes custom en mode FullCache aussi bien par l'interface que par les propriétés du composant ou que par les expressions du dataflow.

Propriété en PartialCache

lookup parametres

Propriété en FullCache

lookup parametres






On constate que les propriétés de paramétrisation ont disparu.

Mais alors quelle solution choisir dans le cas d'une non équi-jointure ?

En SSIS pure il reste une méthode à base de Merge Join. Mais dont l'inconvénient serait la nécessité de trier les datasets amont ce qui serait parfaitement contre-performant et vraiment non recommandable à partir du moment ou il y a plus d'une jointure de ce type à faire dans un même dataflow.

On pourrait imaginer essayer de bluffer SSIS par une expression pour lui passer dans sa propriété SqlCommand une variable contenant une requête dynamique. Mais cette feinte ne suffirait pas. Il faudrait aller plus loin à coup de requêtes paramétrés, de jointure avec une date et de boucle, pour essayer de se retrouver en situation d'équi-jointure. Une machine à gaz indigeste, contre-performante et de surcroît inmaintenable.

Force est de constater que d'utiliser SSIS en tant qu'ETL (plutôt qu'en ELT ou en séquenceur de procédures stockées) pour traiter ce genre de problématique n'est pas une solution sérieusement envisageable si l'on a beaucoup de volumétrie. Dans ce cas, il faut de manière délibérer utiliser le moteur SQL, qui est de toute façon utilisé contre nature par SSIS. Donc tant qu'à utiliser le moteur sans le savoir et de manière non maitrisé autant le faire à fond avec toute la puissance du moteur (requêtes ensemblistes, utilisation d'indexes et de statistiques) afin d'obtenir des résultats performants.

A bientôt!