Utilisation de Having dans une sous-requête

**ultimus** · 16/05/2014, 21h08

Bonjour à tous,

Voila, j'ai développé une appli web qui à partir d'une interface réalisée avec Ext.js permet depuis des scripts php de générer du code SQL qui est passé sur une base mysql.Une sorte de ms-query web ou de Cognos Impromptu "light".
L'un des utilisateurs a créé une requête qui génère le code suivant :

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
SELECT DISTINCT SNV2_ADM.NUM_EXT_CPT AS  "TMDext-gen1509", SNV2_ADM.ETAT_CPT AS  "TMDext-gen1517", SNV2_ADM.CODE_CAT AS  "TMDext-gen1525", WATT2.NUM_AFFAIRE AS  "TMDext-gen1548", WATT2.CODE_CIRCUIT AS "TMDext-gen1879", WATT2.LIBELL_CIRCUIT AS  "TMDext-gen1887", WATT2.ACTEUR_ETAT AS  "TMDext-gen3282", WATT2.ROLE_ETAT AS  "TMDext-gen3290"
FROM TAMARA217_DATAS.SNV2_ADM SNV2_ADM
INNER JOIN TAMARA217_DATAS.WATT2 ON TAMARA217_DATAS.WATT2.NUM_EXT_CPT = TAMARA217_DATAS.SNV2_ADM.NUM_EXT_CPT
WHERE (TAMARA217_DATAS.SNV2_ADM.NUM_EXT_CPT IN (
	SELECT DISTINCT SNV2_ADM.NUM_EXT_CPT
		FROM TAMARA217_DATAS.WATT2 WATT2
		INNER JOIN TAMARA217_DATAS.SNV2_ADM ON TAMARA217_DATAS.SNV2_ADM.NUM_EXT_CPT = TAMARA217_DATAS.WATT2.NUM_EXT_CPT
		WHERE (TAMARA217_DATAS.WATT2.DATE_FIN_ETAT =  '0000-00-00')
		AND (TAMARA217_DATAS.WATT2.ETAT_AFFAIRE =  'En cours')
		AND (TAMARA217_DATAS.WATT2.DATE_CLOTURE_AFFAIRE =  '0000-00-00')
		AND (TAMARA217_DATAS.SNV2_ADM.CODE_GROUPE2 >  '2199')
		AND (TAMARA217_DATAS.SNV2_ADM.CODE_GROUPE2 <  '2299')
		GROUP BY 1 
		HAVING COUNT( DISTINCT TAMARA217_DATAS.WATT2.NUM_AFFAIRE ) >  '2')
	)
	AND (
		TAMARA217_DATAS.WATT2.ETAT_AFFAIRE =  'En cours'
	)

La sous-requête en elle même tourne très rapidement, mais intégrée dans la requête principale c'est inutilisable.
Si je supprime GROUP BY et HAVING, ça fonctionne bien.
D'après ce que je sais, la syntaxe est autorisée.
Est-ce qu'il faut oublier ce genre de chose, revoir la syntaxe, y-a-t-il une solution pour optimiser le code ?

**karlogod** · 18/05/2014, 00h27

la première chose que je me demande, c'est comment le moteur de la base de moteur c'est qui est qui.
l'utilisation d'alias me semble nécessaire.

j'ai aussi supprimé le distinct présent dans la sous-requete car il est inutile à cause du group by.

voila ce que cela donne :

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
 
SELECT DISTINCT A.NUM_EXT_CPT AS  "TMDext-gen1509", A.ETAT_CPT AS  "TMDext-gen1517", A.CODE_CAT AS  "TMDext-gen1525", B.NUM_AFFAIRE AS  "TMDext-gen1548", B.CODE_CIRCUIT AS "TMDext-gen1879", B.LIBELL_CIRCUIT AS  "TMDext-gen1887", B.ACTEUR_ETAT AS  "TMDext-gen3282", B.ROLE_ETAT AS  "TMDext-gen3290"
FROM TAMARA217_DATAS.SNV2_ADM AS A
INNER JOIN TAMARA217_DATAS.WATT2 AS B ON B.NUM_EXT_CPT = A.NUM_EXT_CPT
WHERE (A.NUM_EXT_CPT IN (
	SELECT D.NUM_EXT_CPT
		FROM TAMARA217_DATAS.WATT2 AS C
		INNER JOIN TAMARA217_DATAS.SNV2_ADM AS D ON D.NUM_EXT_CPT = C.NUM_EXT_CPT
		WHERE (C.DATE_FIN_ETAT =  '0000-00-00')
		AND (C.ETAT_AFFAIRE =  'En cours')
		AND (C.DATE_CLOTURE_AFFAIRE =  '0000-00-00')
		AND (D.CODE_GROUPE2 >  '2199')
		AND (D.CODE_GROUPE2 <  '2299')
		GROUP BY 1 
		HAVING COUNT( DISTINCT C.NUM_AFFAIRE ) >  '2')
	)
	AND (
		TAMARA217_DATAS.WATT2.ETAT_AFFAIRE =  'En cours'
	)

