Discussion :

[beberd]

Bonjour tout le monde,

Permettez moi de vous présenter mes meilleurs voeux pour cette année 2007.

Ceci étant fait, je vous présenet mon problème:

Après 2 longues heures de développement j'ai développé une procédure utilisant 2 curseurs sur la même table.
En updatant cette table 15 fois a chaque combinaison des curseurs après 15 tests "if".

Résultat: Sur une table de 13500 lignes = 13 jours de traitement continue (calcul approximatif !!!)

Beaucoup de personnes sur ce forum ne sont pas fan des curseurs et certains m'ont déjà conseillé dans utilisé qu'1.
Mais mon probème est que je sors des données pas à pas à chaque combinaison ligne1boucle1 ligne1boucle2 / ligne1boucle1 ligne2boulce2 .....

13500*13500= 182 250 000 fois 15 tests + update et insert de données.

Je sais que ca parait relativement abstrait de lire ceci, mais si je met le code (172 lignes) j'ai peur de perdre la moité des gens connectés sur ce forum.

Le but de cette procédure est, de comparer charactère par charactère (identique plus place dans la chaine) une chaine VARCHAR(15) et de renvoyer ceux qui sont à 75% identique ( 9 cas identique / 15).

J'ai plusieurs solutions qui me vieinnent en tête mais je ne sais pas laquelle peut être la plus avantageuse.

Première:

Copié ma table client dans 2 tables identiques Comparant et Comparé pour ensuite faire mon jeux de test dessus.
=> 2 curseurs dans 2 boucles chacuns sur une table. Par contre meilleure indéxation des données.

Seconde:

A l'heure actuelle mon jeux de test se fait sur la table clients celle ou s'execute les curseur.
Utilisation d'une table temporaire dans lequel je fais une insertion de ces données de comparaison et non plus un update sur la table actuelle
=> 2 curseurs dans 2 boucles s'executant sur la même table.

Dernière solution:

Création d'index sur les champs de test
=> Solution expérimentée à petite échelle et qui ne permet pas semble t-il de mieux accélérer le résultat de la procédure

Dernière solution:

L'utilisation d'une fonction ressemblance qui comparerait mes champs un à un et ne rendrait que les résultats nécessaire
=> Une fonction sera t-elle plus rapide et surtout comment l'ecrire sans ses curseurs ?

Voilà j'expère avoir été le plus concis possible et surtout que quelqu'un pourra me venir en aide.

Coridalement,

[Antoun]

Première:

Copié ma table client dans 2 tables identiques Comparant et Comparé pour ensuite faire mon jeux de test dessus.
=> 2 curseurs dans 2 boucles chacuns sur une table. Par contre meilleure indéxation des données.

Deux tables identiques ne servent à rien, une seule suffit, que tu compares à elle-même. Plutôt que tes deux curseurs, tu fais un produit cartésien entre deux instances (alias) de ta table.

Seconde:
...=> 2 curseurs dans 2 boucles s'executant sur la même table.

beurk beurk beurk

Dernière solution:
Création d'index sur les champs de test
=> Solution expérimentée à petite échelle et qui ne permet pas semble t-il de mieux accélérer le résultat de la procédure

A petite échelle, un index est inutile, voire même pénalisant. Il faut toujours tester l'intérêt des index en vraie grandeur.

Ceci dit, vus les traitements que tu te proposes de faire, je doute que MySQL puisse s'appuyer sur des index... à tester quand même.

Dernière solution:
L'utilisation d'une fonction ressemblance qui comparerait mes champs un à un et ne rendrait que les résultats nécessaire
=> Une fonction sera t-elle plus rapide et surtout comment l'ecrire sans ses curseurs ?

La fonction ne doit pas comparer tes champs un à un, mais juste deux textes quelconques. Elle exprime ta règle de comparaison.
Par exemple, si je te donne 'azerty' et 'azerti', comment calcules-tu le taux de ressemblance ? et pour 'azerty' et 'azertyuiop' ? ou encore 'azerty' et 'azyrte' ?

En soi, la fonction ne te fais ni gagner ni perdre de temps. Ce qui te fera gagner du temps c'est d'utiliser les méthodes ensemblistes (jointures, produit cartésien) plutôt que des curseurs.

La fonction n'est qu'un outil, avec deux intérêts :
- le premier est purement dans la intellectuelle ; elle permet de séparer le problème de la comparaison de deux textes (traité par la fonction) du problème de la mise en correspondance deux à deux de tous tes textes (traité par la requête).
- le second est instrumental, la fonction doit te permettre de faire une requête ensembliste

[beberd]

Merci pour toutes ces infos

L'idée du produit cartésien me plait bien mais le problème est que sur une table de 13500 enregistrments ca crait une table temporaire ( ou une vue de plus 180 millions d'enregistrement)

C'est pas trop pour mysql?

Il semeblerait que celui-ci bloque (je fais ce test qui est en cours depuis 10h et ca me parait long quand même!!!)

Faut-il que je partitionne la table au préalable ? si oui quel est la façon la plus judicieuse ?

Merci beaucoup

[Antoun]

Commence par me répondre là-dessus :

Par exemple, si je te donne 'azerty' et 'azerti', comment calcules-tu le taux de ressemblance ? et pour 'azerty' et 'azertyuiop' ? ou encore 'azerty' et 'azyrte' ?

[beberd]

Salut antoun et merci pour toutes ces réponses.

Pour comparer azerty avec azerti. Mon idée est de découper azerty et azerti en

Code :

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

1
2
3
ID    N1 N2 N3 N4 N5 N6 R1 R2 R3 R4 R5 R6 TOTAL
id1   a  z  e  r  t  y
id2   a  z  e  r  t  i

Ainsi dans 6 colonnes (N1 à N6 je découpe mon azerty et mon azerti)
Mes colonnes R1->R6 s'implémente dans un jeux de test (1 ou 0). TOTAL est une colonne qui va compter R1->R6

Dans ma batterie de tests, Id1 est valeur de référence (lors de ma première boucle)

Code :

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

1
2
3
Pour l'identifier, R1->R6 prenne la valeur 2.
ID    N1 N2 N3 N4 N5 N6 R1 R2 R3 R4 R5 R6 TOTAL
id1   a  z  e  r  t  y  2  2  2  2  2  2  12

Ensuite je compare (2 eme boucle) les valeurs N1->N6 Id1 avec ls valeurs N1->N6 Id2
J'obtiens:

Code :

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

1
2
3
ID    N1 N2 N3 N4 N5 N6 R1 R2 R3 R4 R5 R6 TOTAL
id1   a  z  e  r  t  y  2  2  2  2  2  2   12
id2   a  z  e  r  t  i  1  1  1  1  1  0   5

Sur 6 caractères 75% représente 4,5 donc 4 caractères minimum identique et à la même place dans la syntaxe.
C'est ca ma règle pour le matchcode

Ainsi j'insert ces données dans une table doublons puis mes je passe au valeurs suivantes.

Merci beaucoup

[Antoun]

on va oublier le processus de calcul et se concentrer sur la règle de calcul. Si j'ai bien compris, tu compares les caractères qui ont la même position, et tu calcules le taux nb carac identiques / nb carac total ?

Donc azerty et azerti sont similaires à 83% (5/6), tandis que azerty zertya sont à 0% ?

Qu'est-ce que ça donne pour des textes de taille différente ? par exemple azerty et azertyuiop ? ou est-ce que la question n'a pas d'intérêt parce que tes matchcode ont toujours la même taille ?

[beberd]

Exactement !!!
Mes matchcodes ont toujours la même taille.

mon matchcode est une concaténation de:

6 caractères centraux RS
2 premier caractères CP
4 caractères centraux de ADR
3 premier caractères de la ville

Si il n'existe pas de 6 car ds la RS je comble avec des espaces.

Le but de ce matchcode est d'aller plus loin que les comparaisons classiques de données: soundex, ville, rs...
Les données étant des données clientes en fonction de la cohérence de ce qui est sortie (doublons) ce fichier est ensuite mis a disposition de notre client qui lui valide si oui ou non il y a doublons ou pas.

Peu importe que ce dernier renvoie des infos incohérentes sous prétexte que la comparaison affiche 75%, car le fichier est doublement valider.
Le problème est qu'aujourd'hui une table client peu aller jusqu'à 70 000 enregistrements (elle croit chaque mois).
D'où mon intérêt pour que mon matchcode se fasse le plus rapidement possible

[Alexandre T]

J'ai lu vos messages. J'apporte juste un complément d'information sur d'autres méthodes existantes. Sachez qu'il existe en effet des méthodoligies de comparaisons de caractères abouties. Celles-ci sont d'ailleurs présentées dans les cours de sqlpro sur ce site : http://sqlpro.developpez.com/

Pour en citer une (pas forcément la meilleure), il y a par exemple les méthodes soundex.

[beberd]

