Sql server : MTVF or ITVF ?

Voici un retour d’expérience à propos d’une optimisation SQL réalisée sur le projet SIMBA où en modifiant la syntaxe de déclaration d'une fonction, nous avons divisé le temps d’exécution par 6…

Voici la syntaxe initiale de la fonction : Multi-Statement Table-Valued Functions (MTVF)

Et voici la syntaxe optimisée : la version Inline Table-Valued Functions (ITVF)

Il n’y a pas de différence en termes de résultat entre ces 2 fonctions : elles renvoient toutes deux le résultat d'un select et le mode d’appel est strictement identique.

Différence syntaxique :

Version Multi-Statement Table-Valued Functions (MTVF) :

  • On déclare une variable table en retour
  • On alimente la table temporaire au sein d'un bloc BEGIN / END
  • On utilise le mot RETURN pour renvoyer le résultat

Version Inline Table-Valued Functions (ITVF)

  • La fonction ne revoit que le résultat d'un select, pas besoin de variable de retour ni d'alimentation de table.

Différence en termes de gain de performance

Lorsque la fonction n'est censée renvoyer que le résultat d'un select, il est plus intéressant de passer en mode Inline Table-Valued Functions (ITVF) car :

  • Le mode ITVF est capable d'exposer son plan d’exécution à la requête externe : le moteur SQL est donc capable d'optimiser le plan d’exécution de manière globale en intégrant le select renvoyé par la fonction.
  • Le mode MTVF est exécuté dans un contexte différent. De ce fait, le moteur SQL ne peut pas optimiser le plan d’exécution de manière globale. Il traite le select exécuté par la fonction de manière totalement séparée de la requête principale.

Limite de la syntaxe Inline Table-Valued Functions (ITVF)

Malheureusement, si la fonction revoit plusieurs select ou doit faire des opérations (calcul, déclaration et alimentation de variables temporaires...) avant de renvoyer le résultat, on ne peut pas la convertir en ITVF car elle est trop complexe.

Ci joint un lien qui explique bien les différences entre ITVF et MTVF

http://blog.waynesheffield.com/wayne/archive/2012/02/comparing-inline-and-multistatement-table-valued-functions/

Conclusion : même si le résultat d'une fonction écrite en ITVF et MTVF est identique, il est plus intéressant en termes de performance d'utiliser la syntaxe ITVF plutôt que  MTVF. En interne, SQL Server considère une fonction ITVF comme une vue (même si une ITVF est plutôt une vue à laquelle on peut passer des paramètres) : il est donc capable d'optimiser son plan d’exécution. Par contre, SQL Server traite une fonction MTVF comme une procédure stockée : il ne peut donc pas l'optimiser comme il le ferait pour une vue. Par ailleurs, un autre avantage d'une fonction ITVF est qu'elle est manipulable comme une vue.

Fred L.

Ajouter un commentaire

Tags: