Optimisation d'une requête SQL

**gaboo_bl** · 20/10/2006, 16h46

Bonjour à tous, j'aimerais avoir votre avis sur une requête que je suis en train d'étudier en vue d'optimisation.
Voilà le morceau d'existant qui me gène:

Code :

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

1
2
3
4
5
6
7
8
9
10
select data.clé,tbl1.mnt, nvl(tbl2.mnt,0) + nvl(tbl3.mnt,0) + nvl(tbl4.mnt,0)
from data, couts tbl1,couts tbl2,couts tbl3,couts tbl4  
where  tbl1.clé(+) = data.clé
   AND tbl1.type(+) = 'a'
   AND tbl2.clé(+) = data.clé
   AND tbl2.type(+) = 'b'
   AND tbl3.clé(+) = data.clé
   AND tbl3.type(+) = 'c'
   AND tbl4.clé(+) = data.clé
   AND tbl4.type(+) = 'd'

en prennant en compte que couts est une table qui contient beaucoup de données, cette requête perds très vite en temps de réponse selon les clauses where qu'on y ajoute.
Alors le but avoué de cette requête, après étude, est de présenter des données qui sont dans plusieurs enregistrements dans la table couts dans un seul enregistrement au final. Je suis OK pour dire que c'est une très mauvaise solution et qu'il faudrait faire çà de manière logicielle, mais pour l'instant çà n'est pas possible pour cause de contraintes projet.

J'ai essayé ceci:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
select data.clé,
       tbl1.montant,
       tbl2.somme
from data, 
        (select clé, nvl(montant,0) as montant 
                from couts 
                where type='d') tbl1, 
        (select clé,sum(nvl(montant,0)) as somme 
                from couts 
                where type in ('a','b','c')group by clé) tbl2
 WHERE tbl1.clé(+) = data.clé
           and tbl2.clé(+) = data.clé

En effet comme le volume de couts est imposant (celui de data aussi d'ailleurs), je me suis dit qu' en réduisant le nombre de full table access, je gagnerais en perfs, mais au contraire, les tris supplémentaires me plombent encore plus...j'ai donc cherché d'autres solutions mais à chaque fois il me faut un group by, et donc je perds plus que je ne gagne.

Quelqu'un aurais une idée, ou est ce que je peux pas faire mieux uniquement au niveau sql?
J'ai oublié de préciser: C'est sur Oracle 9i

**PpPool** · 20/10/2006, 17h30

Bonjour

je n'ai que parcouru ta requete.

je pense que tu devrai regarder du coté des fonctions analytiques si tu es au moins en Oracle 9i.

pour ton pb de tri sur ta colonne type, tu devrais pouvoir t'en sortir avec les case ou les decode.

je suis désolé je suis sur le point de partir, j'essaierai de t'aider plus ce we ou lundi

**gaboo_bl** · 20/10/2006, 17h40

J'ai également essayé avec une fonction analytique:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
select  distinct data.clé,
          tbl1.montant,
          sum(tbl2.montant) over(partition by data.clé)
from data, 
       (select clé, nvl(montant,0) as montant 
                from couts where type='d') tbl1, 
       (select clé,nvl(montant,0) as montant 
                from couts 
                where type in ('a','b','c')) tbl2
 WHERE tbl1.clé(+) = data.clé
       and tbl2.clé(+) = data.clé

sauf qu'avec ces jointures je récupère 4x plus de lignes, et donc je dois rajouter un distinct d'où replombage grrr

enfin peut être que je m'y prend mal je viens à peine de me mettre aux fonctions analytiques.

**remi4444** · 20/10/2006, 18h07

Si tu as un index sur le couple type/clé et que tes stats sont correctement passées, je vois pas pourquoi ta premiere version de requête poserait problème... tu as le plan d'exéction ?

**orafrance** · 23/10/2006, 08h00

4 produits cartésiens c'est un peu violent quand même

**gaboo_bl** · 23/10/2006, 09h37

remi444 -> là est le problème justement: le plan d'execution me sort uniquement des full table access, mais étant donné qu'il s'agit des tables d'un progiciel je ne peux pas modifier le schéma pour ajouter des indexs ou des clés.

Fred_D -> effectivement à la base j'aurais conçu çà différemment, mais bien que j'eusse (çà faisait pas beau "aie"

) pensé le contraire, c'est plus rapide qu'avec le group by.

**remi4444** · 23/10/2006, 10h37

Envoyé par gaboo_bl

remi444 -> là est le problème justement: le plan d'execution me sort uniquement des full table access, mais étant donné qu'il s'agit des tables d'un progiciel je ne peux pas modifier le schéma pour ajouter des indexs ou des clés.

Si il n'y a pas d'index, oracle ne peut faire que des full access, ta requête étant assez simple, je pense que tu n'obtiendra pas mieux que ce que te propose l'optimiseur.

par contre un index théoriquement ça ne doit pas faire de mal si les stats sont passées correctement. Si cette notion "type/clé" est utilisée par le progiciel, c'est un peu bizarre qu'il n'y ai pas d'index. Il faudrait que tu fasse un état des lieux des index de ta table, peut etre découvrira-t-on un chemin indexé possible...

D'autre part, il est tout à fait normal qu'il y ait un full access sur la table DATA vu que tu ne fait aucun filtre et que tu demande d'afficher tous les champs DATA.clé. On peut pas reprocher à oracle de tout parcourir quand on lui demande de tout afficher

Sinon le seul truc que tu peux viser est de ne faire qu'un seul full-scan par table jouant avec des fonction conditionnelle dans le select.

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT DATA.clé,nvl(sum(tbl.mnt1),0),nvl(sum(tbl.mnt2),0)
FROM DATA,
 (select clé,
  decode (clé,'a',mnt) as mnt1, 
  decode (clé,'b',mnt,'c',mnt,'d',mnt) as mnt2 
  from couts where type in ('a','b','c','d') 
  ) tbl
WHERE  tbl.clé(+) = DATA.clé
group by DATA.clé

C'est un peu dans le même esprit que ce que tu as proposé mais je pense que ça va etre moins couteux...

**orafrance** · 23/10/2006, 10h37

t'as essayé avec une clause WITH ?

**gaboo_bl** · 23/10/2006, 11h57

Envoyé par remi4444

D'autre part, il est tout à fait normal qu'il y ait un full access sur la table DATA vu que tu ne fait aucun filtre et que tu demande d'afficher tous les champs DATA.clé. On peut pas reprocher à oracle de tout parcourir quand on lui demande de tout afficher

Tout à fait d'accord sur ce point, en fait je teste la requête sans jointure, mais "dans la vrai vie" de l'application il y en a toujours au moins une qui permet de saucissoner la table data. Cette requête est en fait un bout de requête que j'ai extrait pour essayer de l'optimiser spécifiquement, car il revient souvent dans l'appli.
J'ai testé ta solution, elle prend deux fois plus de temps que celle d'origine à s'executer MAIS je me suis dit un truc:

Dans un group by, on a une opération de tri,non? tandis que dans celle d'origine, il n'y en a pas, alors est ce que on ne pourrait pas penser que plus on travaille sur des grands jeux de données (et donc plus on a de données à trier), moins une requête à base de group by va être interessante?
Autrement dit est ce que le rapport entre mes résultats en performances ne s'inverseraient pas à mesure que je rajoute des jointures sur la table data?

Bon sinon j'ai pas encore essayé avec les with (je connaissais pas, merci de l'info

), je vous tiens au courant des résultats.

**gaboo_bl** · 23/10/2006, 12h09

Bon, oubliez mon histoire de volume de donnée c'est naze finalement

, je conserve la même proportion entre les temps d'execution quelque soit le volume de donnée traité. Allez j'essaye les WITH maintenant.

**McM** · 23/10/2006, 13h15

C'est bizarre les produits cartésiens :
Cle/type dans la table couts est unique ?

Donne les desc des tables et des index.

**McM** · 23/10/2006, 13h22

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT d.clé, 
		NVL((	SELECT SUM(mnt) 
			FROM couts
			WHERE cle = d.cle
			AND TYPE = 'a'
		),0) AS mnt1,
		NVL((	SELECT SUM(mnt) 
			FROM couts
			WHERE cle = d.cle
			AND TYPE BETWEEN 'b' AND 'd' --ou IN ('b', 'c', 'd') --
		),0) AS mnt2;
FROM 	DATA d

Ca dépend pas mal du volume de données aussi et des indexes sur couts

**gaboo_bl** · 23/10/2006, 14h09

Attention ne vous basez pas sur les noms et valeurs, je les aie changé pour des raisons de confidentialité; par exemple, dans la vrai requête, 'a', 'b', 'c', 'd' ne se suivent pas.
Pour les mêmes raisons je ne peux pas vous fournir les describe complets. Par contre je peux vous dire que les seuls indexs se trouvent sur la colonne clé. En effet dans le progiciel il ramène systématiquement tous les types, car c'est juste une distinction d'affichage. Il ne font jamais de jointure sur cette colonne, par contre moi si.
clé / type est (devrait être) un couple unique, mais çà n'est pas garanti par les contraintes d'intégrité.

**remi4444** · 23/10/2006, 14h22

Bien sur que le group by fait un tri qui est très couteux quand il se base sur une colonne non indexée pour une gros volume. La requête idéale est ta premiere version, il n'y a pas de tri, simplement des jointures, seulement il faut absoluement un index sur le couple "type/clé" je ne peux pas imaginer qu'on puisse faire un modèle utilisable de la sorte sans y penser...
le problème, c'est qu'on va vite arriver au bout de ce qu'on dire sur ce problème dans la mesure ou tu filtre les éléments que tu nous donne... il se peut que la clef de l'optimisation se trouve dans un "détail" que tu n'a pas à prioré jugé bon de communiquer...

**gaboo_bl** · 23/10/2006, 14h58

Ben justement, l'éditeur du progiciel ne s'en sert pas pour faire çà, d'où l'absence d'index. Malheureusement j'aimerais bien vous donner toutes les données techniques, mais vous savez comme certains clients sont frileux en matière de divulgation de données, et à juste titre! il suffirait qu'une personne mal intentionnée tombe sur mon post, reconnaisse un nom de table du progiciel, après çà serait plus que facile de se documenter sur l'architecture de mon client, et disposer d'infos sensibles.

De toute façon je suis déjà à priori limité puisque je ne peux pas modifier le schéma qui contient les tables.

Sinon pour en revenir à vos différentes proposition, j'ai testé la dernière requête proposée avec un with, et j'ai le même plan d'execution (et les mêmes perfs, du coup). Sauf si quelqu'un une formule magique venant de la version vaudou d'Oracle, je pense qu'on a fait le tour, et comme le dit remi4444, effectivement la requête d'origine et la seule à ne pas faire de tri. Comme ç'est apparemment çà qui prend le plus de temps, j'en conclue qu'on ne peux pas faire mieux sans toucher au schéma.

Je vais attendre une prochaine refonte pour essayer d'autres pistes (récupérer les données sans mises en forme et les redispatcher, façon repeating frame de reports, par exemple).

Merci à tous de votre aide en tout cas.

Tiens, une petite question subsidiaire: typiquement, dans quels cas vous utiliseriez une clause with?

PS: Ne croyez pas que je sois suspicieux envers la communauté dvp.com, bien au contraire je trouve l'activité et la réactivité de ce forum complètement formidable, c'est simplement qu'on est sur le net, et que n'importe qui a potentiellement accés au net...

**remi4444** · 23/10/2006, 15h06

Je serais toi, je tenterais de monter un environnement de tes en fabriquant le fameux index type/clé juste pour voir... Car mettre un index ce n'est pas vraiment toucher au schéma, tu ne modifie rien fonctionnelement, le seul petit risque est de dégrader par effet de bord des requêtes mal écrites sur des bases mal maintenues...

**orafrance** · 23/10/2006, 15h23

Envoyé par gaboo_bl

Ben justement, l'éditeur du progiciel ne s'en sert pas pour faire çà, d'où l'absence d'index. Malheureusement j'aimerais bien vous donner toutes les données techniques, mais vous savez comme certains clients sont frileux en matière de divulgation de données, et à juste titre! il suffirait qu'une personne mal intentionnée tombe sur mon post, reconnaisse un nom de table du progiciel, après çà serait plus que facile de se documenter sur l'architecture de mon client, et disposer d'infos sensibles.

d'abord, je vois mal comment d'un nom de table on pourrait arriver à un nom de client, à moins qu'il soit le seul à utiliser le progiciel. Quand bien même c'était un souci, tu pourrais fournir un exemple en modifiant le nom des tables et en mettant des données de tests suffisantes

**gaboo_bl** · 23/10/2006, 15h27

Oui c'est sur, mais à chaque montée de version, je n'ai aucun moyen de savoir si mon index ne va pas être supprimé, ou provoquer une erreur, et le petit risque de dégradation ou d'effet de bord devient beaucoup moins négligeable quand il est susceptible de provoquer ton réveil par téléphone en pleine nuit

Mais bon pour être sûr si j'ai le temps j'essairai çà pour ma gouverne sur une base de test

**gaboo_bl** · 23/10/2006, 15h33

Envoyé par Fred_D

d'abord, je vois mal comment d'un nom de table on pourrait arriver à un nom de client, à moins qu'il soit le seul à utiliser le progiciel. Quand bien même c'était un souci, tu pourrais fournir un exemple en modifiant le nom des tables et en mettant des données de tests suffisantes

Si tu utilise la fonction rechercher

tu trouveras facilement la SSII pour laquelle je travaille, et avec le nom d'une table, le nom du progiciel, avec le progiciel, un domaine fonctionnel précis, et avec çà et la SSII, quelques entreprises potentielles...OK ç'est tordu, mais je suis aussi payé pour être parano

. Non et puis serieux, si je le faisait pas je pourrais avoir quelques soucis, sinon je me casserais pas le...é pieds à changer tous les noms à chaque fois dans mes posts, je ferais du copier coller, c'est sûr. C'est une contrainte client en fait.

Optimisation d'une requête SQL

Oracle

Discussions similaires

Partager

Partager