Discussion :

[Crillick]

Bonjour à tous,

Je suis actuellement en train d'essayer de construire une vue qui reporte les différences entre deux tables de structure identique. Voici la structure en question :

# Colonne
1 type
2 ip_address
3 port_num
4 domain_name
5 mid_string

Ma vue doit contenir les différences trouvées pour chaque couple de clefs et chaque champs. S'il n'y a pas de différence la vue n'affiche rien. Voici la structure de la vue que j'ai réalisée :

# Colonne
1 table // nom de la table comparée
2 key // clé de la ligne comparée
3 field // champ comparé
4 reference // valeur dans la première table
5 work // valeur dans al deuxième table

Voici le code que j'ai écrit pour créer ma vue et pour que la détection des différences soit automatique :

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
 
CREATE VIEW differences AS
SELECT "h248_vmg5_vmg_ip_information" AS "table",
CONCAT(`t1`.`ip_address`,"-",`t1`.`port_num`) AS "key",
"type" AS "field",
`t1`.`type` AS "reference",
`t2`.`type` AS "work"
FROM `gpy_7510_R4_ref`.`h248_vmg5_vmg_ip_information` `t1` LEFT JOIN `gpy_7510_R4_tra`.`h248_vmg5_vmg_ip_information` `t2`
ON `t1`.`ip_address`=`t2`.`ip_address`
AND `t1`.`port_num`=`t2`.`port_num`
HAVING (
`reference`<>`work`
OR `reference` IS NULL
OR `work` IS NULL
)
UNION
SELECT "h248_vmg5_vmg_ip_information" AS "table",
CONCAT(`t1`.`ip_address`,"-",`t1`.`port_num`) AS "key",
"domain_name" AS "field",
`t1`.`domain_name` AS "reference",
`t2`.`domain_name` AS "work"
FROM `gpy_7510_R4_ref`.`h248_vmg5_vmg_ip_information` `t1` LEFT JOIN `gpy_7510_R4_tra`.`h248_vmg5_vmg_ip_information` `t2`
ON `t1`.`ip_address`=`t2`.`ip_address`
AND `t1`.`port_num`=`t2`.`port_num`
HAVING (
`reference`<>`work`
OR `reference` IS NULL
OR `work` IS NULL
)
UNION
SELECT "h248_vmg5_vmg_ip_information" AS "table",
CONCAT(`t1`.`ip_address`,"-",`t1`.`port_num`) AS "key",
"mid_string" AS "field",
`t1`.`mid_string` AS "reference",
`t2`.`mid_string` AS "work"
FROM `gpy_7510_R4_ref`.`h248_vmg5_vmg_ip_information` `t1` LEFT JOIN `gpy_7510_R4_tra`.`h248_vmg5_vmg_ip_information` `t2`
ON `t1`.`ip_address`=`t2`.`ip_address`
AND `t1`.`port_num`=`t2`.`port_num`
HAVING (
`reference`<>`work`
OR `reference` IS NULL
OR `work` IS NULL
)

Ce code fonctionne mais il est très lourd et ne fonctionne que pour une structure de table spécifique. Afin de gérer les différences sur d'autres structures de table, j'ai copié ce code et je l'ai adapté à d'autres structures(en changeant le nom des tables et des champs dans le code). Et grâce au mot clef UNION j'ai pu concaténer le tout afin que ma vue affiche les différences entre de 2 bases de même structure.

Le problème est que le traitement est du coup abominablement long rien que pour afficher la vue à cause de la longueur du code de la vue (celui du dessus en 350 fois plus long car 350 tables dans la base)

Alors quelqu'un pourrait-il m'aider à réduire et simplifier mon code afin d'obtenir des délais d'interrogation convenables ? Je pense que ce code est loin d'être optimisé...

Merci.

[ericd69]

salut,

pourquoi ces having?

ce serait pas plus simple de faire un inner join entre les 2 tables avec comme conditions pour la 1ère série:

Code sql :

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

1
2
3
ON `t1`.`ip_address`=`t2`.`ip_address`
AND `t1`.`port_num`=`t2`.`port_num`
AND `t1`.`type`<>`t2`.`type`

l'inner join ne renvoie que les lignes qui correspondent à droite et à gauche de la jointure à la condition de jointure... contriarement au left join qui te renvoie au moins les ligne de la table de gauche...

tes conditions de non nullité sont elles vraiment nécessaires car elle vont éliminer l'utilisation des index possibles...

[Crillick]

Ma condition de nullité me permettait de récupérer les lignes supplémentaires qu'il y avait dans une table ou l'autre. Ta méthode fonctionne bien mais elle ne ramène pas une ligne qui serait dans une table et pas l'autre. Et puis il n'y a pas une méthode pour traiter tous les champs dans un même SELECT plutôt que d'utiliser l'UNION ?

Voilà ce que ça donne :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT "h248_vmg5_vmg_ip_information" AS "table",
CONCAT(`t1`.`ip_address`,"-",`t1`.`port_num`) AS "key",
"type" AS "field",
`t1`.`type` AS "reference",
`t2`.`type` AS "work"
FROM `gpy_7510_R4_ref`.`h248_vmg5_vmg_ip_information` `t1` INNER JOIN `gpy_7510_R4_tra`.`h248_vmg5_vmg_ip_information` `t2`
ON `t1`.`ip_address`=`t2`.`ip_address`
AND `t1`.`port_num`=`t2`.`port_num`
AND `t1`.`type`<>`t2`.`type`

[Maljuna Kris]

Saluton,
Je ne vois qu'une solution, mettre les tables dans deux jointures externes inversées et mettre ses jointures en UNION (simple pas ALL).
La première jointure externe retournera les lignes ayant un type différent ou n'ayant pas de correspondance dans la deuxième table, la deuxième jointure externe retournera les mêmes lignes ayant un type différent, mais en inversant les colonnes du SELECT elles seront dédoublonnées par l'UNION, et les lignes de la seconde table sans correspondance dans la première.

[Crillick]

Heu... pas tout compris

Peux tu m'écrire une trame de cette requête ? Et penses tu que niveau performance elle sera mieux que la première solution que j'ai proposée ?

[Maljuna Kris]

Heu... pas tout compris

Peux tu m'écrire une trame de cette requête ?

Euh désolé mais là je ne suis pas chez moi mais chez ma fille où je garde mes petites filles. Mais l'écriture de ces deux requêtes de jointures externes inversées me semble assez aisée avec un éditeur correct dont je ne dispose pas pour le moment.
En outre, j'ai oublié mes lunettes et je n 'ai pas de clavier en braille ( de toutes façons je ne connais pas le braille)
Avant de s"attacher aux performances, essayons d'obtenir la requête qui retourne les résultats attendus.

[Crillick]

Je ne vois qu'une solution, mettre les tables dans deux jointures externes inversées et mettre ses jointures en UNION (simple pas ALL).
La première jointure externe retournera les lignes ayant un type différent ou n'ayant pas de correspondance dans la deuxième table, la deuxième jointure externe retournera les mêmes lignes ayant un type différent, mais en inversant les colonnes du SELECT elles seront dédoublonnées par l'UNION, et les lignes de la seconde table sans correspondance dans la première.

Tu penses à quelque chose 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
17
18
19
20
 
SELECT "h248_vmg5_vmg_ip_information" AS "table",
CONCAT(`t1`.`ip_address`,"-",`t1`.`port_num`) AS "key",
"type" AS "field",
`t1`.`type` AS "reference",
`t2`.`type` AS "work"
FROM `gpy_7510_R4_ref`.`h248_vmg5_vmg_ip_information` `t1` LEFT OUTER JOIN `gpy_7510_R4_tra`.`h248_vmg5_vmg_ip_information` `t2`
ON `t1`.`ip_address`=`t2`.`ip_address`
AND `t1`.`port_num`=`t2`.`port_num`
AND `t1`.`type`<>`t2`.`type`
UNION
SELECT "h248_vmg5_vmg_ip_information" AS "table",
CONCAT(`t1`.`ip_address`,"-",`t1`.`port_num`) AS "key",
"type" AS "field",
`t2`.`type` AS "reference",
`t1`.`type` AS "work"
FROM `gpy_7510_R4_ref`.`h248_vmg5_vmg_ip_information` `t1` LEFT OUTER JOIN `gpy_7510_R4_tra`.`h248_vmg5_vmg_ip_information` `t2`
ON `t1`.`ip_address`=`t2`.`ip_address`
AND `t1`.`port_num`=`t2`.`port_num`
AND `t1`.`type`<>`t2`.`type`

J'obtiens tout en double sauf que les colonnes sont inversées à chaque fois ...

[ericd69]

perso j'aurais fait alors l'inner comme je t'ai proposé et tu rajoutes les conditions de nullité alternée... peutêtre rajouter un distinct au select...

[Crillick]

Ouais mais le INNER JOIN ne retourne pas la ligne si elle n'est pas dans les deux tables. (présence dans une seule table -> pas de correspondance donc pas retournée).

[ericd69]

tu fais une union avec un select avec les or au pire

tu y gagne beaucoup en temps d'exécution avec le inner?

[Maljuna Kris]

En fait, je pensais à cette requête

Code MySQL :

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
SELECT CONCAT(`t1`.`ip_address`,"-",`t1`.`port_num`) AS "key",
`t1`.`type` AS "reference",
`t2`.`type` AS "work"
FROM `gpy_7510_R4_ref`.`h248_vmg5_vmg_ip_information` `t1`
LEFT OUTER JOIN `gpy_7510_R4_tra`.`h248_vmg5_vmg_ip_information` `t2`
ON `t1`.`ip_address`=`t2`.`ip_address`
AND `t1`.`port_num`=`t2`.`port_num`
AND `t1`.`type`<>`t2`.`type`
UNION
SELECT CONCAT(`t1`.`ip_address`,"-",`t1`.`port_num`),
`t2`.`type`,
`t1`.`type`
FROM `gpy_7510_R4_tra`.`h248_vmg5_vmg_ip_information` `t1` 
LEFT OUTER JOIN `gpy_7510_R4_ref`.`h248_vmg5_vmg_ip_information` `t2`
ON `t1`.`ip_address`=`t2`.`ip_address`
AND `t1`.`port_num`=`t2`.`port_num`
AND `t1`.`type`<>`t2`.`type`

Quand je disais inversée, c'est pour que les doublons soient bien supprimés par le UNION.
Tu n'as pas besoin du nom des tables quand les deux types seront présents c'est forcément ceux de la première requête, sinon un des deux types sera à NULL.

[Crillick]

Merci pour ces réponses mais les requêtes proposées ne sont pas assez rapides en termes d’exécution. Faire 2 jointures prend trop de temps. Celle que j'ai mise en début de poste est actuellement la plus rapide. Si vous avez mieux en termes de perfs, je suis preneur. L'idéal serait une requête qui teste tous les champs de la table d'un coup et non pas champs par champ (quitte à changer la structure de la table retournée).

[CinePhil]

Puisque tu veux généraliser sans connaître a priori le nom des tables et leur structure, il te faut construire une procédure qui interroge information_schema pour récupérer ces informations et avec une boucle sur toutes les tables de la BDD.