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

- [MVC] Plusieurs verbes pour une seule méthode du controller par Blog de Jérémy Jeanson le il y a 13 heures et 46 minutes

- SPS Paris 2015 – Back from MS Ignite par Le blog de Patrick [MVP Office 365] le 05-26-2015, 16:04

- Windows 10 IOT– exploitez vos capteurs en tout genre ! par Blog de Daniel TIZON [daniel] le 05-26-2015, 08:06

- SharePoint Saturday Paris 2015 c’est le 30 mai ! par Le blog de Patrick [MVP Office 365] le 05-25-2015, 21:07

- Configurer un écran 5 pouces 800x480 pour Raspberry Pi2 sous Windows 10 IOT par Blog de Daniel TIZON [daniel] le 05-22-2015, 07:52

- Office 365 : Sway est disponible en preview par Le petit blog de Pierre / Pierre's little blog le 05-21-2015, 22:24

- Première installation de Raspbian sur RASPBERRY Pi 2 par Blog de Daniel TIZON [daniel] le 05-14-2015, 22:17

- Mobile Day : c’est le 25 et c’est chez Microsoft! par Fathi Bellahcene le 05-13-2015, 23:48

- TFS 2013 : Migration d’une ferme TFS 2010 vers 2013 sans upgrade par Blog Technique de Romelard Fabrice le 05-01-2015, 16:28

- [ #SharePoint 2016 ] frappe à nos portes ! (1/2) par Le blog de Patrick [MVP Office 365] le 04-19-2015, 23:21