Discussion :

[Troyan]

Bonjour,

nous avons des procedures stockées en CLr et expression régulière pour nettoyer des champs en caracteres accentués et en caracteres parasite.

toutefois c'est des grosses volumétrie plusieurs millions de ligne et c'est tres long.

en perl cela va bien mais alors bien plus vite.
mais cela ne nous facillite pas la vie en terme de manipulation.
existe t'il quelques chose à ce sujet ?


qui pourrait etre bien optimisé ? ou perl reste le meilleur.


Merci par avance

[mikedavem]

Bonjour,

Un exemple de chaîne à nettoyer ?
Le code de la procédure CLR ?
Comment exécutez vous cette procédure ?

Merci

++

[Philippe Robert]

Tu devrais trouver ton bonheur dans les papiers de SQLPro, en SQL

http://sqlpro.developpez.com/cours/sqlserver/udf/

Cherche le mot clé "purge".

[Troyan]

exemple de chaine à nettoyer...

Thomas ; Névallön-TUTU

doit donner

THOMAS; NEVALLON-TUTU

en gros c'est les majuscules (sans accent)
car le upper les garde sauf si vous avez une astuce

et engros on garde de la chaine les lettres
a-z
A-Z
1-9
et le caractere -
les apostrophes sont remplacé par espace, mais tous les espaces doivent se cumuler à 1 maximum.


chaque champ à traiter est envoyé à une fonction CLR.


via un update du style
update TOTO
set A = dbo.clean(A),
B = dbo.clean(B)

[mikedavem]

Bonjour,

Il faudrait voir le code de votre fonction CLR mais celle-ci pourrait convenir je pense. A tester en terme de performance !!!

Code :

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
using System;
using System.Text.RegularExpressions;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
public partial class RegExpReplaceString
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static string RegExpReplaceStringCLR(string StringToClean)
    {
        byte[] aOctets = System.Text.Encoding.GetEncoding(1251).GetBytes(StringToClean);
        string replaceSpecialCaracters = System.Text.Encoding.ASCII.GetString(aOctets);
        return Regex.Replace(replaceSpecialCaracters, @"\s+|\""", " ").ToUpper();
    }
};

++

[Troyan]

Merci fred j'ai une erreur en Vb.net de ta function je voulais tester la vitesse.

j'etais arrivé à une fonction proche de la tienne ...
et je voulais voir la variante mais elle gauffre je l'ai surement mal traduite en VB.net

voici le code de ta fonction traduite

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Partial Public Class RegExpReplaceString
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function RegExpReplaceStringCLR(ByVal StringToClean As String) As SqlString
 
 
        Dim aOctets As Byte() = System.Text.Encoding.GetEncoding(1251).GetBytes(StringToClean)
        Dim replaceSpecialCaracters As String = System.Text.Encoding.ASCII.GetString(aOctets)
        Return Regex.Replace(replaceSpecialCaracters, "\s+|\""", " ").ToUpper()
 
            End Function
 
End Class

et voici celle que j'utilise qui malheureusement n'est pas aussi rapide que d'utiliser le perl en extrene plus de beaucoup mais bon !
sachant que le remplacement des caracteres spéciaux est remplacé par un espace et que 2 espaces d'affilé doivent etre remplacé par un seul voir meme tout les espaces qui se suivent doivent finir en 1 espace.
j'applique meme pas cette regex et j'suis quand meme plus lent...snif

Code :

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
Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function fn_test(ByVal STR As SqlString) As SqlString
 
        Dim aOctets As Byte() = System.Text.Encoding.GetEncoding(1251).GetBytes(STR.ToString)
        Dim txt As String = System.Text.Encoding.ASCII.GetString(aOctets).ToUpper
 
        Dim Carac As String = "[^ A-Z0-9-]"
        Dim instance As New Regex(Carac)
 
        If instance.IsMatch(txt) Then
            txt = instance.Replace(txt, " ")
        End If
        Return txt
 
 
    End Function
End Class

[mikedavem]

Merci fred j'ai une erreur en Vb.net de ta function je voulais tester la vitesse.

Moi c'est David ou Mikedavem

j'etais arrivé à une fonction proche de la tienne ...
et je voulais voir la variante mais elle gauffre je l'ai surement mal traduite en VB.net

Faudrait que j'essaye en vb.net. J'avoue être bcp plus à l'aise en CSsharp
Mais personnellement je ne pense pas que vous soyez plus rapide avec une fonction .NET.

Il faudrait peut être passer par les objets OLE directement mais je ne pense pas que cela change beaucoup.

++

[Bluedeep]

Les RegEx sont assez désastreuses en terme de temps de traitement.

Ici, vu le coté assez primitif du traitement, un simple string.Replace serait plus performant.

Par ailleurs, c'est une mauvaise idée de faire cela dans une fonction puisque l'update sera fait même si il n'y a rien à remplacer.


Sinon, merci de mettre les balise [CODE], VB est déjà assez fouillli comme cela sans en rajouter

Quant à Perl plus rapide que du SQLCLR, cela relève de la poésie toute pure.

[mikedavem]

Je pense également qu'il n'y a pas besoin d'utiliser la méthode match .. ce qui allourdit le traitement.

D'ailleurs remarquez que je n'en mentionne pas dans mon code.

Par ailleurs, c'est une mauvaise idée de faire cela dans une fonction puisque l'update sera fait même si il n'y a rien à remplacer.

Il faut voir le volume de données à modifier en effet. Mais il est facile de contourner ce problème en ajouter une colonne qui permettrait de flager si la colonne a été mise à jour ou pas.

++

[Bluedeep]

Il faut voir le volume de données à modifier en effet. Mais il est facile de contourner ce problème en ajouter une colonne qui permettrait de flager si la colonne a été mise à jour ou pas.

Peut être serait il plus simple, une fois le contenu existant de la base traitée, de procéder via des triggers pour les insertions/mise à jour futures et de mettre à jour les données au moment de leur écriture.

[mikedavem]

Peut être serait il plus simple, une fois le contenu existant de la base traitée, de procéder via des triggers pour les insertions/mise à jour futures et de mettre à jour les données au moment de leur écriture.

... et du coup rendre moins performante l'application en général .. personnellement je suis plus pour faire un traitement asynchrone pour ce genre de problème car cela a beaucoup mois d'impact sur l'activité.

++

[Bluedeep]

... et du coup rendre moins performante l'application en général .. personnellement je suis plus pour faire un traitement asynchrone pour ce genre de problème car cela a beaucoup mois d'impact sur l'activité.

++

C'est pas faux, mais à voir si la présence de ces données non normalisées (dont on ne sait pas trop en quoi elles sont génantes) n'a pas d'impact sur l'activité.

De toute évidence, la solution ici serait que l'application cliente qui alimente la table en question soit capable de normaliser elle même ses données.

[mikedavem]

C'est pas faux, quoique la solution ici serait que l'application cliente qui alimente la table en question soit capable de normaliser elle même ses données.

+1

[Troyan]

bon alors ptite mise au point c'est cool !

Perl met 15minutes pour 20millions de lignes sur 2 champs à traiter.

La premiere fucntion mettait Plus d'une heure !!! je crois 2 ou 3 meme pour etre precis.


L'avant derniere function celle que je vous ai mis mettait plus que 18minutes.
mais elle ne traitait pas le cas des doubles espaces...

et ici roulement de tambours 6minutes pour notre derniere function CLR
grace à vous les gars. et en plus on peut se permettre de rajouter une pour les espaces

Si y'a encore des axes d'amelioration ???
le rajout du compiled n'a rien changé !

je vous la donne...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function RegExpReplaceStringCLR(ByVal Str As SqlString) As SqlString
 
        If Str.IsNull = True Then Return Str
 
        Dim aOctets As Byte() = System.Text.Encoding.GetEncoding(1251).GetBytes(Str.ToString)
        Dim txt As String = System.Text.Encoding.ASCII.GetString(aOctets).ToUpper
        txt = Regex.Replace(txt, "[^A-Z0-9_-]", " ", RegexOptions.Compiled)
        Return Regex.Replace(txt, "[\s]{2,}", " ", RegexOptions.Compiled)
 
    End Function
 
End Class

[Bluedeep]

Si y'a encore des axes d'amelioration ???
le rajout du compiled n'a rien changé !

J'en vois un qui saute aux yeux, même si il est difficile de quantifier l'amélioration à espérer :

ajouter,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

IsDeterministic = true

à l'attribut SqlFunction.
Ainsi, le système sait que la fonction appelée deux fois avec le même paramètre renvoie deux fois le même résultat, et peut faire une légére optim la dessus.

Ajouter aussi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

IsPrecise = true

( si employé sur une colonne calculée permet son indexation).

[rudib]

Hello,

et en T-SQL ?

Code :

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
 
CREATE TABLE dbo.remplacement (char1 char(1), char2 char(1))
GO
 
INSERT INTO dbo.remplacement
SELECT 'é', 'e'
UNION ALL
SELECT 'à', 'a'
UNION ALL
SELECT '@', ''
 
CREATE TABLE dbo.test (texte varchar(100))
 
INSERT INTO dbo.test
SELECT 'éèazedRftréàRed'
GO
 
DECLARE @texte varchar(100)
 
DECLARE	cur CURSOR 
FOR SELECT texte FROM dbo.test
FOR UPDATE of texte
 
OPEN cur
FETCH NEXT FROM cur INTO @texte
 
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
 
		UPDATE dbo.remplacement
		SET @texte = REPLACE(@texte, char1, char2)
 
		UPDATE dbo.test
		SET texte = @texte
		WHERE CURRENT OF cur
	END
	FETCH NEXT FROM cur INTO @texte
END
 
CLOSE cur
DEALLOCATE cur
GO
 
SELECT *
FROM dbo.test

[Bluedeep]

Hello,

et en T-SQL ?

Es tu certain que cette solution sera plus performante ?

[rudib]

Es tu certain que cette solution sera plus performante ?

Certain jamais avant d'essayer, mais j'en ai l'irrésistible présomption.

[mikedavem]

J'en vois un qui saute aux yeux, même si il est difficile de quantifier l'amélioration à espérer :

ajouter,
Code :
IsDeterministic = true

Je ne pense pas que ce paramètre sert à une quelconque optimisation mais permet simplement de qualifier la fonction pour la création des colonnes calculées indexées ou vues indexés me semble t'il ....

Par contre si une littérature stipule ce fait, je suis preneur ...

++

[SQLpro]

Allez en une seule requête et sans curseur !!!!

1) les conditions du test :

Code :

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
CREATE TABLE dbo.remplacement (char1 char(1) COLLATE French_CS_AS, 
                               char2 char(1) COLLATE French_CS_AS)
GO
 
CREATE TABLE T_NUM (NUM INT PRIMARY KEY);
GO
 
INSERT INTO T_NUM
SELECT COALESCE(MAX(NUM), 0) + 1 FROM T_NUM;
GO 1000
 
 
INSERT INTO dbo.remplacement
SELECT 'é', 'e'
UNION ALL
SELECT 'à', 'a'
UNION ALL
SELECT 'è', 'e'
UNION ALL
SELECT 'À', 'a'
UNION ALL
SELECT 'ö', 'o'
UNION ALL
SELECT '@', ''
 
CREATE TABLE dbo.test (id INT IDENTITY PRIMARY KEY, texte varchar(100) COLLATE French_CS_AS)
GO
 
INSERT INTO dbo.test
SELECT 'éèazedRftréàRed'
UNION ALL
SELECT 'edRfÀtrééèöazàRed'
GO

2) la requête :
-- la requête qui fait tout :

Code :

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
WITH 
T AS
(
SELECT ID, NUM, SUBSTRING(texte, NUM, 1) COLLATE French_CS_AS AS LETTRE, 
       COALESCE(char2, SUBSTRING(texte, NUM, 1)) AS car
FROM   dbo.test
       INNER JOIN T_NUM  
             ON NUM <= LEN(texte)
       LEFT OUTER JOIN dbo.remplacement
             ON SUBSTRING(texte, NUM, 1) = char1 COLLATE French_CS_AS    
),
TR AS
(
SELECT ID, NUM, CAST(car AS VARCHAR(max)) AS texte_corrige
FROM   T
WHERE  NUM = 1
UNION ALL
SELECT TR.ID, T.NUM, TR.texte_corrige + T.car 
FROM   T
       INNER JOIN TR
             ON T.NUM = TR.NUM + 1
               AND T.ID = TR.ID),
TF AS
(               
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NUM DESC) AS N
FROM   TR
)
SELECT TF.ID, texte, texte_corrige
FROM   TF
       INNER JOIN dbo.test AS T
             ON TF.id = T.id
WHERE  N = 1

3) le résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
ID          texte                       texte_corrige
----------- --------------------------- -------------------------
1           éèazedRftréàRed             eeazedRftreaRed
2           edRfÀtrééèöazàRed           edRfatreeeoazaRed

Et si vous voulez faire une mise à jour, remplacez le dernier SELECT par un UPDATE !

A +