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

- Technofolies, votre évènement numérique de l'année par Le Blog (Vert) d'Arnaud JUND le 09-26-2014, 18:40

- Xamarin : From Zero to Hero par Fathi Bellahcene le 09-24-2014, 17:35

- Conférences d’Automne 2014 par Le blog de Patrick [MVP SharePoint] le 09-24-2014, 14:53

- [TFS] Supprimer un projet de Visual Studio Online par Blog de Jérémy Jeanson le 09-22-2014, 20:42

- Nouveau blog en anglais / New blog in english ! par Le blog de Patrick [MVP SharePoint] le 09-18-2014, 18:42

- [ #Yammer ] From Mailbox to Yammer and back / De votre messagerie vers Yammer et retour ! par Le blog de Patrick [MVP SharePoint] le 09-15-2014, 11:31

- [ #Office 365 ] New service settings panel / Nouveau panneau de paramétrage des services par Le blog de Patrick [MVP SharePoint] le 09-11-2014, 08:50

- Problème de déploiement pour une démo SharePoint/TFS? par Blog de Jérémy Jeanson le 09-10-2014, 21:52

- [ #Office365 ] Delve first impressions / Premières impressions sur Delve par Le blog de Patrick [MVP SharePoint] le 09-09-2014, 16:57

- [ #Office365 ] How to change Administration console language ? / Comment changer la langue de la console d’administration ? par Le blog de Patrick [MVP SharePoint] le 09-09-2014, 08:25