« Les procédures stockées ne sont pas des vues paramétrées » Vraiment en SQL 2012?

L’excellent Adam Machanic a publié en 2006 un post qui a fait date dans la compréhension des procédures stockées, et particulièrement dans un contexte OLE DB (qui a dit SSIS?). Ce post s’appelait Stored procedures are not parameterized views et je vous conseille de le lire si ce n’est déjà fait. Il y explique la raison profonde de la non exposition classique de métadonnées dans les procédures stockées: elles sont non prédictibles et peuvent renvoyer des données très différentes selon la branche – au sens algorithmique – dans laquelle le retour s’effectue. Donc il n’y a pas de stockage dans les catalogues C’est un gros problème pour OLE DB par exemple, qui dans un vaste ensemble de cas est incapable de comprendre les méta associées et complique leur usage comme source, d’un rapport, d’un Data Flow ou autre.

Comment cela marche alors dans SSIS?

SQL Server propose un mode d’exécution appelé FMTONLY ON. Si vous regardez Books Online, vous verrez que cette instruction permet effectivement une inspection des chemins de code et le retour explicite de métadonnées seules dans un objectif de validation. C’est une opération assez empirique, et qui pour être honnête marche parfois assez mal: elle ne gère pas les tables temporaires, pas les curseurs… C’est potentiellement  par exemple ce qui est assez handicapant. Ce post est assez connu sur le sujet des tables temporaires et propose des workarounds qui se valent…

Quelle est la parade aujourd’hui?

En réalité il y en a plusieurs. Une des solutions consiste à désactiver ce FMTONLY pour que SSIS exécute réellement pour extraire les métas. Et effectivement cela fonctionne. C’est la technique bien connue de pas mal de devs, mais elle a ses inconvénients aussi car elle cause de sévères problèmes de performances (SSIS valide constamment les sources, et FMTONLY ON est quand même plus économe qu’une exécution complète).

Il y a aussi la parade qui consiste à faire pas mal de refactoring de code dans le T-SQL pour faire marcher FMTONLY ON. Ce qui n’est pas franchement l’objectif souhaité quand on veut faire du couplage faible entre l’ETL et ses sources… Jamie Thomson en était arrivé à la conclusion dans ce post que les PS ne sont pas faites pour ça… et que dans pareil cas les UDF sont là. Je suis assez d’accord avec lui. Sauf que parfois on ne me laisse pas le choix et je dois quand même utiliser des PS.

Qu’apporte SQL Server 2012?

Il apporte la possibilité d’exécuter une procédure stockée en spécifiant le contrat attendu. Les métadonnées exposées sont celles que vous spécifiez. Si le contrat est enfreint alors l’exécution échoue. Ecrivons une requête bien inutilisable, avec une belle table temporaire pour SSIS🙂

CREATE PROC usp_GetTopCustomersForYear(@Year INT)
AS
 SET NOCOUNT ON
 --Une requête toute bête dans une table temporaire
 SELECT TOP 10 c.CustomerAlternateKey, SUM(s.SalesAmount) SalesAmount
 INTO #TopCustomers
 FROM AdventureWorksDWDenali.dbo.DimCustomer c
 JOIN AdventureWorksDWDenali.dbo.FactInternetSales s
 ON s.CustomerKey = c.CustomerKey
 JOIN AdventureWorksDWDenali.dbo.DimDate d
 ON d.DateKey = s.OrderDateKey
 WHERE d.CalendarYear=@Year
 GROUP BY c.CustomerAlternateKey
 ORDER BY SalesAmount DESC
 --Un SELECT depuis cette table
 SELECT *
 FROM #TopCustomers
GO

Essayons de l’utiliser dans SSIS: échec. Le message d’erreur indique bien que FMTONLY est incapable de parser les métadonnées d’une PS qui fait usage d’une table temporaire.

Là ou je me lancerais maintenant dans un refactoring fastidieux, je vais modifier simplement l’appel à la procédure stockée. Je vais juste spécifier ce que j’attend.

EXEC usp_GetTopCustomersForYear ?
WITH RESULT SETS
(
   (
      CustomerKey NVARCHAR(15),
      Amount FLOAT
   )
)

Le résultat est bien meilleur à tous points de vue!

Il est à noter que ce WITH RESULT SETS peut être utilisé à beaucoup d’autre endroits, pour permettre du couplage très faible entre les applications et les procédures stockées: cela va par exemple permettre de caster les types castables dans un type précis attendu, plutôt que d’être potentiellement embêté par des providers pointilleux sur le typage en cas de modification de la PS par le DBA.

Et pourquoi ne pas avoir directement permis d’exposer les métas des procédures stockées?

Parce que sinon on appellerait ça des UDF. Blague à part ce n’est pas la volonté de Microsoft, la séparation entre procédure et fonction, que les vieux codeurs Pascal connaissent bien aussi, reste dans SQL Server 2012.

D’autres choses?

Oui, on peut noter aussi pour remplacer FMTONLY de nouvelles DMV toutes propres font leur apparition: elles permettent d’exposer des catalogues de métas renvoyés par des procédures stockées si cela est possible. Mais elles partagent avec FMTONLYdes inconvénients. Ma PS n’est pas reconnaissable par ce statement, qui renvoie invariablement:

The metadata could not be determined because statement ‘SELECT * FROM #TopCustomers’ in procedure ‘usp_GetTopCustomersForYear’ uses a temp table.

SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('dbo.usp_GetTopCustomersForYear'),1)

Voilà pour le sujet, en espérant que cela vous permette d’exécuter des PS en source plus facilement. A bientôt!

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s