Une des nouveautés introduites par sqlserver sont les types et fonctions utilisateurs (UDT / UDF) basés sur le common runtime language (CLR). Bien que cette nouvelle fonctionnalité s’éloigne de la norme SQL, elle permet d’étendre le champ d’action du développeur. Mais dans quel cas est-il judicieux de les utiliser ?
Il y a quelques jours un internaute posait la question suivante sur le forum (Discussion sur le forum)
Voilà , je débute en SQL et il m’est demandé de créer une base sous SQL server.
Je voudrais faire la chose suivante:
Créer une table avec un champ ‘nom’ pour lequel je n’autorise que certains caractères, à savoir:
les lettres minuscules et majuscules
le caractère espace
le caractere ‘
J’interdirais donc tout le reste.
Un membre du forum a répondu à cette problématique par une fonction utilisateur SQL (UDF)
(Merci à Elsuket pour son code)
RETURNS BIT AS
BEGIN
DECLARE @listeCarateresAutorises VARCHAR(128)
SET @listeCarateresAutorises = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'''
DECLARE @indice TINYINT, @ok BIT
SELECT @indice = 1, @ok = 1
WHILE @indice = LEN(@chaineAVerifier) + 1
BEGIN
IF PATINDEX('%' + SUBSTRING(@chaineAVerifier, @indice, 1) + '%', @listeCarateresAutorises) = 0
BEGIN
SET @ok = 0
END
SET @indice = @indice + 1
END
RETURN @ok
END
Bien que cette fonction fonctionne très bien, on comprend bien que les fonctions de chaînes proposés par TSQL ne permettent pas de manière aisée de traiter ce problème qui est vérifier si un nom correspond bien à « un modèle » défini. On peut également s’interroger sur les performances d’une telle fonction.
Une autre approche consiste à utiliser les expressions régulières mais SQL Server ne possède pas de support intrinsèque pour celles-ci. Il faut donc passer par la construction d’une UDF CLR. (Fonction réalisée avec C#)
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class UserDefinedFunctions
{
static readonly string nameRegex = "^[a-zA-Z\'\\s]*$";
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean VerifName(SqlString name)
{
Regex exp = new Regex(nameRegex);
return exp.IsMatch(name.Value);
}
};
Une première constatation est que la vérification de la chaîne de caractère par une expression régulière est beaucoup plus simple à écrire et que la création de notre fonction à proprement dite n’est pas complexe au niveau syntaxe du code.
Une autre question se pose également. Si la création de l’UDF CLR n’est pas complexe en soit, est-elle plus performante que l’UDF SQL ? Pour y répondre, nous allons tester ces 2 fonctions et comparer leurs temps de traitement avec un jeu de données important.
(
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
nom VARCHAR(255) NOT NULL
)
GO
DECLARE @i INT
SET @i=0
WHILE (@i < 110000)
BEGIN
INSERT INTO test_nom (nom)
SELECT SUBSTRING(
'abcdefghijklmnopqrstuvwyz01234567890zywvutsr'' '
, convert(smallint, 1+ rand() * 35)
, convert(smallint, 2+ rand() * 10))
SET @i = @i + 1
END
GO
On réalise ensuite nos tests avec les 2 UDF :
-- Test avec UDF CLR
SELECT id,nom,dbo.VerifName(nom) AS verif_pattern
FROM test_nom
-- Test avec UDF SQL
SELECT id,nom,uFn_VerifieNom(nom) AS verif_pattern
FROM test_nom
SET STATISTICS TIME OFF
On vérifie nos résultats :
(110000 row(s) affected)
SQL Server Execution Times:
CPU time = 2829 ms, elapsed time = 8985 ms.
(110000 row(s) affected)
SQL Server Execution Times:
CPU time = 54671 ms, elapsed time = 66781 ms
Force est de constater que l’UDF CLR a traité beaucoup plus rapidement notre jeu de données en prenant beaucoup moins de ressources CPU !!!
Dans le cas de notre test l’utilisation d’une UDF CLR est plus donc adaptée pour traiter un volume de données important.
David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon
Bonjour ylarvor,
J’avais pas vu le commentaire … pas d’envoi de mail ou quoi
Dans le cas précis de la fonction du billet c’est effectivement plus performant. Pour effectuer du traitement de chaîne de caractère complexe, des calculs complexes les UDF CLR vont bien (Je n’ai pas encore eu l’occasion de tester dans mes projets) … Dans les autres cas les UDF natives SQL sont quand même beaucoup plus performantes.
merci pour la démonstration.
La démonstration est limpide, il semble plus simple d’écrire un fonction C#. Par contre, en terme de performance, je demande à voir une comparaison avec la fonction transact sql de elsuket, en effet, j’ai fait des test de performance sur le CLR et les résultats ne sont pas tres bon!