Discussion :

[Herman]

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]

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]

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]

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]

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]

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