Merci pour l'information je suis justement aller prendre des infos dessus.

Je me suis par contre inspiré du travail fait par la société datalgo sur le dédoublonnage et la mise au propre des données.

C'est pourquoi aujourd'hui j'essaie d'implémenter sous mysql des solutions beaucoup plus poussées et complexes.

Merci beaucoup pour les infos!!!!!

[Antoun]

Voilà donc ta fonction pour des matchcodes taillés à 15 caractères :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP FUNCTION IF EXISTS Simi ;
CREATE FUNCTION Simi(Matchcode1 CHAR(15), Matchcode2 CHAR(15)) RETURNS FLOAT
BEGIN
  DECLARE i TINYINT UNSIGNED DEFAULT 1 ;
  DECLARE resu FLOAT DEFAULT 0 ;
  REPEAT 
    IF SUBSTRING(Matchcode1 FROM i FOR 1) = SUBSTRING(Matchcode2 FROM i FOR 1)
    THEN SET resu = resu + 1 ;
    END IF ;
    SET i = i + 1 ;
    UNTIL i > 15 
    END REPEAT ;
RETURN Resu / 15 ; 
END ;

Si tu es familier avec les expressions booléennes, tu peux aussi remplacer le IF... END IF par cette seule ligne :

Code :

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

    SET resu = resu + (SUBSTRING(Matchcode1 FROM i FOR 1) = SUBSTRING(Matchcode2 FROM i FOR 1) ) ;

Mais cela ne fonctionnerait sans doute pas avec d'autres SGBD qui ont une attitude plus orthodoxe vis-à-vis des conditions booléennes.

Pour utiliser cette fonction, je te propose la requête suivante :

Code :

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

1
2
3
4
5
SELECT T1.ID, T2.ID, T1.matchcode, T2.matchcode
FROM ta_table AS T1
  INNER JOIN ta_table AS T2
    ON Simi(T1.matchcode, T2.matchcode) >= .75
      AND T1.ID <> T2.ID

[Antoun]

Maintenant que j'ai rédigé la fonction... voici la solution 100% ensembliste sans fonction, qui utilise une table Entiers(Num) contenant (au moins) les nombres de 1 à 15.

ça devrait donner qqch comme ça :

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
SELECT T1.ID, T2.ID, T1.matchcode, T2.matchcode
FROM Entiers
  CROSS JOIN ta_table AS T1
  INNER JOIN ta_table AS T2
    ON T1.ID <> T2.ID
WHERE Num BETWEEN 1 AND 15
GROUP BY T1.ID, T2.ID
-- (avec un autre SGBD il faudrait ajouter  T1.matchcode et T2.matchcode)
HAVING 
  SUM(CASE 
    WHEN SUBSTRING(T1.Matchcode FROM Num FOR 1) =    
       SUBSTRING(T2.Matchcode FROM Num FOR 1) 
    THEN 1
    ELSE 0
    END
  ) >= 15 * 3 / 4

[beberd]

Merci !!!
C'est super propre.

Pour la fonction j'avoue que je n'avais pas penser jongler de la sorte avec SUBSTRING.
En plus le fait que ce soit une fonction qui prend 2 paramètre en entrée (matchcode) me permet de faire sauter mes curseurs (le select exécute ainsi sous forme de séquence la fonction simi !!!)
Très ingénieux.

La solution ensembliste et sympathique !!! Mais j'aimerais que tu m'expliques le contenu de la table NUM.
Je pense qu'elle contient une colonne d'entier de 1-> 15 ?

Enfin je te remercie pour tout car j'avoue que l'une comme l'autre de ces solutions vont faciliter grandement mon travail !!!
Merci

[Antoun]

Pour la fonction j'avoue que je n'avais pas penser jongler de la sorte avec SUBSTRING.

Comment pensais-tu donc éclater tes textes en colonnes ?

En plus le fait que ce soit une fonction qui prend 2 paramètre en entrée (matchcode) me permet de faire sauter mes curseurs (le select exécute ainsi sous forme de séquence la fonction simi !!!)
Très ingénieux.

Yes, c'est ce que je développais dans mon post #2 : la fonction, en soi, n'a rien de spécialement rapide ; mais l'intérêt est d'isoler l'algo de comparaison un à un d'un côté, la moulinette ensembliste de l'autre.

La solution ensembliste et sympathique !!! Mais j'aimerais que tu m'expliques le contenu de la table NUM.
Je pense qu'elle contient une colonne d'entier de 1-> 15 ?

Exactement. C'est une table qui sert souvent (générer un calendrier, des tables de caractères, etc.), n'hésite pas à y mettre des nombres au-delà de 15 (d'où le BETWEEN).

Enfin je te remercie pour tout car j'avoue que l'une comme l'autre de ces solutions vont faciliter grandement mon travail !!!
Merci

Je crois qu'au départ tu anticipais 9 jours de traitement... tu nous diras combien de temps ça te prend maintenant !

[beberd]

Salut et merci,

Code :

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

Exactement. C'est une table qui sert souvent (générer un calendrier, des tables de caractères, etc.), n'hésite pas à y mettre des nombres au-delà de 15 (d'où le BETWEEN).

C'est donc une table qui est déjà créée sous mysql ou c'est classiquement que ce nom de table est utiliser pour les calendriers ou autres ?

Pour ce qui est de substring, moi je pensais découper mon champs matchode dans 15 colonnes et non comparer de la sorte !!

Merci beaucoup,

[Antoun]

C'est donc une table qui est déjà créée sous mysql ou c'est classiquement que ce nom de table est utiliser pour les calendriers ou autres ?

Juste un nom usuel (et assez descriptif) pour ce genre de table !

[beberd]

Salut,

la solution ensembliste tourne depuis 17h00 (il est 19h00).
Il semblerait qu'il créait la jointure dans une table temporaire.
J'ai pourtant changer la solution en ajoutant une ligne d'insertion avant le select

Code :

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

1
2
insert into doublons
select id,id,matchocde,matchcode.....

Je pense que c'est normal par contre il semblerait que cette solution soit longuette tout de même.
J'en saurais plus demain quand je reviendrais au boulot.

Bonne soirée

[beberd]

Premier Résultat obtenu:

"Duplicate entry for key1 20456-3245676654"

J'ai relancé la requête sans faire d'insert pour voir ce qui se passe et surtout le temps avant le plantage.
Je pense d'ailleurs que ce dernier est lié au fait que dans la table doublons j'ai cherché a insérer 2 fois le même ID.

Effectivement j'ai fait le test plus tôt dans la journée sur 20 lignes et il me sort comme résultats:

14 15
15 14

il ne tient pas compte que la combinaison existe déjà!!!!
Donc lors de mon insert dans ma table doublons j'ai du le faire planter !!!

[Adjanakis]

Hello,

En modifiant le critère de jointure tu résoudra le problème tout en divisant par 2 les tests effectués

Code :

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

T1.ID < T2.ID

[Antoun]

1) "15-14" n'est pas un doublon de "14-15"

2) si tu veux éviter cette combinaison, utilise ON T1.ID < T2.ID au lieu de ON T1.ID <> T2.ID comme condition de jointure ; ainsi, seule "14-15" apparaîtra (en plus ça divise ton volume par deux)

3) quelles sont les colonnes de ta clé 1 ? id et id ?

4) pour éviter cette erreur (au moins en premier test), le + simple est encore de faire un REPLACE INTO au lieu de INSERT INTO

5) ton id est bien déclarée comme clé primaire ?

[beberd]

1) "15-14" n'est pas un doublon de "14-15"

En l'occurence si car 15=id_cln et 14=id_cln de la table clients.
Donc si il y a une ressemblance > 75% l'un est forcement un doublon de l'autre.
Donc aucun intérêt pour moi d'avoir les 2 "14-15" et "15-14"

2) si tu veux éviter cette combinaison, utilise ON T1.ID < T2.ID au lieu de ON T1.ID <> T2.ID comme condition de jointure ; ainsi, seule "14-15" apparaîtra (en plus ça divise ton volume par deux)

Merci (à adjanakis aussi)

3) quelles sont les colonnes de ta clé 1 ? id et id ?

id_cln de la table clients.

4) pour éviter cette erreur (au moins en premier test), le + simple est encore de faire un REPLACE INTO au lieu de INSERT INTO

Je ne veux pas remplacer dans ma table clients mais créés une table doublons avec quelques infos (id_cln1,id_cn2,matchcode1,matchcode2,rs1,rs2)
Car une fois cette table doublons créées, je n'ai plus qu'à valider si ces doublons sont effectifs.

5) ton id est bien déclarée comme clé primaire ?

Non je l'ai déclaré comme un index mais je n'y ai pas rajoutté de condition de clefs primaires dessus!!!
Est-ce important? Est-ce que cela peux poser problème?

Merci à vous je corrige et je continue mes tests et je vous tiens ensuite au courant sur comment ca fonctionne!!!!