Réduire (Optimiser) le nombre de sous-requêtes sql sous Oracle

**sabdoul** · 10/10/2013, 13h33

Bonjour, j'ai une requête SQL qui prend du temps avant de s’exécuter car comportant trop de sous-requête.
ma requête est une requête qui doit m'afficher les lignes d'une table (table caisse) dont les prix sont inférieur ou supérieur de 5f au prix de référence contenus dans une autre table (table prix) pour chaque produit. ma table prix comporte plusieurs lignes d'un même produit en fonction des différents prix qui ont été mise a jour selon une date donnée. Ainsi ma requête doit prendre en compte le prix de chaque produit dont la date maximum de mise a jour est inférieur ou égale la date entrée par l’utilisateur.
ma table prix se presente comme suit:

Code :

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

1
2
3
4
5
CODE_PROD,
DESCRIPTION,
PRIX,
CODE_MAG,
DATE_PRIX

et la table caisse comme suit:

Code :

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

1
2
3
4
5
6
CODE_MAG,
CODE_PROD ,
DESCRIPTION,
QUANTITE,
PRIX,
DATE_TICKET

Ainsi pour cela j'ai fait cette requete CTE qui me permet de recupérer pour chaque produit sa date maximum par rapport la date entrée par l’utilisateur , ensuite lui associer son prix correspondant à cette date et pour finir faire la jointure entre la table caisse pour afficher les lignes de la table caisse dont le prix est inférieur ou supérieur de 5f au prix de référence.
la requette CTE:

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
WITH prixV1 AS (
SELECT code_prod,code_mag,max(date_prix) as date_prix
       from prix
   WHERE code_mag=(select CODE_MAG from MAGASIN where DESCRIPTION='";
       $sql1.=$etabl[$i]. "') and date_prix <= '" . $date . "'
	   GROUP BY code_prod,code_mag
),
prixV AS(
select s.code_prod,s.code_mag,s.date_prix,t.prix
       from prixV1 s, prix t
     where t.code_prod=s.code_prod and t.date_prix=s.date_prix and t.code_mag= s.code_mag
)
 select t.CODE_PROD \"CODE PRODUIT\",t.DESCRIPTION,
		 to_char(round(t.QUANTITE),'999G999G999G999',  'nls_numeric_characters='', ''') \"Quantite\",
		 to_char(t.PRIX,'999G999G999G990D99',  'nls_numeric_characters='', ''') \"Prix de vente\",
		 to_char(s.PRIX,'999G999G999G990D99',  'nls_numeric_characters='', ''') \"Prix de reference\",
		  to_char(t.PRIX-s.PRIX,'999G999G999G990D99',  'nls_numeric_characters='', ''') \"Ecart\",
		 from caisse t
		 inner JOIN prixV s ON t.code_prod=s.code_prod 
         where t.code_mag=(select CODE_MAG from MAGASIN where DESCRIPTION='";
       $sql1.=$etabl[$i]. "') and (t.prix-s.prix>5 or t.prix-s.prix<-5) 
		  and t.date_ticket = '" . $date . "'";

comme dit précédemment cette requete met du temps avant de s'exécuter.
Ainsi j'aurais voulu utiliser un seul CTE comme ci-dessous:

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
WITH prixV1 AS (
SELECT code_prod,code_mag,prix,max(date_prix) as date_prix
       from prix
   WHERE code_mag=(select CODE_MAG from MAGASIN where DESCRIPTION='";
       $sql1.=$etabl[$i]. "') and date_prix <= '" . $date . "'
	   GROUP BY code_prod,code_mag,prix
)
 select t.CODE_PROD \"CODE PRODUIT\",t.DESCRIPTION,
		 to_char(round(t.QUANTITE),'999G999G999G999',  'nls_numeric_characters='', ''') \"Quantite\",
		 to_char(t.PRIX,'999G999G999G990D99',  'nls_numeric_characters='', ''') \"Prix de vente\",
		 to_char(s.PRIX,'999G999G999G990D99',  'nls_numeric_characters='', ''') \"Prix de reference\",
		  to_char(t.PRIX-s.PRIX,'999G999G999G990D99',  'nls_numeric_characters='', ''') \"Ecart\",
		 from caisse t
		 inner JOIN prixV s ON t.code_prod=s.code_prod 
         where t.code_mag=(select CODE_MAG from MAGASIN where DESCRIPTION='";
       $sql1.=$etabl[$i]. "') and (t.prix-s.prix>5 or t.prix-s.prix<-5) 
		  and t.date_ticket = '" . $date . "'";

mais la fonction de groupage me fait des doublons a cause du prix.
je sais pas s'il y'a une fonction agrégat qui n'aura pas d'influence sur le champ prix que je peux utiliser sur le champ prix me permettant ainsi de le mettre dans la fonction de groupage évitant ainsi les doublons car les sum,min,avg... font ds opérations sur toutes les lignes du champ prix alors que moi je ne veux que le prix unique correspondant à cette date.
où si vous avez des suggestions pour que je puisse améliorer ma requete j'en serai ravi. merci

**skuatamad** · 10/10/2013, 13h50

Envoyé par sabdoul

mais la fonction de groupage me fait des doublons a cause du prix.

S'il n'y a qu'un prix par date (ou si vous ne souhaitez en avoir qu'un) utilisez keep dense_rank, sinon utilisez une fonction analytique.

Et utilisez des variables de liaisons à la place de la concaténation utilisée actuellement :

Code :

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

1
2
DESCRIPTION='";
       $sql1.=$etabl[$i]. "') AND date_prix <= '" . $date . "'

**sabdoul** · 10/10/2013, 13h59

Envoyé par skuatamad

S'il n'y a qu'un prix par date (ou si vous ne souhaitez en avoir qu'un) utilisez keep dense_rank, sinon utilisez une fonction analytique.

Et utilisez des variables de liaisons à la place de la concaténation utilisée actuellement :

Code :

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

1
2
DESCRIPTION='";
       $sql1.=$etabl[$i]. "') AND date_prix <= '" . $date . "'

Salut, merci de me répondre effectivement il n'y a qu'un seul prix par date je vais regarder le liens que vous m'avez suggéré.
cependant pour les variables de liaisons je sais pas comment on s'en sert pouvez vous m'en dire plus. merci

**sabdoul** · 10/10/2013, 14h30

salut, skuatamad je viens de regarder du côté des fonctions analytiques il semble être beaucoup intéressant mais a vrai dire je m'y perd dans les explications car ne connaissant pas trop ce dernier je compte me mettre mais pour l'instant pouvez vous me donner la correspondance en fonction analytiques pour cette requête. merci
cependant je suis toujours preneur pour une autre solution autre que les fonctions analytiques me permettant d'avoir une requête plus rapide a l’exécution. merci

**skuatamad** · 10/10/2013, 15h22

Comme ceci peut être :

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
  WITH prixV1 AS (
SELECT code_prod, code_mag,
       max(prix) keep (dense_rank first order by date_prix desc) as prix,
       max(date_prix) AS date_prix
  FROM prix
 WHERE code_mag = (SELECT CODE_MAG FROM MAGASIN WHERE DESCRIPTION = :description) 
   AND date_prix <= :dte
 GROUP BY code_prod,code_mag
)
SELECT t.CODE_PROD \"CODE PRODUIT\",t.DESCRIPTION,
		   to_char(round(t.QUANTITE),'999G999G999G999',  'nls_numeric_characters='', ''') \"Quantite\",
		   to_char(t.PRIX,'999G999G999G990D99',  'nls_numeric_characters='', ''') \"Prix de vente\",
		   to_char(s.PRIX,'999G999G999G990D99',  'nls_numeric_characters='', ''') \"Prix de reference\",
		   to_char(t.PRIX-s.PRIX,'999G999G999G990D99',  'nls_numeric_characters='', ''') \"Ecart\",
  from caisse t
	JOIN prixV s ON t.code_prod = s.code_prod 
 where t.code_mag = (SELECT CODE_MAG FROM MAGASIN WHERE DESCRIPTION = :description) 
   AND (t.prix-s.prix>5 or t.prix-s.prix<-5) 
   and t.date_ticket = :dte

Pour les variables de liaisons (bind variable), regardez la doc de votre lib, si vous utilisez php par exemple oci_bind_by_name

**sabdoul** · 10/10/2013, 16h38

Merci skuatamad la requête marche mais j'aimerais que tu m'explique cette ligne

Code :

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

max(prix) keep (dense_rank first ORDER BY date_prix DESC) AS prix

car c'est la seule ligne qui a changé dans ma requête précédente et qui m'enlever les doublons que j'avais merci encore

Réduire (Optimiser) le nombre de sous-requêtes sql sous Oracle

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager