Vous n'êtes pas identifié(e).
Pages : 1
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.idComme 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.idY a t'il une autre méthode de faire ce test?
Ai je oublié quelque chose?
Merci,
Hors ligne
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
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
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
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
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
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
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
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
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 
Guillaume.
Hors ligne
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
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