après avoir le plan d’exécution de la requete + la structure des tables peut être untile pour vous aider.

**ultimus** · 18/05/2014, 17h40

Merci,

Effectivement il faudrait que je change la génération du code SQL pour utiliser des alias.
J'ai testé la requête modifiée, malheureusement le temps de traitement est encore très/trop long.
Voici les plans d'exécution de la requête que j'ai intégré à la discussion et la votre.
Ma requête : Nom : originale.jpg
Affichages : 303
Taille : 167,8 Ko

Nom : originale.jpg
Affichages : 303
Taille : 167,8 Ko

La votre:

Nom : modifiée.jpg
Affichages : 284
Taille : 162,6 Ko

Ainsi quel la structure des tables.

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
CREATE TABLE IF NOT EXISTS `SNV2_ADM` (
  `NUM_PERS` int(11) NOT NULL COMMENT 'Numéro de personne',
  `TYPE_PERS` varchar(2) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Type de personne',
  `FORM_JUR` int(11) NOT NULL COMMENT 'Code forme juridique de la personne',
  `NUM_EXT_CPT` varchar(18) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Numéro de compte externe',
  `ETAT_CPT` int(11) NOT NULL COMMENT 'Etat du compte',
  `MOTIF_ETAT_CPT` varchar(2) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Motif de l''état du compte',
  `CODE_CAT` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Code catégorie',
  `DATE_ASSU` date NOT NULL COMMENT 'Date assujetissement',
  `DATE_IMMA` date NOT NULL COMMENT 'Date d''immatriculation',
  `DATE_EFF_CPT` date NOT NULL COMMENT 'Date d''effet de l''état du compte',
  `DATE_NAISS_PERS_PHY` date NOT NULL COMMENT 'Date de naissance (personnes physiques)',
  `CODE_TYPE_PERS` char(3) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Code type de personnel',
  `CODE_ECHEANCE` varchar(2) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Code échéance',
  `CODE_GROUPE1` int(11) NOT NULL COMMENT '1er groupe de travail',
  `CODE_GROUPE2` int(11) NOT NULL COMMENT '2eme groupe de travail',
  `CODE_GROUPE3` int(11) NOT NULL COMMENT '3eme groupe de travail',
  `CODE_GROUPE4` int(11) NOT NULL COMMENT '4eme groupe de travail',
  `CODE_GROUPE5` int(11) NOT NULL COMMENT '5eme groupe de travail',
  `CODE_GROUPE6` int(11) NOT NULL COMMENT '6eme groupe de travail',
  `CODE_GROUPE7` int(11) NOT NULL COMMENT '7eme groupe de travail',
  `NUM_TEL` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Numéro de téléphone',
  `NUM_FAX` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Numéro de FAX',
  `QUA_PERS` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Qualité ou titre de la personne',
  `NOM_PERS1` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Dénomination 1ere ligne',
  `NOM_PERS2` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Dénomination 2eme ligne',
  `PRENOM_PERS` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Prénom de la personne',
  `COMP_ADR_RISQUE` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Complément d''adresse du risque',
  `NUM_VOIE_RISQUE` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Numéro de voie du risque',
  `LETTRE_VOIE_RISQUE` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Lette du numéro de voie du risque',
  `NATURE_VOIE_RISQUE` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Nature de la voie du risque',
  `NOM_VOIE_RISQUE` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Nom de la voie du risque',
  `COMMUNE_RISQUE` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Commune du risque',
  `CP_RISQUE` varchar(5) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Code postal du risque',
  `BUR_DISP_RISQUE` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Bureau distributreur du risque',
  `CODE_COM_INSEE_RISQUE` varchar(5) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Code commune INSEE du risque',
  `COMP_ADR_CORR` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Complément d''adresse de correspondance',
  `NUM_VOIE_CORR` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Numéro de voie de correspondance',
  `LETTRE_VOIE_CORR` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Lettre numéro de voie de correspondance',
  `NATURE_VOIE_CORR` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Nature voie de correspondance',
  `NOM_VOIE_CORR` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Nom voie de correspondance',
  `COMMUNE_CORR` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Commune de correspondance',
  `CP_CORR` varchar(5) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Code postal correspondance',
  `BUR_DISP_CORR` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Bureau distributeur correspondance',
  `CODE_COM_INSEE_CORR` varchar(5) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Code INSEE correspondance',
  `TM_DATA_MAJ` date NOT NULL,
  UNIQUE KEY `NUM_EXT_CPT` (`NUM_EXT_CPT`),
  KEY `NUM_PERS` (`NUM_PERS`),
  KEY `TYPE_PERS` (`TYPE_PERS`),
  KEY `ETAT_CPT` (`ETAT_CPT`),
  KEY `CODE_CAT` (`CODE_CAT`),
  KEY `DATE_ASSU` (`DATE_ASSU`),
  KEY `DATE_IMMA` (`DATE_IMMA`),
  KEY `DATE_EFF_CPT` (`DATE_EFF_CPT`),
  KEY `CODE_GROUPE1` (`CODE_GROUPE1`),
  KEY `CODE_GROUPE2` (`CODE_GROUPE2`),
  KEY `CODE_GROUPE3` (`CODE_GROUPE3`),
  KEY `CODE_GROUPE4` (`CODE_GROUPE4`),
  KEY `CODE_GROUPE5` (`CODE_GROUPE5`),
  KEY `CODE_GROUPE7` (`CODE_GROUPE7`),
  KEY `CODE_TELEM` (`CODE_TELEM`),
  KEY `NUM_TEL` (`NUM_TEL`),
  KEY `NUM_FAX` (`NUM_FAX`),
  KEY `CODE_COM_INSEE_CORR` (`CODE_COM_INSEE_CORR`),
  KEY `CP_CORR` (`CP_CORR`),
  KEY `COMMUNE_CORR` (`COMMUNE_CORR`),
  KEY `NUM_VOIE_CORR` (`NUM_VOIE_CORR`),
  KEY `CODE_COM_INSEE_RISQUE` (`CODE_COM_INSEE_RISQUE`),
  KEY `TM_DATA_MAJ` (`TM_DATA_MAJ`),
  KEY `MOTIF_ETAT_CPT` (`MOTIF_ETAT_CPT`),
  KEY `DATE_NAISS_PERS_PHY` (`DATE_NAISS_PERS_PHY`),
  KEY `CODE_TYPE_PERS` (`CODE_TYPE_PERS`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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
CREATE TABLE IF NOT EXISTS `WATT2` (
  `NUM_EXT_CPT` varchar(18) COLLATE utf8_unicode_ci NOT NULL,
  `NUM_AFFAIRE` varchar(15) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Numéro d''affaire',
  `ETAT_AFFAIRE` varchar(15) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Etat de l''affaire',
  `DATE_CREATION_AFFAIRE` datetime NOT NULL COMMENT 'Date de création de l''affaire',
  `DATE_NUMERISATION` datetime NOT NULL COMMENT 'Date de numérisation',
  `DATE_CLOTURE_AFFAIRE` datetime NOT NULL COMMENT 'Date de clôture de l''affaire',
  `CODE_CIRCUIT` varchar(40) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Code circuit WATT 2',
  `LIBELL_CIRCUIT` varchar(80) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Libellé du circuit WATT 2',
  `TYPE_DEMANDE` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Type de demande',
  `LIBELL_TYPE_DEMANDE` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Libellé du type de demande',
  `ACTEUR_ETAT` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Acteur de l''état',
  `IDENT_ROLE` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `ROLE_ETAT` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Rôle de l''état',
  `NOM_ETAT` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Nom de l''état',
  `LIBELL_ETAT` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Libellé de l''état',
  `DATE_DEBUT_ETAT` datetime NOT NULL COMMENT 'Date de début d''état',
  `DATE_FIN_ETAT` datetime NOT NULL COMMENT 'Date de fin d''état',
  `UUID` char(32) COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `NUM_AFFAIRE` (`NUM_AFFAIRE`),
  KEY `NUM_EXT_CPT` (`NUM_EXT_CPT`),
  KEY `CODE_CIRCUIT` (`CODE_CIRCUIT`),
  KEY `ETAT_AFFAIRE` (`ETAT_AFFAIRE`),
  KEY `DATE_CREATION` (`DATE_CREATION_AFFAIRE`),
  KEY `DATE_CLOTURE_AFFAIRE` (`DATE_CLOTURE_AFFAIRE`),
  KEY `TYPE_DEMANDE` (`TYPE_DEMANDE`),
  KEY `ROLE_ETAT` (`ROLE_ETAT`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Dernière chose que j'ai oublié d'indiquer : chaque table contient entre 200 000 et 250 000 enregistrements, avec 14 000 enregistrements pour la table WATT2 ayant la donnée ETAT_AFFAIRE avec comme valeur "en cours".

**Justforfunmedia** · 18/05/2014, 19h52

Avec une jointure sur la sous requête pour éviter le IN qui n'est qu'un ensemble de OR

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 DISTINCT A.NUM_EXT_CPT AS  "TMDext-gen1509", A.ETAT_CPT AS  "TMDext-gen1517", A.CODE_CAT AS  "TMDext-gen1525", B.NUM_AFFAIRE AS  "TMDext-gen1548", B.CODE_CIRCUIT AS "TMDext-gen1879", B.LIBELL_CIRCUIT AS  "TMDext-gen1887", B.ACTEUR_ETAT AS  "TMDext-gen3282", B.ROLE_ETAT AS  "TMDext-gen3290"
FROM test.SNV2_ADM AS A
INNER JOIN TAMARA217_DATAS.WATT2 AS B ON B.NUM_EXT_CPT = A.NUM_EXT_CPT
INNER JOIN (
	SELECT D.NUM_EXT_CPT
		FROM TAMARA217_DATAS.WATT2 AS C
		INNER JOIN TAMARA217_DATAS.SNV2_ADM AS D ON D.NUM_EXT_CPT = C.NUM_EXT_CPT
		WHERE (C.DATE_FIN_ETAT =  '0000-00-00')
		AND (C.ETAT_AFFAIRE =  'En cours')
		AND (C.DATE_CLOTURE_AFFAIRE =  '0000-00-00')
		AND (D.CODE_GROUPE2 >  '2199')
		AND (D.CODE_GROUPE2 <  '2299')
		GROUP BY 1 
		HAVING COUNT( DISTINCT C.NUM_AFFAIRE ) >  '2'
	) AS E ON E.NUM_EXT_CPT = A.NUM_EXT_CPT
WHERE B.ETAT_AFFAIRE =  'En cours';

**ultimus** · 18/05/2014, 20h23

Merci, c'est parfait,
Exécution en 1,71 sec.
Je vais voir comment modifier mon générateur SQL pour qu'il n'utilise plus de IN mais une jointure.
Par extension (si je ne me trompe pas) avec LEFT JOIN je devrais remplacer le NOT IN.

**Justforfunmedia** · 18/05/2014, 21h25

Envoyé par ultimus

Par extension (si je ne me trompe pas) avec LEFT JOIN je devrais remplacer le NOT IN.

Pas seulement il faudra un WHERE WHERE B.ETAT_AFFAIRE = 'En cours' AND E.NUM_EXT_CPT IS NULL
Ou quelque chose qui s'en rapproche.

Pour n'avoir que les row qui ne match pas avec la sous-requete donc le E.NUM_EXT_CPT IS NULL dans la solution fournie.

**ultimus** · 19/05/2014, 00h01

Ok compris,

Pas facile à implémenter dans un moteur qui doit générer du code SQL automatiquement, et donc qui doit être le plus générique possible.
Mais en grattant ça doit être faisable.
Sinon migrer de mysql à mariaBD, je viens de tester, les sous-requêtes sont bien mieux gérées.

Encore merci

Utilisation de Having dans une sous-requête

Langage SQL

Discussions similaires

Partager

Partager