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

- Evénement monfial Global Azure Bootcamp (10 000 participants) Venez !! par Blog de Vincent THAVONEKHAM, Objet Direct le 01-25-2015, 16:29

- Mon Blog déplacé vers une version anglaise... www.thavo.com par Blog de Vincent THAVONEKHAM, Objet Direct le 01-25-2015, 16:25

- Localisation et globalisation ne sont pas des options par Blog de Jérémy Jeanson le 01-17-2015, 11:47

- [Clean Code] les commentaires… par Fathi Bellahcene le 01-10-2015, 17:17

- Mise à jour de Test Professional 2013 par Blog de Jérémy Jeanson le 01-10-2015, 11:32

- [Dynamics CRM] Ajouter un bouton pour déclencher un workflow ou un script (dialogue) par Christine Dubois le 01-09-2015, 14:03

- RDV aux #SharePoint Days 2015 à Casablanca les 28 et 29 janvier ! par Le blog de Patrick [MVP Office 365] le 01-06-2015, 08:41

- TFS Online, vous allez aimer vos projets par Blog de Jérémy Jeanson le 01-03-2015, 11:19

- Bon code 2015 ! par Blog de Jérémy Jeanson le 01-02-2015, 19:01

- [Dynamics CRM] Créer un contact à partir d’une signature email par Christine Dubois le 12-30-2014, 14:37