Le forum (ô combien francophone) des utilisateurs de Powerbuilder.
Bonjour à tous,
J'ai un souci d'optimisation de requête avec une vue. La vue elle-même n'a pas d'importance. Sachez juste qu'elle est assez lourde, contient des distinct, group by, listagg (oracle), connect by prior (oracle), min, sum, ... Bref, que du beau monde. Le cas énoncé ci-dessous fonctionne pour toute vue. Appelons la v_client.
Le problème est le suivant.
Si une requête est exprimée de la sorte (exemple construit de toutes pièces):
SELECT v_client.id_personne, compte.id_compte FROM compte, v_client WHERE v_client.id_personne = compte.id_personne AND compte.cod_type = 'COMPLET'
Imaginons que cette requête me fournit les résultats suivants:
V_CLIENT.ID_PERSONNE | COMPTE.ID_COMPTE ------------------------------------------------------------ 4545 | 46 4648 | 87 5345 | 89
Imaginons maintenant que l'on reformule la requête de la façon suivante (on lui fournit directement les ID):
SELECT v_client.id_personne, compte.id_compte FROM compte, v_client WHERE v_client.id_personne = compte.id_personne AND compte.cod_type = 'COMPLET' AND v_client.id_personne IN ( 4545, 4648, 5345 )
Si le nombre de lignes dans la vue est grand, les performances de la première requête sont exécrables. Le problème ne se pose pas si il s'agit d'une table à la place d'une vue. Un work-around est de reformuler la première requête de la façon suivante:
SELECT compte.id_compte, (SELECT v_client.id_personne FROM v_client WHERE v_client.id_personne = compte.id_personne) FROM compte WHERE compte.cod_type = 'COMPLET'
Mais si l'on désire obtenir tous les champs de la vue, cela va dupliquer le nombre de clause SELECT et va donc aussi faire augmenter le temps de résolution (mais dans une moindre mesure).
Si l'on regarde l'explain plan d'Oracle, on remarque qu'il est normal que le 1er cas soit plus lourd que le 2ème car dans le 1er cas, le sgbd va d'abord faire un retrieve complet sur la vue AVANT de faire la jointure dans la clause where . Dans le 2ème cas, comme on lui fournit les ID, le retrieve complet sur la vue n'est pas fait. Dans le troisième cas (le word-around), je force le schéma de résolution en lui fournissant la jointure dans la clause select, et le sgbd suit alors le bon schéma de résolution.
Alors moi je dis: WTF Comment qu'on fait pour exprimer la première requête et lui demandant de suivre le bon schéma de résolution? J'ai regardé du coté des HINTs Oracle, mais rien ne semble vraiment convenir...
Hors ligne
Salut,
avec SQL server pour ce genre de cas je crée une fonction qui agira comme la vue sauf qu'elle prend les clause du where en paramètre et retourne une table (en gros le retrieve de la vue n'est pas complet vu qu'on va lui appliquer les paramètres, pas sûr de bien m'expliquer là)
CREATE FUNCTION [dbo].[F_TMP] ( @id_armoire INT ) RETURNS TABLE AS RETURN ( SELECT id_type_doc , nom_type_doc , id_armoire FROM MA_TABLE WHERE id_armoire = @id_armoire )
tu peux faire un SELECT dessus
SELECT * FROM dbo.f_tmp(3)
puis pour les jointures on fait un CROSS APPLY comme suit
http://stackoverflow.com/questions/8470 … -sql-table
Je n'ai pas utilisé oracle depuis plus d'une décénie et je n'en ai pas sous la main mais est-ce que le lien ci-dessous n'expliquerait pas cette même technique avec Oracle ?
http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html
En espérant avoir pu te mettre sur une piste
Hors ligne
C'est un proposition élégante, mais elle ne me convient pas pour deux raisons:
- La vue sera (entre autre) utilisée dans JPA et donc exit les procédures stockées
- Les procédures stockées sont très dépendantes du sgbd utilisé, ce qui veut dire que je suis bon pour reprogrammer toutes les fonctions dès que je change de sgbd.
Mais je suis déjà content de voir que d'autres personnes sont tombées sur le même problème, au moins, je ne suis pas fou ^^
D'autres propositions? :-D
Hors ligne
Shed a écrit:
ce qui veut dire que je suis bon pour reprogrammer toutes les fonctions dès que je change de sgbd.
Cela dit le SELECT dans le SELECT est spécifique à Oracle, non ?
Hors ligne
Peut-être. Mais comme dit dans mon post, cette solution là ne me satisfait pas non plus car elle ne permet la récupération que d'un seul champ.
Hors ligne
Et une requête de ce type :
SELECT v_client.id_personne, ... FROM v_client WHERE v_client.id_personne IN (SELECT compte.id_personne FROM compte WHERE compte.cod_type = 'COMPLET')
Hors ligne
Ca ne va pas non plus:
- performances pas bonnes (il fait aussi un retrieve complet de la vue)
- il me faut l'info compte.id_compte (ou plus généralement les infos d'autres tables jointes)
C'est fou quand même... Si je fais cette bête requête en deux temps, d'abord pour obtenir les id_personne depuis compte, puis après en les utilisant dans un select sur la vue, ça se passe nickel... Je deviens zinzin.
Hors ligne
Hum non le SELECT dans le SELECT n'est pas spécifique Oracle ça marche aussi sous SQL Server
Pour les procédures stockées en JPA j'ai trouvé ça mais ce n'est pas forcement applicable à ton cas et ça reste toujours de la procédure stockée donc dépendant du SGBD
http://stackoverflow.com/questions/3572 … m-java-jpa
Comme je ne connais pas Java je ne peux pas être d'une plus grande aide.
Hors ligne
Shed a écrit:
- performances pas bonnes (il fait aussi un retrieve complet de la vue)
Hum, étrange, en principe il fait d'abord le sous-select, peut-être une spécificité avec les vues (je ne sais pas je n'ai pas souvent été amené à en utiliser)
Si c'est le cas utilise directement la (les) table(s) au lieu de la vue.
Shed a écrit:
- il me faut l'info compte.id_compte (ou plus généralement les infos d'autres tables jointes)
Ca c'est pas un problème :
SELECT v_client.id_personne, compte.id_compte, ... FROM compte, v_client WHERE v_client.id_personne = compte.id_personne AND v_client.id_personne IN (SELECT compte.id_personne FROM compte WHERE compte.cod_type = 'COMPLET')
Hors ligne
Par contre, les Hints, ça c'est vraiment spécifique Oracle
Hors ligne
Et modifier la vue v_client pour rajouter la table compte n'est vraiment pas possible je suppose
Hors ligne
Hum, étrange, en principe il fait d'abord le sous-select, peut-être une spécificité avec les vues (je ne sais pas je n'ai pas souvent été amené à en utiliser)
Si c'est le cas utilise directement la (les) table(s) au lieu de la vue.
On est d'accord, c'est pas normal. Et pour se passer de la vue, c'est un doux rêve. Si je passe par là, c'est que je n'ai évidemment pas le choix.
Et modifier la vue v_client pour rajouter la table compte n'est vraiment pas possible je suppose
C'est une solution qui a été envisagée, mais le problème est qu'on va devoir ajouter chaque table à partir de laquelle on peut joindre la vue. Donc globalement toutes les tables qui référencent un id_personne. C'est pas viable.
Par contre, les Hints, ça c'est vraiment spécifique Oracle
Entièrement d'accord. Mais je soupçonne ce problème de chemin de résolution d'être aussi vraiment spécifique à Oracle... ^^
J'ai un peu recherché sur le net à ce sujet là et je suis globalement assez étonné que personne n'en parle, mis à part un ou deux sujets qui traitent du "predicate pushing". J'ai essayé quelques trucs dans ce sens mais qui sont restés sans effets.
Hors ligne