Vous n'êtes pas identifié(e).

Pages : 1

#1 13/09/2011 15:41:58

Gold.Strike
Membre

Recherche de doublons sur des champs texte

Bonjour,

Dans ma base de données, j'ai une table CONTACT qui contient des informations nominatives et personnelles (nom, prénom, sexe, date de naissance, etc...).
Je me suis rendu qu'il y avait déja plusieurs "doublons" dans cette base.

Afin de pouvoir les identifier rapidement, j'ai donc exécuté cette requête qui marche parfaitement bien :

SELECT T1.id
FROM contacts T1, contacts T2
WHERE LOWER(T1.nom) = LOWER(T2.nom)
AND LOWER(T1.prenom) = LOWER(T2.prenom)
AND T1.id <> T2.id

Comme il y d'autres cas, avec des accents ou des guillemets, j'ai créé une fonction intermédiaire censée gérer ces cas :

-- Function: fct_format_string(text)
-- DROP FUNCTION fct_format_string(text);

CREATE OR REPLACE FUNCTION fct_format_string(stringtoformat text)
  RETURNS text AS
$BODY$DECLARE formatedString text;
BEGIN
	SELECT LOWER(stringToFormat) INTO formatedString ;
                --SELECT replace(formatedString,'&','') INTO formatedString ;
                --SELECT replace(formatedString,'$','') INTO formatedString ;
	--SELECT replace(formatedString,'*','') INTO formatedString ;
	--SELECT replace(formatedString,'@','') INTO formatedString ;
                --SELECT replace(formatedString,'^','') INTO formatedString ;
	--SELECT replace(formatedString,'#','') INTO formatedString ;
	SELECT replace(formatedString,'æ','ae') INTO formatedString ;
	SELECT replace(formatedString,'â','a') INTO formatedString ;
	SELECT replace(formatedString,'ä','a') INTO formatedString ;
	SELECT replace(formatedString,'à','a') INTO formatedString ;
	SELECT replace(formatedString,'é','e') INTO formatedString ;
	SELECT replace(formatedString,'è','e') INTO formatedString ;
	SELECT replace(formatedString,'ê','e') INTO formatedString ;
	SELECT replace(formatedString,'ë','e') INTO formatedString ;
	SELECT replace(formatedString,'î','i') INTO formatedString ;
	SELECT replace(formatedString,'ï','i') INTO formatedString ;
	SELECT replace(formatedString,'œ','oe') INTO formatedString ;
	SELECT replace(formatedString,'ö','o') INTO formatedString ;
	SELECT replace(formatedString,'ô','o') INTO formatedString ;
	SELECT replace(formatedString,'û','u') INTO formatedString ;
	SELECT replace(formatedString,'ü','u') INTO formatedString ;
	SELECT replace(formatedString,'-','') INTO formatedString ;
	SELECT replace(formatedString,' ','') INTO formatedString ;
	--SELECT replace(formatedString,'_','') INTO formatedString ;
	--SELECT replace(formatedString,'''','') INTO formatedString ;
	--SELECT replace(formatedString,''"','') INTO formatedString ;

RETURN formatedString;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION fct_format_string(text) OWNER TO postgres;

Malheureusement, quand j'exécute la requête précédente avec cette fonction, Postgre mouline énormément et ne m'a toujours pas retourné de résultat après près d'une heure d'exécution...

WHERE C.id in (
SELECT T1.id
FROM contacts T1, contacts T2
WHERE fct_format_string(T1.nom) = fct_format_string(T2.nom)
AND fct_format_string(T1.prenom) = fct_format_string(T2.prenom)
AND T1.id <> T2.id

Y a t'il une autre méthode de faire ce test?
Ai je oublié quelque chose?

Merci,

Hors ligne

#2 13/09/2011 22:24:58

gleu
Administrateur

Re : Recherche de doublons sur des champs texte

Une première chose à faire serait de remplacer tous les replace par un translate, du style translate(formatedString,'âäàéèêë','aaaeeee''). Si la base est en latin1 ou latin9, il faudrait peut-être tester utf-8.

Mais de toute façon, avec le produit cartésien que vous faites et l'impossibilité d'utiliser des index, vous êtes sûr d'avoir un gros temps d'exécution pour une grosse table.

Guillaume.

Hors ligne

#3 16/09/2011 16:30:52

dim
Membre

Re : Recherche de doublons sur des champs texte

S'il s'agit comme je le pense de mettre au point un premier niveau de filtre dans la requête SQL, alors je conseillerais d'utiliser pg_trgm.  Bon il se trouve que sa version en 9.1 est particulièrement adaptée, mais même dans les versions précédentes cela devrait être d'une grande aide.

  http://docs.postgresqlfr.org/9.1/pgtrgm.html

Note: gleu, il est assez pénible de naviguer vers cette page depuis http://docs.postgresqlfr.org/9.1/contrib.html, ne devrait-il pas y avoir un menu des sections dans l'annexe à cette URL?

Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Hors ligne

#4 16/09/2011 17:10:36

gleu
Administrateur

Re : Recherche de doublons sur des champs texte

Il faudrait que je trouve le bon filtre XSLT pour ça. Et comme générer la doc met un temps fou, ça motive peu pour le faire.

Guillaume.

Hors ligne

#5 04/11/2011 19:07:48

Gold.Strike
Membre

Re : Recherche de doublons sur des champs texte

Je me permets de relancer cette discussion, car la fonction que j'ai cité, est utilisé à différents endroits, notamment pour synchroniser des données entre une base MAITRE et des bases CLIENTES. Je me sers donc de la fonction pour vérifier la présence de données dans différentes tables...

-- Function: fct_format_string(text)

-- DROP FUNCTION fct_format_string(text);

CREATE OR REPLACE FUNCTION fct_format_string(stringtoformat text)
  RETURNS text AS
$BODY$DECLARE formatedString text;
BEGIN
    SELECT LOWER(stringToFormat) INTO formatedString ;
        SELECT replace(formatedString,'&','') INTO formatedString ;
    SELECT replace(formatedString,'$','') INTO formatedString ;
    SELECT replace(formatedString,'*','') INTO formatedString ;
    SELECT replace(formatedString,'@','') INTO formatedString ;
        SELECT replace(formatedString,'^','') INTO formatedString ;
    SELECT replace(formatedString,'#','') INTO formatedString ;
    SELECT replace(formatedString,'æ','ae') INTO formatedString ;
    SELECT replace(formatedString,'â','a') INTO formatedString ;
    SELECT replace(formatedString,'ä','a') INTO formatedString ;
    SELECT replace(formatedString,'à','a') INTO formatedString ;
    SELECT replace(formatedString,'é','e') INTO formatedString ;
    SELECT replace(formatedString,'è','e') INTO formatedString ;
    SELECT replace(formatedString,'ê','e') INTO formatedString ;
    SELECT replace(formatedString,'ë','e') INTO formatedString ;
    SELECT replace(formatedString,'î','i') INTO formatedString ;
    SELECT replace(formatedString,'ï','i') INTO formatedString ;
    SELECT replace(formatedString,'œ','oe') INTO formatedString ;
    SELECT replace(formatedString,'ö','o') INTO formatedString ;
    SELECT replace(formatedString,'ô','o') INTO formatedString ;
    SELECT replace(formatedString,'û','u') INTO formatedString ;
    SELECT replace(formatedString,'ü','u') INTO formatedString ;
    SELECT replace(formatedString,'-','') INTO formatedString ;
    SELECT replace(formatedString,' ','') INTO formatedString ;
    SELECT replace(formatedString,'_','') INTO formatedString ;
    SELECT replace(formatedString,'''','') INTO formatedString ;
    --SELECT replace(formatedString,''"','') INTO formatedString ;

RETURN formatedString;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION fct_format_string(text) OWNER TO postgres;

Le fait d'utiliser "translate" apporte t'il un gain significatif?
Du coup existe t'il une fonction Postgre qui fasse la même chose? Je n'ai pas très bien compris l'utilité ni l'application de "pg_trm"...

Merci,

Hors ligne

#6 04/11/2011 21:02:10

gleu
Administrateur

Re : Recherche de doublons sur des champs texte

Le fait d'utiliser "translate" apporte t'il un gain significatif?

Le plus simple est certainement de faire le test vous-même non ? moi, en exécutant 100000 fois une requête, j'obtiens 9,2 secondes avec votre fonction qui utilise un tas de replace. Et 2.2 secondes avec la mienne qui utilise un translate suivi de deux ou trois replace.

existe t'il une fonction Postgre qui fasse la même chose?

La même chose que quoi ?

Je n'ai pas très bien compris l'utilité ni l'application de "pg_trm"

pg_trgm permet de trouver des noms ressemblant (par exemple une lettre oubliée ou deux lettres inversées... ce genre de problème). Ça pourrait être utile pour trouver des doublons mais ça ne répond pas directement à votre problème.

Guillaume.

Hors ligne

#7 07/11/2011 19:23:14

Gold.Strike
Membre

Re : Recherche de doublons sur des champs texte

Alors j'ai effectivement constaté une nette amélioration des performances en utilisant des "translate". Pour une recherche classique, je passe de 4 secondes à 1.5 secondes.

Je voudrais cependant savoir si je peux encore optimiser d'avantage ma fonction :

-- Function: fct_format_string(text)

-- DROP FUNCTION fct_format_string(text);

CREATE OR REPLACE FUNCTION fct_format_string2(stringtoformat text)
  RETURNS text AS
$BODY$DECLARE formatedString text;
BEGIN
    SELECT LOWER(stringToFormat) INTO formatedString ;
   
--     bloc non remplacé
    SELECT replace(formatedString,'æ','ae') INTO formatedString ;
    SELECT replace(formatedString,'œ','oe') INTO formatedString ;

--     bloc "accents"       
--     SELECT replace(formatedString,'â','a') INTO formatedString ;
--     SELECT replace(formatedString,'ä','a') INTO formatedString ;
--     SELECT replace(formatedString,'à','a') INTO formatedString ;   
--     SELECT replace(formatedString,'é','e') INTO formatedString ;
--     SELECT replace(formatedString,'è','e') INTO formatedString ;
--     SELECT replace(formatedString,'ê','e') INTO formatedString ;
--     SELECT replace(formatedString,'ë','e') INTO formatedString ;
--     SELECT replace(formatedString,'î','i') INTO formatedString ;
--     SELECT replace(formatedString,'ï','i') INTO formatedString ;
--     SELECT replace(formatedString,'ö','o') INTO formatedString ;
--     SELECT replace(formatedString,'ô','o') INTO formatedString ;
--     SELECT replace(formatedString,'û','u') INTO formatedString ;
--     SELECT replace(formatedString,'ü','u') INTO formatedString ;
    SELECT translate(formatedString, 'âäàéèêëîïöôûü', 'aaaeeeeiioouu') INTO formatedString ;

--     bloc "caractère spéciaux"   
--     SELECT replace(formatedString,'&','') INTO formatedString ;
--     SELECT replace(formatedString,'$','') INTO formatedString ;
--     SELECT replace(formatedString,'*','') INTO formatedString ;
--     SELECT replace(formatedString,'@','') INTO formatedString ;
--              SELECT replace(formatedString,'^','') INTO formatedString ;
--     SELECT replace(formatedString,'#','') INTO formatedString ;
--     SELECT replace(formatedString,'-','') INTO formatedString ;
--     SELECT replace(formatedString,' ','') INTO formatedString ;
--     SELECT replace(formatedString,'_','') INTO formatedString ;
    SELECT translate(formatedString, '&$*@^#- _', '') INTO formatedString ;

--     gestion du caractère simple quote (')
    SELECT replace(formatedString,'''','') INTO formatedString ;
   
    --SELECT replace(formatedString,''"','') INTO formatedString ;

RETURN formatedString;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION fct_format_string(text) OWNER TO postgres;

Enfin, j'ai un problème avec le caractère simple quote ('). En effet, je n'arrive pas à trouver la méthode permettant de le prendre en compte dans le bloc des caractères spéciaux. Ai je oublié quelque chose?

Hors ligne

#8 07/11/2011 21:01:20

gleu
Administrateur

Re : Recherche de doublons sur des champs texte

Le plus simple serait de tout transformer en une seule requête et du coup de transformer la procédure PL/pgsql en procédure SQL.

Guillaume.

Hors ligne

#9 08/11/2011 11:12:08

Gold.Strike
Membre

Re : Recherche de doublons sur des champs texte

gleu a écrit :

Le plus simple serait de tout transformer en une seule requête et du coup de transformer la procédure PL/pgsql en procédure SQL.

Je ne comprends pas ce que tu veux dire. Est ce ma fonction que je dois transformer en une seule requête?
Les procédure SQL sont elles gérées de la même façon que les procédures PLSQL?

Hors ligne

#10 08/11/2011 21:31:12

gleu
Administrateur

Re : Recherche de doublons sur des champs texte

Il est possible de créer une procédure stockée en SQL. C'est généralement bien plus rapide qu'en PL/pgsql. L'idée, c'est que plutôt que de faire 15 SELECT pour ne récupérer qu'une info, on n'en fait qu'un seul. Du coup, il agrege tous les appels. Donc ça donnerait qqc du genre :

CREATE OR REPLACE FUNCTION fct_format_string3(text)
  RETURNS text AS
$BODY$
    SELECT replace(translate(translate(replace(replace(LOWER($1),'æ','ae'),'œ','oe'), 'âäàéèêëîïöôûü', 'aaaeeeeiioouu'), '&$*@^#- _', ''),'''','');
$BODY$
  LANGUAGE 'sql';

Pour rappel, test d'exécution 100000 fois :
* la première procédure avec tous les SELECT : 9s
* la procédure avec le translate : 2s
* la procédure SQL : 54 ms

Je vous accorde que c'est moins lisible. Mais c'est clairement plus performant smile

Guillaume.

Hors ligne

#11 09/11/2011 17:25:00

Gold.Strike
Membre

Re : Recherche de doublons sur des champs texte

Merci à toi, effectivement, en terme de perf ca n'a rien à voire : pour la recherche des doublons, je passe de 30 minutes avec la première version de la fonction, à quelques secondes avec la dernière!

Mais en clair, qu'est ce qui distingue une procédure stockée "SQL" d'une procédure sotckée "PLSQL"?
La gestion des paramètres utilisées?
La structure de la fonction est la même, donc j'ai du mal à comprendre de tels écarts de performance.

Hors ligne

#12 09/11/2011 19:03:34

gleu
Administrateur

Re : Recherche de doublons sur des champs texte

Tout d'abord, ce n'est pas du PLSQL (car c'est le langage d'Oracle). C'est du PL/pgsql.

Les écarts de performance s'expliquent par le fait qu'il n'est pas nécessaire d'avoir un interpréteur spécial pour exécuter ce code. Dans le cas d'une fonction SQL, c'est du SQL bête et méchant. Dans le cas du PL/pgsql, il faut instancier des variables, interpréter les requêtes, ajouter la valeur à la variable, retourner le contenu de la variable. Tout ce travail prend du temps.

Guillaume.

Hors ligne

Pages : 1

Pied de page des forums

Propulsé par FluxBB