Optimisation requête - CROSS JOIN

**Herman** · 11/08/2015, 10h09

Bonjour à tous,

J'ai besoin d'un peu d'aide pour l'optimisation de la requête ci-dessous

Contexte :
Table Dimension Produit : D_PRODUCT
Table Dimension Temps : D_TIME (granularité au mois)
Table Dimension Account : D_ACCOUNT
Table faits : F_SALES

Je n'ai des records dans Sales que s'il y a eu effectivement une vente pour un produit / time / Account. Utilisant Tableau (outil BI), je dois "recréer artificiellement" les records pour lesquels il n'y a pas de vente, de sorte à pouvoir faire ce que je veux faire sous Tableau, d'où l'utilisation de CROSS JOIN pour obtenir un record par Product / Time / Account (et les mettre à '0' s'il n'y a pas eu de vente). Forcément, je ne m'attends pas à ce que la requête mette 2 seconde à s’exécuter, mais je pense (j'espère) qu'il est possible de faire mieux que mes 45 minutes+ d'execution. J'ai essayé de restreindre la quantité de données dans mes CROSS JOIN (avec des sous-requêtes sur D_TIME notamment en passant les filtres dans une sous-requête), mais j'ai 'l'impression' que cela augmente le temps d’exécution.

Volumétrie :
Dimension Produit : 15 000 records
Dimension Temps : 250 records si je prends tout. 48 records si j'applique mes filtres dans une sous-requête dans le cross join (mais comme je le disais j'ai l'impression que ça met encore plus de temps - je me trompe peut être)
Dimension Account : 200 records
Fait Sales : 500 000 records. 260 000 records avec le filtre appliqué pour récupérer les données des 48 derniers mois.

Logique:
Je CROSS JOIN ma D_PRODUCT à ma D_TIME et à ma D_ACCOUNT (j'ai donc une ligne par produit / temps / Account), en orange ci-dessous. Et je Left Join avec mes Sales (en vert ci-dessous).

Requête :

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
CREATE OR REPLACE FORCE VIEW MaVue
(
   NET_SALES,
   ACCOUNT,
   PRODUCT,
   YEAR_MONTH, -- Format YYYY-MM
   YEAR_MONTH_DATE_FORMAT,
   GROSS_SALES
)
AS
     SELECT 
            COALESCE (SUM (sub_sales.sub_sum_netsales), 0) AS sum_netsales,
            subquery.ACCOUNT,
            subquery.PRODUCT,
            subquery.YEAR_MONTH,
            TO_DATE (subquery.YEAR_MONTH, 'YYYY-MM'),
            COALESCE (SUM (sub_sales.sub_sum_gross), 0) AS sum_grosssales
     FROM   (SELECT   
                         D_PRODUCT.PRODUCT,
                         D_PRODUCT.PRODUCT_ID,
                         D_TIME.YEAR_MONTH,
                         D_TIME.YYYYMM,
                         D_ACCOUNT.ACCOUNT,
                         D_ACCOUNT.UNIQUEIDACCOUNT -- Unique ID
             FROM D_PRODUCT
                         CROSS JOIN D_TIME
                         CROSS JOIN D_ACCOUNT
                         WHERE D_TIME.YYYYMM >= TO_CHAR (ADD_MONTHS (SYSDATE, -48), 'YYYYMM') -- Récupération des 48 derniers mois
                         AND D_TIME.YYYYMM < TO_CHAR (SYSDATE, 'YYYYMM') -- Récupération des données seulement jusqu'à aujourd'hui et pas jusque fin d'année 2015
                         AND D_ACCOUNT.COUNTRY = 'XX' -- Récupération Pays 'XX' seulement
             GROUP BY    D_PRODUCT.PRODUCT,
                         D_PRODUCT.PRODUCT_ID,
                         D_TIME.YYYYMM,
                         D_TIME.YEAR_MONTH,
                         D_ACCOUNT.ACCOUNT,
                         D_ACCOUNT.UNIQUEIDACCOUNT ) subquery
            LEFT JOIN
               ( SELECT SUM (NET_SALES) AS sub_sum_netsales,
                         SUM (GROSS_SALES) AS sub_sum_gross,
                         PRODUCT_ID,
                         YYYYMM,
                         UNIQUEIDACCOUNT 
                    FROM F_SALES
                   WHERE YYYYMM >= TO_CHAR (ADD_MONTHS (SYSDATE, -48), 'YYYYMM') -- récupération des 48 derniers mois
                GROUP BY PRODUCT_ID, YYYYMM, UNIQUEIDACCOUNT) sub_sales
            ON     sub_sales.PRODUCT_ID = subquery.PRODUCT_ID -- Jointure Product
               AND subquery.YYYYMM = sub_sales.YYYYMM - Jointure Time
               AND subquery.UNIQUEIDACCOUNT = sub_sales.UNIQUEIDACCOUNT - Jointure Account
   GROUP BY subquery.ACCOUNT,
            subquery.PRODUCT,
            subquery.YEAR_MONTH,
            TO_DATE (subquery.YEAR_MONTH, 'YYYY-MM');

Merci beaucoup !

**bstevy** · 11/08/2015, 10h27

Vous êtes sur de votre cross join ? que pensez vous du full outer ?
Puisque vous avez des enregistrements, et que vous voulez juste combler les trous, autant utiliser un full outer.

**Herman** · 11/08/2015, 10h37

Justement non, il ne s'agit pas de combler les trous car je n'ai pas du tout d'enregistrements s'il n'y a pas de Ventes. Il faut que je crée ces enregistrements de toutes pièces.

**Waldar** · 11/08/2015, 11h14

15.000 produits * 48 mois * 200 comptes = 144 millions de lignes, et après vous faites une jointure externe sur une table de 0.25 millions de lignes.

**Herman** · 11/08/2015, 11h18

Merci bien, j'ai également réussi à faire cette multiplication sur la calculette Windows

Plus sérieusement, pour un même nombre de lignes on peut passer d'un certain temps d’exécution à un autre * 100, d'où ma question sur une éventuelle optimisation de la requête qui pour ce même nombre de ligne me ferait gagner un temps précieux

**Waldar** · 11/08/2015, 11h28

Je pense qu'il faut essayer quand même de réduire le nombre de produits que vous présentez au final.
Que donne cette requête :

Code :

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

1
2
3
4
5
select count(distinct product_id)      as nb_product
     , count(distinct yyyymm)          as nb_mois
     , count(distinct uniqueidaccount) as nb_compte
  from f_sales
 where yyyymm >= to_char (add_months (sysdate, -48), 'yyyymm');

**Herman** · 11/08/2015, 11h47

Code :

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

1
2
3
nb product : 227
nb mois : 49
nb compte : 232 -- Certains sont surement inactifs d'où la différence avec ma D_PRODUCT

Donc, disons que mes produits qui n'ont jamais eu aucune vente ne m'interesse pas : je devrais pouvoir faire un Inner entre Sales et Product pour ne récupérer que mes 227 produits. Je CROSS JOIN ça sur la D_TIME et D_ACCOUNT, et ensuite je LEFT comme je le faisais déjà sur mes sales. Comme ça je descends à 2 000 000 records avant mon LEFT. Correct ?

**Waldar** · 11/08/2015, 12h00

Au niveau de la volumétrie c'est beaucoup plus exploitable, que ce soit par un outil ou par un œil humain.
J'ai maintenant une autre méthode à vous proposer.

Essayez comme ceci :

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
23
24
25
26
27
28
with cte_sales as
(
  select product_id
       , yyyymm
       , uniqueidaccount
       , sum(net_sales)   as sub_sum_netsales
       , sum(gross_sales) as sub_sum_gross
    from f_sales
   where yyyymm >= to_char (add_months (sysdate, -48), 'yyyymm') -- récupération des 48 derniers mois
group by product_id
       , yyyymm
       , uniqueidaccount
)
    select tim.year_month
         , tim.yyyymm
         , prd.product
         , prd.product_id
         , act.compte
         , sls.uniqueidaccount
         , coalesce(sls.sub_sum_netsales, 0) as sub_sum_netsales
         , coalesce(sls.sub_sum_gross   , 0) as sub_sum_gross
      from cte_sales sls partition by (product_id, uniqueidaccount)
right join d_time    tim  on tim.yyyymm          = sls.yyyymm 
inner join d_product prd  on prd.product_id      = sls.product_id
 left join d_account act  on act.uniqueidaccount = sls.uniqueidaccount -- jointure externe pour gérer d'éventuels comptes qui ne seraient plus présents dans d_account
                         and act.country         = 'XX'
     where tim.yyyymm >= to_char (add_months (sysdate, -48), 'yyyymm')
       and tim.yyyymm <  to_char(sysdate, 'yyyymm');

**Herman** · 11/08/2015, 12h25

Ça me semble pas mal

Les with ne fonctionne pas dans une vue, c'est ça ? Du coup je passe comme ça et je vais tester immédiatement !

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
23
24
select tim.year_month
         , tim.yyyymm
         , prd.product
         , prd.product_id
         , act.account
         , sls.uniqueidaccount
         , coalesce(sls.sub_sum_netsales, 0) as sub_sum_netsales
         , coalesce(sls.sub_sum_gross   , 0) as sub_sum_gross
from (  select product_id
                    , yyyymm
                    , uniqueidaccount
                    , sum(net_sales)   as sub_sum_netsales
                    , sum(gross_sales) as sub_sum_gross
              from f_sales
              where yyyymm >= to_char (add_months (sysdate, -48), 'yyyymm') -- récupération des 48 derniers mois
              group by product_id
                     , spbup
                     , uniqueidaccount) sls partition by (product_id, uniqueidaccount)
right join D_TIME    tim  on tim.yyyymm          = sls.yyyymm 
inner join D_PRODUCT prd  on prd.product_id      = sls.product_id
left join D_ACCOUNT act  on act.uniqueidaccount = sls.uniqueidaccount -- jointure externe pour gérer d'éventuels comptes qui ne seraient plus présents dans d_account
                         and act.country  = 'XX'
       where tim.yyyymm  >= to_char (add_months (sysdate, -48), 'yyyymm')
       and tim.yyyymm  <  to_char(sysdate, 'yyyymm');

**Waldar** · 11/08/2015, 14h08

Envoyé par Herman

Ça me semble pas mal

Les with ne fonctionne pas dans une vue, c'est ça ? Du coup je passe comme ça et je vais tester immédiatement !

Pas de problème, en quelle version êtes-vous ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
create view v_dummy as
with cte_toto as
(
select dummy from dual
)
select dummy
  from cte_toto;
 
-- view V_DUMMY créé(e).
 
drop view v_dummy;
 
-- view V_DUMMY supprimé(e).