Discussion :

[Christophe P.]

Bonjour,

le requête suivante prend plus de 2 heures, ce qui est un "peu" trop long. Table1 contient 110 000 lignes et table2 40 000.
Il y a un index sur chacun des champs (NomCourt, Type et CodeEmploye) ainsi qu'un index sur les trois réunis. Je ne vois pas comment améliorer les performances (autrement qu'en améliorant le serveur).
La requête liste les doublons.

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT a.CodeEmploye, a.Nom, a.Debut, a.Fin
FROM table1 as a INNER JOIN table2 ON a.ID = table2.ID
WHERE a.NomCourt In 
	(SELECT NomCourt 
	FROM table1 As Tmp 
	GROUP BY Tmp.NomCourt, Tmp.Type, 
	Tmp.CodeEmploye HAVING Count(*) > 1  And 
	Tmp.Type = a.Type And 
	Tmp.CodeEmploye = a.CodeEmploye) AND 
(table2.Fin Is Null Or table2.Fin > Now());

[StringBuilder]

Filtrer dans le HAVING est une très mauvaise idée, d'autant que je doute que ça marche sur autre-chose que MySQL puisque ce sont des colonnes ne faisant pas partie du group by.

Essayez plutôt ça :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT a.CodeEmploye, a.Nom, a.Debut, a.Fin
FROM table1 as a INNER JOIN table2 ON a.ID = table2.ID
WHERE a.NomCourt In 
(
        SELECT NomCourt 
	FROM table1 As Tmp 
        WHERE Tmp.Type = a.Type And 
	Tmp.CodeEmploye = a.CodeEmploye
	GROUP BY Tmp.NomCourt, Tmp.Type, 
	Tmp.CodeEmploye HAVING Count(*) > 1
)
AND (table2.Fin Is Null Or table2.Fin > Now());

[escartefigue]

Pour pouvoir répondre précisément, il faudrait que vous communiquiez le DDL des tables et index

Quelle est la clef unique de la table1 ? d'après votre requête, le code employé n'est pas unique dans table1 ? ni lie trio nomcourt+code employé + type ?

[Christophe P.]

@StringBuilder
Cette requête provient d'Access. J'ai essayé en sortant les filtres de la clause HAVING, mais ça ne fonctionne pas mieux (j'ai arrêté la requête après 10 min.).

Si je comprends bien, l'explain plan indique un problème avec table2 alors qu'il y a des index sur les deux champs.

Code :

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

1
2
3
4
5
6
7
+----+--------------------+-----------+-------+---------------------------------------------------------------------------+-----------------------------------+---------+-----------+-------+--------------------------+
| id | select_type        | table     | type  | possible_keys                                                             | key                               | key_len | ref       | rows  | Extra                    |
+----+--------------------+-----------+-------+---------------------------------------------------------------------------+-----------------------------------+---------+-----------+-------+--------------------------+
|  1 | PRIMARY            | table2    | ALL   | IDX_ORE_ID,IDX_ORE_FIN                                                    | NULL                              | NULL    | NULL      | 41269 | Using where              |
|  1 | PRIMARY            | table1    | ref   | IDX_ORA_NOMCOURT,IDX_ORA_FIN,IDX_ORA_ID,IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE | IDX_ORA_ID                        | 23      | table2.ID |    10 | Using where              |
|  2 | DEPENDENT SUBQUERY | Tmp       | index | IDX_ORA_CODEEMPLOYE,IDX_ORA_TYPE                                          | IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE | 309     | NULL      |     1 | Using where; Using index |
+----+--------------------+-----------+-------+---------------------------------------------------------------------------+-----------------------------------+---------+-----------+-------+--------------------------+

Les DDL :

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
create table table1(
	ID int,
	NOMCOURT varchar(100),
	NOM varchar(100),
	BRISDEGLACE varchar(100),
	DEBUT datetime default 0,
	FIN datetime default 0,
	TYPE varchar(100),
	ID varchar(20),
	CODEEMPLOYE varchar(100),
	ORA_ID int not null auto_increment,
	primary key(ORA_ID),
	KEY IDX_ORA_ID (ID),
	KEY IDX_ORA_NOMCOURT (NOMCOURT),
	KEY IDX_ORA_FIN (FIN),
	KEY IDX_ORA_TYPE (TYPE),
	KEY IDX_ORA_ID (ID),
	KEY IDX_ORA_CODEEMPLOYE (CODEEMPLOYE),
	KEY IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE (NOMCOURT, TYPE, CODEEMPLOYE)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
create table table2(
	ID varchar(20),
	CODEEMPLOYE varchar(25),
	NOM varchar(50),
	PRENOM varchar(50),
	STATUT varchar(50),
	TITRE VARCHAR(3),
	DEBUT datetime default 0,
	FIN datetime default 0,
	ORE_ID int not null auto_increment,
	primary key(ORE_ID),
	KEY IDX_ORE_ID (ID),
	KEY IDX_ORE_CODEEMPLOYE (CODEEMPLOYE),
	KEY IDX_ORE_FIN (FIN)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

[escartefigue]

il y a des aberrations dans le DDL

D'une part vous faites une jointure entre un varchar(20) et un integer , ce qui compromet l'usage d'index

Il est donc tout à fait normal que votre jointure rame !

d'autre part vous avez des redondances d'index et des index probablement inutiles

De plus, pour trouver les doubles vous n'avez nullement besoin de faire un groupage du coup, un where exists suffit en vérifant que l'id est différent !

[escartefigue]

Je suppose donc que l'id qui vous permet de faire la jointure devrait être de type integer dans les 2 tables, une fois que le DDL sera corrigé dans Table2, vous pourrez faire comme ci-dessous :

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
 
SELECT a.CodeEmploye
      ,a.Nom 
      ,a.Debut
      ,a.Fin
FROM       table1 as a 
INNER JOIN table2 
   ON a.ID = table2.ID
WHERE exists
     (SELECT 1
      FROM  table1 As Tmp 
      WHERE Tmp.NomCourt    = a.NomCourt
        and Tmp.Type        = a.Type 
	and Tmp.CodeEmploye = a.CodeEmploye
        and Tmp.ORA_ID     <> a.ORA_ID)
  AND (table2.Fin Is Null Or table2.Fin > Now())
;

[Christophe P.]

La jointure se fait bien sur deux varchar(20), c'est moi qui est fait une erreur en renommant les champs dans le ddl.

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
create table table1(
	AID int,
	NOMCOURT varchar(100),
	NOM varchar(100),
	BRISDEGLACE varchar(100),
	DEBUT datetime default 0,
	FIN datetime default 0,
	TYPE varchar(100),
	ID varchar(20),
	CODEEMPLOYE varchar(100),
	ORA_ID int not null auto_increment,
	primary key(ORA_ID),
	KEY IDX_ORA_ID (ID),
	KEY IDX_ORA_NOMCOURT (NOMCOURT),
	KEY IDX_ORA_FIN (FIN),
	KEY IDX_ORA_TYPE (TYPE),
	KEY IDX_ORA_ID (ID),
	KEY IDX_ORA_CODEEMPLOYE (CODEEMPLOYE),
	KEY IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE (NOMCOURT, TYPE, CODEEMPLOYE)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
create table table2(
	ID varchar(20),
	CODEEMPLOYE varchar(25),
	NOM varchar(50),
	PRENOM varchar(50),
	STATUT varchar(50),
	TITRE VARCHAR(3),
	DEBUT datetime default 0,
	FIN datetime default 0,
	ORE_ID int not null auto_increment,
	primary key(ORE_ID),
	KEY IDX_ORE_ID (ID),
	KEY IDX_ORE_CODEEMPLOYE (CODEEMPLOYE),
	KEY IDX_ORE_FIN (FIN)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Je teste la requête proposée.

[escartefigue]

Si vous avez les habilitations pour le faire, profitez en pour
- supprimer l'index IDX_ORA_NOMCOURT (NOMCOURT) redondant avec IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE (NOMCOURT, TYPE, CODEEMPLOYE)
- vérifier combien il y a de valeurs distinctes possibles pour TYPE, s'il n'existe que 5 ou 6 valeurs possibles, alors vous pouvez supprimer l'index IDX_ORA_TYPE (TYPE) car inutile
- vérifier s'il y a un intérêt fonctionnel à rechercher sur la date de fin sans autre critère, ce qui est peu probable, dans le cas contraire, l'index IDX_ORA_FIN(FIN) peut être supprimé.
- pareil pour l'index IDX_ORE_FIN de la table2
- remplacer TITRE VARCHAR(3) par TITRE CHAR(3)
- mettre des valeurs par défaut qui soient des dates/heures dans vos colonnes date-time, je suis surpris que zéro soit accepté !

[Christophe P.]

Merci pour vos conseils. La requête s'effectue en 6 minutes (c'est un temps acceptable pour un script).