Optimisation de requête [Résolu]

[stos]

Bonjour à tous,
je rencontre un problème d'optimisation d'une de mes requêtes. je souhaite afficher les séjours répartis par communes d'origines des patients pour un établissment et un GHM donné.
Voici les trois tables que j'intérroge dans ma requête :


Voici ma requete trop longue (plus de 30 sec) ainsi que son plan d'éxécution :

Code :

1
2
3
4
5
6
7
8
9
SELECT Code_geo,  nb_sejour 
FROM Sejour s 
	INNER JOIN GHM g ON s.Id_GHM=g.Id_GHM
	INNER JOIN Etablissement e ON e.id_etab=s.id_etab
	INNER JOIN Geo ge ON s.id_geo=ge.id_geo
WHERE 
Code_GHM LIKE '%C%' AND
finess LIKE '690781810' 
GROUP BY Code_geo;

Le problème vient manifestement de la jointure avec la table Geo. En effet, quand j'affiche juste l'id_geo de la table sejour, mon temps d'execution passe sous la seconde

Code :

1
2
3
4
5
6
7
8
SELECT id_geo,  nb_sejour 
FROM Sejour s 
	INNER JOIN GHM g ON s.Id_GHM=g.Id_GHM
	INNER JOIN Etablissement e ON e.id_etab=s.id_etab
WHERE
Code_GHM LIKE '%C%' AND
finess LIKE '690781810' 
GROUP BY Code_geo;

Pour info, voici les show index des tables sejour et geo :
Sejour :

GEO :


Avez-vous des pistes de réflexion?

[SQLpro]

C'est parfaitement normal que ce soit lent, et cela le restera toujours du fait du LIKE '%C%'. En effet le seul moyen de résoudre cette recherche est de faire un scan. Et plus le volume augmentera plus les perf vont chuter dramatiquement.

Ce qui est en cause ici, c'est votre modèle de données qui ne respecte pas les formes normale. En particulier ici la première forme normale est violée !
Lorsque vous ne respectez pas les formes normales au cours de la modélisation, alors les performances sont irrémédiablement catastrophiques un jour ou l'autre !
Dans votre cas, la recherche de '%c%' dans le code GHM (Groupement Hospitalier Medical ?) signifie que cette lettre toute seule est significative ! Dès lors pourquoi l'avez vous noyée dans votre code GHM ??? C'est cela le viol de la 1FN.

Si vous aviez été sur un vrai SGBDR et non pas sur MySQL qui est un ersatz de SGBDR !!! - a lire : http://blog.developpez.com/sqlpro/p9...udre-aux-yeux/) il y aurait eut des moyens de contourner le problème, à l'aide par exemple de colonnes calculées, d'index sur fonction ou encore de vues indexées, mais aucun des ces outils n'est disponible dans ce SGBD pseudo relationnel !

A +

[Maljuna Kris]

Saluton,
En fait, réminiscences de mon DESS Traitement de l'Information Médicale Hsopitalière, les GHM sont des Groupes Homogènes de Malades, cette nomenclature nationale permet de codifier les pathologies afin de fournir des informations statistiques anonymes afférentes à l'activité médicale hospitalière.
Et il vrai que le code est une clé composite et qu'il est maladroit d'avoir repris la nomemclature telle quelle.

[SQLpro]

Oui, ça y est, ça me recolle. D’où la modélisation que j’avais fait pour AGGIR :
http://blog.developpez.com/sqlpro/p9...r-la-grille-2/

Ou pour la CIM :
http://blog.developpez.com/exercices...s-de-la-cim10/

A +

[mydb_server]

Regarde du côté des deux paramètres suivants :

sort_buffer_size et join_buffer_size

Rachid

[stos]

Merci pour vos réponses,
Effectivement, je pense que ma modélisation des "Groupes Homogènes de Malade" (GHM) ne correspond pas aux règles de l'art. J'ai fait ce choix car les utilisateurs vont nécessairement sélectionner un ou plusieurs GHM lors de l'utilisation (donc pas comme dans mon exemple...).
Je vais normaliser cette table pour analyser l'impact sur les performances.

Néanmoins il me semble que le point bloquant est plutôt la jointure avec la table Geo non?

L'utilisation de MySQL n'est pas une donnée que je maitrise; pour ma culture perso, que me conseillerez vous comme SGBD vraiment relationnel?

Rachid, il me semble que join_buffer_size n'est utilisé que si la jointure se fait sans index, ce qui n'est pas la cas ici (cf plan d’exécution).
Je bosse sur sort_buffer_size, mais je comprends pas exactement son fonctionnement et ça n'a pas l'air de changer grands chose.

Note : il manquait le sum(nb_sejour) dans mes requêtes.

[SQLpro]

Envoyé par stos

Merci pour vos réponses,
Effectivement, je pense que ma modélisation des "Groupes Homogènes de Malade" (GHM) ne correspond pas aux règles de l'art. J'ai fait ce choix car les utilisateurs vont nécessairement sélectionner un ou plusieurs GHM lors de l'utilisation (donc pas comme dans mon exemple...).
Je vais normaliser cette table pour analyser l'impact sur les performances.

Ça c'est important !

Néanmoins il me semble que le point bloquant est plutôt la jointure avec la table Geo non?

Difficile à optimiser car MySQL implémente des index très rustiques sur objets géographiques (entre autres limites de MySQL...). A lire :
http://blog.developpez.com/sqlpro/p9...t-sur-l-index/
§ 1.7.5

L'utilisation de MySQL n'est pas une donnée que je maitrise; pour ma culture perso, que me conseillerez vous comme SGBD vraiment relationnel?

PostGreSQL est pas mal, SQL Server est un poil plus pointu notamment pour la partie SIG...

A +

[stos]

Bonjour à tous
Merci de vos conseils et de vos pistes de réflexion.
Ma requête de sélection est finalement assez bien optimisée car elle passe sous la barre des deux secondes sur ma base de prod.
J'ai fonctionné en deux étapes :
1) Normalisation de la table GHM sur les recommandation de SQLPro. Cela ne m'a pas fait gagner grand chose, mais j'y gagnerais sans doute plus tard...

2) Ecriture d'une requête dans le select pour "remonter" la jointure qui posais problème et me faisais perdre du temps :

Code :

1
2
3
4
5
6
7
SELECT (SELECT g.Code_Geo FROM Geo g WHERE g.Id_Geo=s.Id_Geo) AS code_cp, sum(nb_sejour) 
FROM Sejour s 
	INNER JOIN GHM g ON s.Id_GHM=g.Id_GHM
	INNER JOIN Etablissement e ON e.id_etab=s.id_etab  
WHERE 
Code_GHM LIKE "28Z07Z" AND finess LIKE '690781810'
GROUP BY code_cp;

Bonne journée.