[SGBD/SQL] Optimisation de requête

Optimisation de requête [SGBD/SQL] - SQL/NoSQL - Programmation

Marsh Posté le 25-06-2004 à 15:04:01    

Bonjour,
 
j'ai fait ce topic dans le but de recueillir le maximum d'informations concernant les différentes optimisations que vous avez faites sur des requêtes.
Il est donc là dans le but de discuter des différentes solutions que vous trouvez à un problème.
J'essaierai de regrouper les différentes remarques qui seront postées sur le forum.
 
Merci de préciser le SGBD dont vous parlez au début de votre post pour éviter toute confusion.
Les liens sont également la bienvenue.
 
Merci  :jap:

Reply

Marsh Posté le 25-06-2004 à 15:04:01   

Reply

Marsh Posté le 25-06-2004 à 15:05:32    

Arjuna a écrit :

Un SGBD-R permet de gérer les relations entre les éléments, et de les "enforce", c'est à dire d'en assurer l'intégrité.
 
Ainsi, on note deux systèmes de verroux de base et un personnalisable.
 
Les verroux de base.
Il s'agit des clés primaires et étrangères.
Pour ce qui est de la clé primaire, lorsqu'un champ (ou un tuple de champs) est spécifié comme clé primaire, alors le moteur de la base de données va interdire que ce tuple prennent plusieurs fois la même valeur.
Et niveau clé étrangère, cette fois le SGBD va s'assurer avant l'insertion de valeur dans ce tuple, que ces valeurs existent déjà dans la table de référence.
 
Ces deux systèmes de base permettent de s'assurer par exemple que deux clients n'auront pas le même numéro de client, ou qu'on ne pourra pas associer un produit qui n'existe pas à une commande.
 
Les verroux personnalisables.
Il s'agit des triggers. Plus que des verroux, un trigger permet de faire à peut près n'importe quelle action sur une action spécifique, sur un objet spécifique. Ainsi, on pourra par exemple se servir d'un trigger pour mettre à jour le prix total d'une commande lorsque la quantité d'un produit change. Etant des calculs évolués, cela permet au SGBD de se comporter exactement comme vous l'entendez.
 
Ces méthodes permettent de mettre en application les méthodes d'analyse tels que Merise, et d'assurer, au sein du stockage des données, l'intégralité et la cohérence de ces dernières.
 
 
Sur le papier, tout ça c'est très bien... Mais... Oui, il y a un mais...
 
Regardons ce qu'il se passe sur une clé primaire.
-> Lorsqu'on ajoute une valeur, le SGBD va en premier rechercher la présence de cette valeur dans la table, puis l'insérer si elle n'existe pas. PUIS, étant donné qu'une clé primaire est systématiquement associée à un index unique, elle va vérifier l'existance de cet élément dans l'index unique, et l'ajouter si elle n'existe pas (ce qui est forcément le cas si on est allé jusque là).
 
Ca fait pas mal de trucs... Voyons voir ce que ça donne si on supprime cette contrainte, et qu'on ne garde que l'index unique.
-> Recherche de la valeur dans l'index. Si elle n'existe pas, insertion de la donnée, mise à jour de l'index, sinon, erreur.
On a fait 2 traîtements de moins.
 
=> Il ne faut pas utiliser de clé primaire sur une table. Un index unique suffit, et offre le même résultat en un temps plus rapide.
Deplus, les clé alternatives n'existant que rarement dans les SGBD, elles sont habituellement traîtées de cette façon, alors qu'il n'y a pas de raison de les différencier.
 
Maintenant, penchons-nous sur la clé étrangère.
-> Lorsque j'insère une donnée dans la table "fille", je regarde si la donnée liée existe dans la table mère. Si elle existe, j'insère ma ligne, et si elle n'existe pas, je fait une erreur. Ceci peut prendre pas mal de temps si la table mère contient beaucoup de données, ou que le tuple servant de lien est gros.
Lorsque je supprime une donnée de la table "mère", je regarde si des données de la table fille y font référence. Si oui, alors je plante, sinon je supprime la ligne. Ceci est encore plus lent, puisque très généralement il y a plus de filles que de mères. Sans compter le fait que du côté mère, ce champ est forcément unique, tandis que côté fille, il ne l'est pas.
 
Quand j'ai un programme qui me permet de remplir un formulaire ou les données de la table mère sont listées dans une dropdown list, quel est l'intérêt d'aller vérifier qu'elle existent dans la table mère au moment de l'insertion ? Aucun, puisque ces données existes forcément !
Et dans un traîtement de suppression d'une donnée de référence, pour éviter de planter si des données existent dans la table fille, on va de toute façon nettoyer cette dernière en premier. Ainsi, au moment du delete sur la table mère, on est certains qu'il ne peut pas y avoir de filles, vu qu'on vient de toutes les effacer. A nouveau, aucun intérêt.
 
=> Les clé étrangères ne servent donc à rien, et c'est au programme de s'assurer de l'intégrité des données lors de ces accès à la base. Il vaut donc mieu éviter de les utiliser, afin d'économiser les traîtements inutiles.
 
A noter deplus le problème du CASCADE CONSTRAINTS. Cette fabuleuse commande permet de supprimer une mère et toutes ses filles en une ligne.
Elle a aussi la formidable capacité à vider la moitiée des tables de la base en une seconde si on écrit mal la requête... Dans ce cas, le segment de rollback étant sous-dimensionné, ça plante, et on ne peut plus faire rollback. La catastrophe. Sans clé étrangère, aucun risque, le CASCADE CONSTRAINTS ne retrouvant pas de cheminement entre les table, ne pourra faire aucun dégat.
 
-> Les triggers. Un trigger s'éxécute sur différents évèments et différentes actions sur différents objets. Un trigger est généralement utilisé sur une table (mais ça peut aussi bien être sur une vue pour les SGBD récents), avant ou après la modification de la table, sur une insertion, une suppression ou/et une mise à jour.
L'éxécution du trigger sera systématique lorsqu'un tel traîtement aura lieu sur son l'objet qu'il surveille. Ainsi, si un trigger s'occupe de mettre à jour le montant d'une commande en fonction des produits qui y sont rattachés, la moindre modification d'un libellé d'un produit dans la liste va déclencher le calcul, même si aucune quantité ni prix n'a été modifié.
 
-> Un trigger fonctionne dans sa propre sous-transaction
-> Un trigger peut tout à fait déclencher d'autres triggers
-> A savoir qu'une sous-transaction fonctionne comme de la récusrivité : c'est la transaction principale qui encaisse tout. On voit rapidement que pour certains trigger un rollback segment fault risque d'arriver, avec toutes les erreurs et incohérences que cela peut engendrer.
-> Un trigger, étant écrit à la main, ne peut pas bénéficier des optimisation des PK et FK (qui sont en réalité des triggers automatiques), et se contenteront d'utiliser du code SQL et dérivés (PL/SQL ou T-SQL pour ne citer qu'eux), ce qui risque rapidement de prendre du temps.
 
=> La plus part des traîtements par trigger peuvent être évités en faisant attention à ce qu'on fait au niveau du programme qui utilise la base de données. Le calcul d'un total d'une commande notamment, n'a rien à faire dans la base, il a sa place dans le programme, d'autant plus que c'est plus évident de modifier un programme pour y rajouter des frais de port ou la TVA que dans un trigger...
 
En bref, les triggers sont à éviter autant que possible. Les seuls qui sont vraiment acceptables, sont ceux qui vont s'occuper de générer un identifiant (pour les bases qui n'ont pas de fonction native).
 
On crééra alors un trigger "on before insert" qui s'occupera d'aller récupérer la valeur d'une séquence par exemple. Sorti de ça, les tests comme exclusion de champs, surtout lorsque le trigger se lève sur un traîtement fréquent, et lorsque ses traîtements sont longs, doivent impérativement déportés dans l'application.
 
 
Voilà. C'était juste parceque j'avais rien à faire et que j'ai pas de question à poser, et y'a pas de réponse auxquelles répondre, donc j'étalle ma science, ça peu toujours servir si un jour une personne se pose la question  

