1. #1
    Expert confirmé
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    2 932
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 38
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : février 2010
    Messages : 2 932
    Points : 4 840
    Points
    4 840
    Billets dans le blog
    1

    Par défaut [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 c# : Sélectionner tout - Visualiser dans une fenêtre à part
    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 c# : Sélectionner tout - Visualiser dans une fenêtre à part
    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 sql : Sélectionner tout - Visualiser dans une fenêtre à part
    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 sql : Sélectionner tout - Visualiser dans une fenêtre à part
    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 bat : Sélectionner tout - Visualiser dans une fenêtre à part
    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 sql : Sélectionner tout - Visualiser dans une fenêtre à part
    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 !
    On ne jouit bien que de ce qu’on partage.

  2. #2
    Membre actif
    Étudiant
    Inscrit en
    avril 2008
    Messages
    311
    Détails du profil
    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : avril 2008
    Messages : 311
    Points : 251
    Points
    251

    Par défaut

    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+

  3. #3
    Expert confirmé
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    2 932
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 38
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : février 2010
    Messages : 2 932
    Points : 4 840
    Points
    4 840
    Billets dans le blog
    1

    Par défaut

    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 sql : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    On ne jouit bien que de ce qu’on partage.

  4. #4
    Expert confirmé
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    2 932
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 38
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : février 2010
    Messages : 2 932
    Points : 4 840
    Points
    4 840
    Billets dans le blog
    1

    Par défaut

    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.
    On ne jouit bien que de ce qu’on partage.

  5. #5
    Expert confirmé
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    février 2010
    Messages
    2 932
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 38
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : février 2010
    Messages : 2 932
    Points : 4 840
    Points
    4 840
    Billets dans le blog
    1

    Par défaut

    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)
    On ne jouit bien que de ce qu’on partage.

Discussions similaires

  1. Réponses: 4
    Dernier message: 05/03/2009, 16h42
  2. peut-on utiliser les CLR pour appeler du code Java ?
    Par Mathusalem dans le forum MS SQL-Server
    Réponses: 2
    Dernier message: 16/09/2008, 13h42
  3. Réponses: 0
    Dernier message: 22/07/2008, 17h47

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo