[2014] GroupConcat et Split en utilisant le CLR

Bonjour,

Qui n'a jamais envié la fonction d'aggrégation "groupconcat" de MySQL ?
Qui ne s'est jamais cassé la tête avec des CTE, fonctions récursives et autres tables temporaires pour obtenir une fonction split performante ?

Hé bien vous ne rêvez pas, voici les deux sous forme de bundle.

Elles utilisent la capacité de SQL Server, depuis la version 2005 R2 d'utiliser des librairies externes programmées en CLR (n'importe quel langage .NET managed) directement depuis des requêtes SQL.

Si l'appel au CLR est assez lourd, SQL Server tire cependant partie d'un code compilé, qui monte très bien en charge, très utile pour des calculs intensifs, ou pour des traitements sur des chaînes de caractère.

Je ne dirais pas que ces deux fonctions ci-dessous n'ont pas d'équivalent aussi rapide, si ce n'est plus, en T-SQL (quoique ?), mais elles ont cependant plusieurs avantages :
- leur montée en charge devrait être linéaire, là où du code T-SQL peur s'avérer catastrophique
- leur implémentation support le parallélisme d'exécution (par exemple traiter plusieurs blocs de données en // au sein de la même requête)
- ils utilisent ce qui se fait de "mieux" pour aborder ces problèmes (une VRAIE fonction d'agrégation, un VRAI split)
- leur code est tellement enfantin que même mon chien saurait les faire évoluer (bon, j'ai pas de chien, mais j'ai un chat, ça compte ?)

J'ai flagué ce topic "SQL Server 2014" car j'ai fait le développement et test sur cette version (avec Visual Studio 2013).
Cependant, rien ne devrait empêcher le code ci-dessous de compiler avec Visual Studio 2005 et SQL Server 2005R2.

Le support des fonctions CLR est possible avec SQL Server Express, et est compilable avec Visual Studio Express.

Pour commencer, il vous faut créer un projet de type "classe" dans Visual Studio.
Choisissez comme version cible ".NET 2.0" (pas de version plus récente, car même sous 2014, ça demande à bidouiller, et vu qu'on n'en a pas besoin ici...)

Puis créer deux classes :

Aggregates.cs (GroupConcat)

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Data.SqlTypes; using System.IO; using Microsoft.SqlServer.Server; using Microsoft.SqlServer.Types; using System.Text;   namespace DeveloppezDotNet { [Serializable] [SqlUserDefinedAggregate( Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = false, IsInvariantToOrder = false, MaxByteSize = 8000, Name = "GroupConcat") ] public class GroupConcat : IBinarySerialize { private StringBuilder groupconcat;   public void Init() { groupconcat = new StringBuilder(); }   public void Accumulate(SqlString value) { if (!value.IsNull && value.Value.Length > 0) { this.groupconcat.Append(value.Value).Append(','); } }   public void Merge(GroupConcat other) { this.groupconcat.Append(other.groupconcat); }   public SqlString Terminate() { string output = string.Empty; if (this.groupconcat != null && this.groupconcat.Length > 0) { output = this.groupconcat.ToString(0, this.groupconcat.Length - 1); }   return new SqlString(output); }   public void Read(BinaryReader r) { groupconcat = new StringBuilder(r.ReadString()); }   public void Write(BinaryWriter w) { w.Write(this.groupconcat.ToString()); } } }

---

TableFunctions.cs (Split)

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Collections;   namespace DeveloppezDotNet { public class TableFunctions { [SqlFunction(FillRowMethodName = "SplitFillRow")] public static IEnumerable SplitInit(SqlString list, SqlString separator) { return list.Value.Split(separator.Value.ToCharArray()); }   public static void SplitFillRow(object obj, out SqlString line) { line = new SqlString((string)obj); } } }

---

Vous allez ensuite ajouter deux fichiers SQL à votre solution :

Deploy.sql (corrigez le script pour refléter votre base de données et les chemins d'accès)

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

ALTER DATABASE SandBox SET TRUSTWORTHY ON go sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'clr enabled', 1; GO RECONFIGURE; GO   PRINT N'Deploying assemply to database' CREATE ASSEMBLY DeveloppezDotNet FROM N'C:\in\DeveloppezDotNet.dll' WITH PERMISSION_SET = UNSAFE PRINT N'Assembly DeveloppezDotNet.dll created' GO   CREATE AGGREGATE GroupConcat (@input nvarchar(200)) RETURNS nvarchar(max) EXTERNAL NAME DeveloppezDotNet.[DeveloppezDotNet.GroupConcat]; go   CREATE FUNCTION Split(@liste nvarchar(max), @separateurs nvarchar(10)) RETURNS TABLE ( ligne nvarchar(50) ) AS EXTERNAL NAME DeveloppezDotNet.[DeveloppezDotNet.TableFunctions].SplitInit; GO

---

Remove.sql

Code:

---

1 2 3 4 5 6 7 8 9 10 11

PRINT N'Removing assemply from database'   IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'DeveloppezDotNet') BEGIN DROP AGGREGATE GroupConcat DROP FUNCTION Split DROP ASSEMBLY DeveloppezDotNet PRINT N'Assembly DeveloppezDotNet.dll dropped' END GO

---

Dans les options du projet, allez dans "Build Events" et dans la section "Post-build event command line" copiez ce script :
(A nouveau, adaptez les chemins d'accès et base de données)

Code:

---

1 2 3 4 5

sqlcmd -S " localhost" -d "SandBox" -i "$(TargetDir)\Remove.sql" del "c:\in\DeveloppezDotNet.dll" /q copy "$(TargetDir)\DeveloppezDotNet.dll" "C:\in\DeveloppezDotNet.dll" /y sqlcmd -S " localhost" -d "SandBox" -i "$(TargetDir)\Deploy.sql"

---

Et enfin, vous pouvez compiler !

Ensuite, dans SQL Server, vous disposez des deux nouvelles fonctions.

Voici un script d'exemple d'utilisation :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

-- Je vous décourage très fortement à utiliser ce genre de structure, qui ne respecte pas la première forme normale ! create table livre ( id int not null primary key identity, auteur nvarchar(50), titre nvarchar(50), motcles nvarchar(max) ); go   -- Faites pas attention aux valeurs, c'est un exemple de test à 22h15... insert into livre (auteur, titre, motcles) values ('Jack London', 'Croc Blanc', 'loup,chien,nature,nord,roman'); insert into livre (auteur, titre, motcles) values ('Jack London', 'L''Appel de la Forêt', 'loup,chien,nature,nord,roman'); insert into livre (auteur, titre, motcles) values ('Stephen King', 'Ça', 'clown,horreur'); insert into livre (auteur, titre, motcles) values ('Stephen King', 'Rage', 'école,meurtre,drame');   -- Bon, alors, la liste des livres publiés par chaque auteur select auteur, dbo.groupconcat(titre) from livre group by auteur;   -- Et l'explosion des mots clés sous forme de ligne select distinct livre.auteur, motcle.ligne from livre cross apply split(livre.motcles, ',') motcle;   -- Et la magie opère lorsqu'on regroupe les deux fonctions en un seul appel : select livre.auteur, dbo.groupconcat(distinct motcle.ligne) from livre cross apply split(livre.motcles, ',') motcle group by livre.auteur

---

J'espère que vous trouverez autant d'utilité à ma contribution que moi de plaisir à l'écrire !

Bonjour,

ton post/article est très intéressant mais je me pose la question de la maintenance (renommage, évolutions, etc.) de ce genre d'utilisation dans un cadre professionnel.
Qu'en est-il aussi d'un potentiel changement de SGBD (oracle permet-il de faire pareil ?) ?
Il serait donc intéressant de savoir les cas d'utilisation adéquats...
A+

Oracle permet de faire l'équivalent, mais en Java.

Sinon, pour l'évolution du code, il s'agit au final que d'une DLL "assembly" qu'on peut recompiler à la demande pour la faire évoluer.

Les noms des fonctions "SQL Server" sont définies au moment où on les déclare dans SQL Server.

Ainsi, la fonction "split" peut parfaitement s'appeler "eclate" sur une autre base, sans pour autant changer quoi que ce soit au niveau de la DLL :

On peut même faire cohabiter les deux alias :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

-- Split CREATE FUNCTION Split(@liste nvarchar(max), @separateurs nvarchar(10)) RETURNS TABLE ( ligne nvarchar(50) ) AS EXTERNAL NAME DeveloppezDotNet.[DeveloppezDotNet.TableFunctions].SplitInit; GO   -- Eclate CREATE FUNCTION Eclate(@groupe nvarchar(max), @delimiteurs nvarchar(10)) RETURNS TABLE ( morceau nvarchar(50) ) AS EXTERNAL NAME DeveloppezDotNet.[DeveloppezDotNet.TableFunctions].SplitInit; GO

---

Sinon, un exemple concret de cas d'utilisation (qui m'a fait découvrir cette fonctionnalité), c'est lors de traitements plus complexes qu'on ne peut absolument pas traiter en T-SQL, mais qu'on souhaite cependant traiter dans la transaction.

J'ai un client qui nous a demandé, lors de la création d'une fiche client :
- de vérifier l'existence géographique de l'adresse
- de corriger la syntaxe de l'adresse afin de répondre aux normes postales
- de géolocaliser l'adresse
Si l'adresse n'est pas reconnu, ne pas enregistrer la fiche client et retourner une erreur.

Google Maps peut facilement être appelé par WebServices, et permet d'effectuer ces trois opérations.

J'ai donc écrit un trigger en CLR qui effectue un appel au WebService de GoogleMaps.
Ainsi, j'ai pu gérer directement, au niveau de ma transaction unitaire la géolocalisation et la validité des adresses saisies, garantissant un fichier client dénué de toute adresse erronée.

Déterrage, désolé.

Pour information, la solution CLR a été benchmarkée ici, et cette solution s'avère, de loin ou presque, la plus rapide.

http://sqlperformance.com/2014/08/t-...-concatenation

La seule solution non CLR qui s'approche un peu (à 20% près quand même) c'est la syntaxe "for xml path", qui, personnellement, me rebute (car totalement détournée, donc illisible)