Reply

Marsh Posté le 25-06-2004 à 15:18:55    

MagicBuzz a écrit il y longtps :

Grossomodo :
-> Faire les filtres les filtres et les jointures regroupés table par table.
-> Toujours faire les jointures avant les filtres (sauf pour la première, qui n'est pas fille d'une jointure)
-> Pour les jointures, réutiliser les tables qui contiennent le moins de données possibles, même si au niveau du modèle ça ne correspond pas aux relations qu'on a fait.
 
Exemple :
 
PROPRIETAIRE
----
UID
NOM
PRENOM
PERMIS_ID
 
 
ADRESSE
------
AID
NUMERO
RUE
VILLE
CP
PAYS
TYPE
PROPRIETAIRE_ID
 
VOITURE
-------
IMMATRICULATION
PROPRIETAIRE_ID
 
Maintenant, le problème est :
 
Retrouver l'adresse de résidence principaledu conducteur de la voiture immatriculée 1234AB56 (histoire de lui envoyer un PV par exemple)
 
Tu commences par le filtre le plus restrictif, donc ici, l'imatriculation, puisque c'est un identifiant (une ligne retournée)
 
where v.immatriculation = '1234AB56'
 
Ensuite, tu ne vas pas t'amuser à lier les utilisateurs aux adresses, sinon tu vas te retrouver avec un nombre incalculable de lignes - autant qu'il y a d'adresse dans la base -, qui devront être filtrées au moment de la jointure (perte de temps).  
 
Donc tu commences par retrouver le conducteur.
 
and p.uid = v.proprietaire_id
 
