Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

SQL Server : Performance d’un code TSQL face à un code .Net (SQLCLR) - Split de chaînes

Comme indiqué dans les commentaires d'un précédent billet, qu'en est-il des performances entre du code .Net intégré à SQL Server (SQLCLR) face à du code Transact-SQL pur et dur ? (http://blogs.codes-sources.com/christian/archive/2009/12/17/sql-server-cr-er-une-fonction-de-type-table-tvf-via-net-sqlclr-exemple-d-un-split-de-cha-ne.aspx)

Tout dépend du type de code que vous comparez, dans mon exemple ici, je reprends l'exemple du Split exposé précédemment.

Le test ne se veut pas exhaustif, j'ai pris quelques exemples types de ces fonctions, ayant des caractéristiques différents pour illustrer le comportement. Elles sont toutes en pièces jointe de ce billet pour des questions de lisibilité.

  • Split
    • La fonction .Net
  • Split2
    • Traitement de chaîne par fonctions native SQL
  • Split3
    • Utilise la méthode Nodes() du type XML
  • Split4
    • Traitement de chaîne par fonctions native SQL
  • Split5
    • Algorithme utilisant une CTE recursive, attention il ne fonctionnera pas au-dessus de 32767 éléments à renvoyer

Chacune d'entre elle prend comme argument des chaînes Unicode (nvarchar), pas de limites sur la longueur en entrée. On va cependant réaliser les tests sur une chaîne de caractère courte (4000 caractères).

Le banc de test :

set statistics io on
set
statistics time on

SELECT *
FROM
dbo.Split5(REPLICATE(N'A ', 2000), ' ')

OPTION(MAXRECURSION 32000) ;

SELECT *
FROM
dbo.Split4(REPLICATE(N'A ', 2000), ' ') ;

SELECT *
FROM
dbo.Split3(REPLICATE(N'A ', 2000), ' ') ;

SELECT *
FROM
dbo.Split2(REPLICATE(N'A ', 2000), ' ') ;

SELECT *
FROM
dbo.Split(REPLICATE(N'A ', 2000), ' ') ;

set statistics time off
set
statistics io off

Et le tableau récapitulatif :

 

Split

Split2

Split3

Split4

Split5

Temps CPU

30 - 70 ms

150 – 160 ms

> 4 sec

150 - 160 ms

30 - 70 ms

Nbre I/O

0

4

4

5

> 12000

Les temps processeur étant assez volatile je me suis permis de mettre une fourchette, avec les valeurs que l'on retrouve habituellement lors de ces exécutions.

En termes de temps processeur pur, la fonction .Net est au coude à coude avec Split5 à base de CTE récursive. Cependant le facteur des entrées / sorties (I/O) est aussi à prendre en compte et les 12000 I/O sont très très pénalisante au jour le jour. Je ne parle pas de la limitation intrinsèque du nombre maximale d'itération de cette dernière.

Deuxième test :

Cette fois ci je limite le nombre de lignes renvoyés en rallongeant les fragments qui composent ma chaîne principale.

SELECT *
FROM
dbo.SplitX(REPLICATE(N'AAA ', 500), ' ') ;

Résulats :

 

Split

Split2

Split3

Split4

Split5

Temps CPU

~ 30 ms

~ 30 ms

~ 250 ms

~ 30 ms

~ 15 ms

Nbre I/O

0

2

2

2

~ 3000

Dans cette situation la méthode récursive est clairement la plus rapide, mais toujours au détriment des I/O

Sur une exécution simple, le résultat est sans appel pour cette liste de fonction, le code.Net l'emporte haut la main en termes de performances.

Par contre si je change mon exemple et que je mets une liste de chaîne dans une table de test, et que j'exécute une requête du type :

SELECT *
FROM Table1
    CROSS APPLY Split(col, ' ')

Le résultat se dégrade fortement pour la variante .net face à ses concurrentes en T-SQL. C'est sans doute lié à la différence de comportement des TVF (fonctions de type table) .Net face à leur équivalent SQL. La première renvoie le résultat au fur et à mesure, tandis que le second peuple un espace temporaire avant de renvoyer le résultat.

Mais pourquoi dans exemple précédent le code SQL est plus lent ? Généralement parce que le traitement de chaîne force le moteur à utiliser beaucoup d'allocation de mémoire et dans les pire des cas de l'espace dans tempdb. A l'opposé .Net est optimisé pour ces opérations-là, qui se font uniquement en mémoire et nécessite moins d'allocations.

Comme je l'évoquais ces tests sont nullement exhaustif, il reflète un exemple où .Net peut être globalement plus efficace que le T-SQL. J'essaierais de fournir des exemples contraires par la suite.

Bons tests…

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Publié lundi 21 décembre 2009 10:49 par christian
Attachment(s): SplitAll.zip

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Créer un périphérique Windows To Go 10 ! par Blog de Jérémy Jeanson le 11-21-2014, 04:54

- RDV à Genève le 12 décembre pour l’évènement “SharePoint–Office 365 : des pratiques pour une meilleure productivité !” par Le blog de Patrick [MVP Office 365] le 11-19-2014, 10:40

- [IIS] Erreurs web personnalisées par Blog de Jérémy Jeanson le 11-19-2014, 00:00

- BDD/TDD + Javascript par Fathi Bellahcene le 11-16-2014, 16:57

- Sécuriser sans stocker de mots de passe par Blog de Jérémy Jeanson le 11-15-2014, 08:58

- Où télécharger la preview de Visual Studio 2015 ? par Blog de Jérémy Jeanson le 11-13-2014, 21:33

- Les cartes sont partout ! par Le blog de Patrick [MVP Office 365] le 11-13-2014, 17:26

- [ #Office365 ] Courrier basse priorité ! par Le blog de Patrick [MVP Office 365] le 11-12-2014, 08:56

- [Oracle] Fichier oranfsodm12.dll absent du package client par Blog de Jérémy Jeanson le 11-10-2014, 20:44

- [ #Office365 ] Le chapitre 1 des Groupes est écrit, et alors ? par Le blog de Patrick [MVP Office 365] le 11-10-2014, 20:23