Optimisation requête

Optimisation requête - CROSS JOIN

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:

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 !

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.

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.

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.

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

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 ;)

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:

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');

Code:

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 ?

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:

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');

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

Code:

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');

Citation:

Envoyé par Herman

Ça me semble pas mal :D 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:

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).

Ah oui ça fonctionne effectivement, je m'étais raté ! Merci encore :)

Et que donne la requête côté performance et stabilité ?

Le retour des résultats est quasi immédiat :D