Puis, tu vas chercher les adresses du conducteur (NON, ne fait pas le filtre sur le type d'adresse AVANT, sinon tu vas filtrer les adresses habitation de TOUS les propriétaires. Donc à nouveau, trop de lignes. Tu vas donc chercher la liste des adresses du conducteur, qui représentent beaucoup moins de lignes.
 
and a.PROPRIETAIRE_ID = u.UID
 
Et enfin (et seulement maintenant) tu vas filtrer le type d'adresse.
 
and a.type = 'PRINCIPALE'
 
Ensuite, dans le FROM, il faut mettre les tables dans l'ordre inverse de leur position dans la requête.
 
Truc à bien garder en tête aussi :
 
and a.PROPRIETAIRE_ID = u.UID
 
est différent de  
 
and u.UID = a.PROPRIETAIRE_ID
 
En effet, le second va recherche tous les UID qui sont présents dans les adresses, et ensuite va filter pour retrouver les UID qui connerspondent aux précédents critères. Alors que le premier va rechercher les adresses qui correspondent uniquement aux prorpiétaires déjà filtrés.
 
Le requête est donc :
 
SELECT p.NOM, p.PRENOM, a.NUMERO, a.RUE, a.CP, a.VILLE, a.PAYS
FROM adresse a, proprietaire p, voiture v
WHERE v.immatriculation = '1234AB56'
AND p.uid = v.proprietaire_id
AND a.PROPRIETAIRE_ID = u.UID
AND a.type = 'PRINCIPALE'
 
Et non pas un truc monstrueux genre :
 
SELECT p.NOM, p.PRENOM, a.NUMERO, a.RUE, a.CP, a.VILLE, a.PAYS
FROM proprietaire p, voiture v, adresse a
WHERE a.PROPRIETAIRE_ID = v.PROPRIETAIRE_ID
AND a.PROPRIETAIRE = p.UID
AND a.TYPE = 'PRINCIPALE'
and v.IMMATRICULATION = '1234AB56'
 
La seconde est tout ce qu'il y a de plus immonde  
... mais elle fonctionne aussi (c'est bien ça le problème...)

Reply

Marsh Posté le 25-06-2004 à 15:49:29    

Citation :


SELECT p.NOM, p.PRENOM, a.NUMERO, a.RUE, a.CP, a.VILLE, a.PAYS  
FROM adresse a, proprietaire p, voiture v  
WHERE v.immatriculation = '1234AB56'  
AND p.uid = v.proprietaire_id  
AND a.PROPRIETAIRE_ID = u.UID  
AND a.type = 'PRINCIPALE'  
   
Et non pas un truc monstrueux genre :  
   
SELECT p.NOM, p.PRENOM, a.NUMERO, a.RUE, a.CP, a.VILLE, a.PAYS  
FROM proprietaire p, voiture v, adresse a  
WHERE a.PROPRIETAIRE_ID = v.PROPRIETAIRE_ID  
AND a.PROPRIETAIRE = p.UID  
AND a.TYPE = 'PRINCIPALE'  
and v.IMMATRICULATION = '1234AB56'  
   
La seconde est tout ce qu'il y a de plus immonde    
... mais elle fonctionne aussi (c'est bien ça le problème...)  


:??: je croyait qu'il y avait des optimisations avec les stats de la base et que les requêtes ci-dessus  revenaient au même... Me trompais-je ?
 
edit : ortho


Message édité par pascal_ le 25-06-2004 à 16:49:44
Reply

Marsh Posté le 25-06-2004 à 16:32:39    

Les stats sont bien, mais sur une base très solicitée, il y a un temps plus ou moins important avant qu'elles soient recomputées.
 
Dans la base d'un ERP par exemple, qui va se taper quelques milliers de connections concurrentes pendant 10 heures d'affilée par jour, les stats sont carrément mise à jour a la main par batch au heures creuses (ça consomme énormément le régénération des stats).
 
De ce fait, si les stats permettent en effet en temps normal de corriger les lacunes de la première requête, ils faut systématiquement écrire ses requêtes comme si elles n'existaient pas.

Reply

Marsh Posté le 26-06-2004 à 09:32:10    

les stats sur les tables ne sont pas nécessairement regardées ... Sous Oracle par exemple, il y a 2 modes de fonctionnements d'une base : choose (optimisation en regardant les stats) / rule (optimiseur de requête se basant sur les index, etc.).
 
Dans les 2 cas, il me semble que l'ordre des éléments dans les clauses where importe peu, l'optimiseur va de lui-même prendre le chemin le plus rapide ... mais il fait de son mieux, et de temps en temps, il faut l'aider :D
 
D'où les 'hints' en Oracle, ces petites indications qu'on fournit pour forcer l'interprétation d'une requête dans un sens précis.
 
Exemple :
select /*+ index(code code_index) */ *
from code
where id_code = 1;
 
Avec le hint '/*+ index(code code_index) */' on force Oracle à prendre l'index code_index de la table code. (il l'aurait pris par défaut de toute façon, soit s'il est en mode rule car l'index est là, soit en mode choose si les stats sont à jour).
 
Il y a énormément de hints Oracle, pas tous documentés d'ailleurs, qui permettent d'avoir l'explain plan qu'on veut ...
On peut par exemple forcer l'interprétation de la where clause dans l'ordre dans lequel elle est écrite, ou encore dire si on veut faire des 'nested loops' ou 'hash join', etc.

Reply

Marsh Posté le 26-06-2004 à 18:06:55    

drapal :o

Reply

Marsh Posté le 27-06-2004 à 01:39:43    

Beegee > Tu as l'air de bien maîtriser le sujet des hints pour oracle.
 
A propos du forçage des index, j'ai découvert le hint que tu cites il y a quelques jours (j'ai une requête qui dure des heures, et je n'arrive pas à en réduire le temps).
 
J'ai donc trouvé la syntaxe /* +index([table] [index]) */
 
Seulement, dans mon cas, c'est légèrement plus compliqué...
En effet, j'utilise 5 fois la même table (aliasée) dans la même requête, en utilisant des index différents.
 
Est-ce que dans le hint, on peut remplacer le nom de la table par son alias ? Si non, comment puis-je faire ?

Reply

Marsh Posté le 27-06-2004 à 09:18:49    

oui, tu peux utiliser les alias ;)
 
mais y a peut-être aussi moyen d'améliorer ta requête, parce que utiliser 5 fois la même table dans une requête c'est un peu trop ... :)

Reply

Marsh Posté le 27-06-2004 à 20:21:39    

Ben j'ai pas d'autre choix...
 
La table contient un calendrier des jours fériés. Et dans ma requête je fais 5 différences de dates sur 5 périodes différentes... :sweat:

Reply

Marsh Posté le 27-06-2004 à 20:21:39   

Reply

Marsh Posté le 27-06-2004 à 20:23:25    

Ben si tu peux, poste la requête ...
 
tu es vraiment obligé de tout renvoyer en une requête ? tu peux pas faire plusieurs petites requêtes ? Vu comme tu le présentes, je comprends pas trop l'intérêt de grouper le résultat en un requête ...

Reply

Marsh Posté le 27-06-2004 à 20:34:57    

Parceque la requête renvoie des informations sur la performance des fournisseurs de l'entreprise ou je suis.
 
Et les 67 000 lignes générées (infos sur les 6 derniers mois) sont ensuite traîtées dans Excel afin de faire une chiée de calculs et générer divers rapports et graphs.
 
Sur le poste des personnes qui utiliseront la macro, il n'y a pas Oracle Client, et il est impossible qu'ils le mettent (contraintes logicielles au niveau du groupe)
 
Ainsi, mla macro passe par une requête HTTP sur une page intranet, qui renvoie les données au format CSV. Ce système étant extrêment lent, je doit donc récupérer un maximum d'infos (pour le moment toutes) puis faire mes traîtements de regroupement et filtres à la main dans ma macro, plutôt que par exemple générer 67000 * 5 requêtes HTTP afin de retrouver les infos de chaque ligne utilisée...
Ca va faire deux semaines que je retourne le problème dans tous les sens, et je commence à sècher complètement :sweat:
 
Là on est en train d'essayer de stocker les données de la requête dans une table temporaire, mise à jour toutes les semaines... Seulement la requête actuelle semble durer plus de 12 heures, et il faut donc qu'on trouve une solution pour arriver à faire marcher cette solution de rattrappage. En fait, on ne comprend même pas pourquoi c'est aussi lent. Tous les index nécessaires sont créés et opérationnel, et le serveur encaisse des requêtes bien plus complexes et volumineuse sans problème... J'ai mis un DBA sur le coup pour voir s'il trouve une explication en monitorant de plus près le SGBD (rien à tirer du plan d'éxécution, il est clean, il ne reste plus aucun range scan ni rien...). Mais vu que c'est le closing actuellement (Q2) toutes les équipes systèmes sont full-time pour la maintenance et garantir qu'aucun problème ne puisse arriver en cette période critique de l'année... J'espère que d'ici 1 ou 2 semaines j'aurai une réponse de sa part, et qui m'éclairera :sweat:

Reply

Marsh Posté le 27-06-2004 à 20:37:19    

Elle a pas mal changé depuis, mais elle reste globalement la même (avec les bugs en moins, j'ai corrigé quelques inepties depuis).
http://forum.hardware.fr/hardwaref [...] 3420-1.htm
 
Demain je te poste la version actuelle si tu veux.


Message édité par Arjuna le 27-06-2004 à 20:38:20
Reply

Marsh Posté le 27-06-2004 à 20:41:11    

D'ailleurs, en relisant le post initial, j'aurais mieu fait de ne pas corriger les bugs :sweat:
 
Je suis passe de 17 000 à 67 000 lignes (aucun doublon) et de 30 minutes à une journée :sweat:
 
J'ai réussi à la faire tourner un coup, en la lançant le matin en arrivant, j'ai retrouvé les résultat le lendemain matin, a priori elle a fini avant minuit, heure à laquelle Oracle est descendu afin de faire un backup a froid.

Reply

Marsh Posté le 27-06-2004 à 20:42:13    

12h pour renvoyer en tout seulement 67000 lignes ? elle doit être méchante la requête :)
Moi je suis sur une migration de données assez conséquente (un des batches doit extraire plus de 90.000.000 lignes d'une table, en faisant quelques jointures sur d'autre :D ), mais on a réussi à faire tomber la durée d'exécution à environ 2h30, ce qui est plutôt correct vu le volume de données (on est limités par les performances moyennes de la baie de disques :D ).
 
Pour ton problème, si tu donnes pas plus d'infos, je vais pas pouvoir t'aider :|
 
edit : ah oui, je me souviens de ton sujet :)


Message édité par Beegee le 27-06-2004 à 20:43:56
Reply

Marsh Posté le 27-06-2004 à 20:43:49    

Le problème c'est que dimanche je ne suis pas à mon boulot ;)
 
Je te poste le tout demain (requête + vue) histoire de voir à quoi ça ressemble. Tu me demanderas s'il y a des trucs que tu ne comprends pas dans la vue, parceque le modèle des données est relativement compliqué :))

Reply

Marsh Posté le 27-06-2004 à 20:49:40    

ce qui est assez étrange, c'est qu'avec une requête aussi symétrique, on arrive pas à simplifier ...
 
enfin je veux dire : y a apparemment pas de vraie jointure entre tes cap<X> ... tu as essayé déjà de faire une vue sur ta table cap qui filtre sur :
cap1.codsoc(+) = 0  
and cap1.codcal(+) = 'FER'
 
et de l'utiliser au lieu de la table cap directement ?
 
A mon avis faut chercher dans ce sens ...
 
et ne pas hésiter à faire des sous-requêtes si possible, pour réduire le volume de données manipulées dès le début.

Reply

Marsh Posté le 27-06-2004 à 22:31:58    

codsoc / codcal / datfin étant l'index unique de la table (la clé) je ne pense pas qu'une vue doit vraiment utile.
 
Deplus, toutes les lignes ont codsoc à 0 (l'ERP a un support multi-SOCiété, mais ici seule une instance est installée), et FER est correspond à 80% des lignes. A partir de là, utiliser une vue pour réduire le nombre de lignes lues ne me semble pas super intéressante.
Je garde quand même l'idée sous le coude, demain je doit finir la rédaction de la documentation fonctionnelle d'un projet que je dois démarrer dans la semaine, mais si j'ai le temps, je ne manquerai pas de faire le test, de toute façon je ne risque rien à essayer ;)
 
Sinon, la jointure se fait uniquement sur les champs date.
Toutes les périodes fériées qui se terminent durant la période étudiée sont réputées comme complètes (tant pis si quelques lignes peuvent être saisies pendant un week-end, l'erreur indûe ne justifie pas une complexification de la requête), donc je n'ai qu'à prendre les périodes fériées qui se terminent dans la période étudiée pour connaître le nombre de jours fériés dans ma période.
 
Pas besoin de faire intervenir la date de début, car non seulement j'aurai peut-être quelques lignes de moins (beaucoup moins de 1%), mais surtout ce champ n'étant pas indexé, je perdrais en performances.
 
Je te poste le détail de tout ce bronx demain.
 
Chose amusant à noter d'ailleurs, c'est qu'au départ j'avais oublié de faire une jointure externe, et depuis que je l'ai mise, la requête est plus rapide (sauf qu'elle s'est complexifiée depuis donc elle est plus lente mais bon :D)

Reply

Marsh Posté le 01-07-2004 à 09:12:35    

Quelques notes intéressantes sur :
- Le probleme une table/utilisateur (cai mal)
- Ne pas mettre de clé primaire dans une table (cai mal)
- Les tables isolés dans le modèle
 

Arjuna et Jagstang ont écrit :


...
il ne faut pas faire une BASE, ni même une TABLE par utilsateur crée
...
 
... peut on faire une table sans clé ?
--> Non
 
...
il se peut qu'une table n'aie aucun lien avec les autres (par exemple les logs)  
mais pourquoi la mettre dans une autre base ? ça n'apporte rien, si ce n'est du travail supplémentaire


 
Le débat sur le fait de mettre des clés primaires (ou non) va surement continuer ici http://forum.hardware.fr/hardwaref [...] 4188-1.htm

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed