Discussion :

[colapis]

Bonjour,

Pour un projet, on me demandes de faire la Somme de certaines vente sur n-1, n et n-1 à date. J'ai réussis mais le problème c'est que ma requête est beaucoup trop lourde, elle met plus de 30 minutes à me sortir 1300 lignes...
Je sais que c'est à cause de ma requête qui est pas du tout optimisé mais c'est le seul moyen que j'ai trouvé pour qu'elle fonctionne.

est-ce que vous auriez une idée d'optimisation de ma requête ?
Je me permet de mettre la requête si 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
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
 SELECT distinct  [Source No_], [Activity Code], 
(SELECT SUM([Sales Amount (Actual)]) as CA_N
FROM [Value Entry] as valeurtotal
WHERE [Campaign No_] = 
	(
		select No_
		from [CMC Campaign] 
		where GETDATE() between [Starting Date] and [Ending Date] 
	)
AND [Activity Code] = 'APPRO' and [Sales Amount (Actual)] !=0 and valeurtotal.[Source No_] = valeur.[Source No_] 
group by [Source No_], [Activity Code]) as n,
 
(SELECT  sum([Sales Amount (Actual)])
FROM [Value Entry]  as valeurN
WHERE [Campaign No_] = 
	(
		select No_-1 
		from [CMC Campaign] 
		where GETDATE() between [Starting Date] and [Ending Date]
		AND [Activity Code] = 'APPRO'  and [Sales Amount (Actual)] !=0 and valeurN.[Source No_] = valeur.[Source No_] 
	) 
) AS total,
 
(SELECT SUM([Sales Amount (Actual)]) AS 'CA_N1'
FROM [Value Entry]  as valeurN1
WHERE [Posting Date] < 
	(
		select GETDATE()-365
	) 
and [Campaign No_] = 
	(
		select No_-1 
		from [CMC Campaign] 
		where GETDATE() between [Starting Date] and [Ending Date] 
	)
AND [Activity Code] = 'APPRO' and [Sales Amount (Actual)] !=0 and valeurN1.[Source No_] = valeur.[Source No_]  
group by [Source No_], [Activity Code])  
as 'n-1'
FROM [Value Entry] as valeur
WHERE [Activity Code] = 'APPRO' and [Sales Amount (Actual)] !=0

Et j'aimerai également que les résultats NULL ne s'affiche pas que les 3 sont NULL. Exemple :

source no_	activity code	n	total	n-1
1	approvisionnement	null	null	null
2	approvisionnement	1000	null	600
3	approvisionnement	1600	300	10000

J'aimerais que la source 1 n'apparaisse pas à cause de ses CA qui sont null sur les 3 conditions.

Merci d'avance.

[escartefigue]

Bonjour,

Pour une aide efficace, communiquez un exemple de contenu des tables en jeu et leur description exacte (idéalement le script DDL avec les ordres CREATE TABLE complets)

[colapis]

Bonjour

Merci du retour.

Je vais mettre des tableaux pour les 2 tables.

CMC CAMPAIGN

No_	Starting Date	Ending Date
2021	2021-07-01 00:00:00.000	2022-06-30 00:00:00.000
2022	2022-07-01 00:00:00.000	2023-06-30 00:00:00.000

VALUE ENTRY

Source No_	Activity Code	Sales Amount (Actual)
00010	APPRO	150
00016	APPRO	130

J'espère que c'est suffisant.
Merci.

[escartefigue]

Ce n'est pas suffisant : vu qu'il y a deux tables, il faut pouvoir réaliser une jointure, sinon on fera le produit cartésien de l'effectif des deux tables, ce qui peut largement expliquer des temps de réponse rédhibitoires

Or dans les deux tableaux communiqués, on ne voit aucune colonne en commun qui permette de réaliser cette jointure.

C'est pourquoi il est préférable de communiquer le DDL complet (les ordres CREATE TABLE) de chaque table en plus d'un extrait du contenu.

[colapis]

CMC CAMPAIGN

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
CREATE TABLE [dbo].[CMC Campaign](
	[timestamp] [timestamp] NOT NULL,
	[No_] [nvarchar](10) NOT NULL,
	[Starting Date] [datetime] NOT NULL,
	[Ending Date] [datetime] NOT NULL,
	[Type Code] [nvarchar](10) NOT NULL,
	[Status] [int] NOT NULL,
	[Min_ Threshold] [decimal](38, 20) NOT NULL,
	[Max_ Threshold] [decimal](38, 20) NOT NULL,
	[Statutory Capital Table Code] [nvarchar](10) NOT NULL,
	[Crop Rotation Table Code] [nvarchar](10) NOT NULL,
	[Campaigns Number] [int] NOT NULL,
	[Crop Rot_ Creat_ Start_ Date] [datetime] NOT NULL,
	[Crop Rot_ Creat_ End Date] [datetime] NOT NULL,
	[Crop Rot_ Mod Start_ Date] [datetime] NOT NULL,
	[Crop Rot_ Mod End Date] [datetime] NOT NULL,
	[Average Harvest Minimum Amount] [decimal](38, 20) NOT NULL,
	[JA Campaign Number] [int] NOT NULL,
 CONSTRAINT [CMC Campaign$0] PRIMARY KEY CLUSTERED 
(
	[No_] ASC
)

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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
CREATE TABLE [dbo].[Value Entry](
	[timestamp] [timestamp] NOT NULL,
	[Entry No_] [int] NOT NULL,
	[Item No_] [nvarchar](20) NOT NULL,
	[Posting Date] [datetime] NOT NULL,
	[Item Ledger Entry Type] [int] NOT NULL,
	[Source No_] [nvarchar](20) NOT NULL,
	[Document No_] [nvarchar](20) NOT NULL,
	[Description] [nvarchar](50) NOT NULL,
	[Location Code] [nvarchar](10) NOT NULL,
	[Inventory Posting Group] [nvarchar](10) NOT NULL,
	[Source Posting Group] [nvarchar](10) NOT NULL,
	[Item Ledger Entry No_] [int] NOT NULL,
	[Valued Quantity] [decimal](38, 20) NOT NULL,
	[Item Ledger Entry Quantity] [decimal](38, 20) NOT NULL,
	[Invoiced Quantity] [decimal](38, 20) NOT NULL,
	[Cost per Unit] [decimal](38, 20) NOT NULL,
	[Sales Amount (Actual)] [decimal](38, 20) NOT NULL,
	[Salespers__Purch_ Code] [nvarchar](10) NOT NULL,
	[Discount Amount] [decimal](38, 20) NOT NULL,
	[User ID] [nvarchar](50) NOT NULL,
	[Source Code] [nvarchar](10) NOT NULL,
	[Applies-to Entry] [int] NOT NULL,
	[Global Dimension 1 Code] [nvarchar](20) NOT NULL,
	[Global Dimension 2 Code] [nvarchar](20) NOT NULL,
	[Source Type] [int] NOT NULL,
	[Cost Amount (Actual)] [decimal](38, 20) NOT NULL,
	[Cost Posted to G_L] [decimal](38, 20) NOT NULL,
	[Reason Code] [nvarchar](10) NOT NULL,
	[Drop Shipment] [tinyint] NOT NULL,
	[Journal Batch Name] [nvarchar](10) NOT NULL,
	[Gen_ Bus_ Posting Group] [nvarchar](10) NOT NULL,
	[Gen_ Prod_ Posting Group] [nvarchar](10) NOT NULL,
	[Document Date] [datetime] NOT NULL,
	[External Document No_] [nvarchar](35) NOT NULL,
	[Cost Amount (Actual) (ACY)] [decimal](38, 20) NOT NULL,
	[Cost Posted to G_L (ACY)] [decimal](38, 20) NOT NULL,
	[Cost per Unit (ACY)] [decimal](38, 20) NOT NULL,
	[Document Type] [int] NOT NULL,
	[Document Line No_] [int] NOT NULL,
	[Order Type] [int] NOT NULL,
	[Order No_] [nvarchar](20) NOT NULL,
	[Order Line No_] [int] NOT NULL,
	[Expected Cost] [tinyint] NOT NULL,
	[Item Charge No_] [nvarchar](20) NOT NULL,
	[Valued By Average Cost] [tinyint] NOT NULL,
	[Partial Revaluation] [tinyint] NOT NULL,
	[Inventoriable] [tinyint] NOT NULL,
	[Valuation Date] [datetime] NOT NULL,
	[Entry Type] [int] NOT NULL,
	[Variance Type] [int] NOT NULL,
	[Purchase Amount (Actual)] [decimal](38, 20) NOT NULL,
	[Purchase Amount (Expected)] [decimal](38, 20) NOT NULL,
	[Sales Amount (Expected)] [decimal](38, 20) NOT NULL,
	[Cost Amount (Expected)] [decimal](38, 20) NOT NULL,
	[Cost Amount (Non-Invtbl_)] [decimal](38, 20) NOT NULL,
	[Cost Amount (Expected) (ACY)] [decimal](38, 20) NOT NULL,
	[Cost Amount (Non-Invtbl_)(ACY)] [decimal](38, 20) NOT NULL,
	[Expected Cost Posted to G_L] [decimal](38, 20) NOT NULL,
	[Exp_ Cost Posted to G_L (ACY)] [decimal](38, 20) NOT NULL,
	[Dimension Set ID] [int] NOT NULL,
	[Job No_] [nvarchar](20) NOT NULL,
	[Job Task No_] [nvarchar](20) NOT NULL,
	[Job Ledger Entry No_] [int] NOT NULL,
	[Variant Code] [nvarchar](10) NOT NULL,
	[Adjustment] [tinyint] NOT NULL,
	[Average Cost Exception] [tinyint] NOT NULL,
	[Capacity Ledger Entry No_] [int] NOT NULL,
	[Type] [int] NOT NULL,
	[No_] [nvarchar](20) NOT NULL,
	[Return Reason Code] [nvarchar](10) NOT NULL,
	[Budgeted Unit Cost] [decimal](38, 20) NOT NULL,
	[Outstanding ECD] [decimal](38, 20) NOT NULL,
	[Invoiced Cost ECD] [decimal](38, 20) NOT NULL,
	[Unit ECD Porposed] [decimal](38, 20) NOT NULL,
	[ECD Invoice Entry No] [int] NOT NULL,
	[ECD Unit Amount] [decimal](38, 20) NOT NULL,
	[ECD Calculation Priority] [int] NOT NULL,
	[Customer Price Group] [nvarchar](10) NOT NULL,
	[Sold Quantity] [decimal](38, 20) NOT NULL,
	[Eco Tax Amount] [decimal](38, 20) NOT NULL,
	[Item Charge Type] [int] NOT NULL,
	[VAT Bus_ Posting Group] [nvarchar](10) NOT NULL,
	[VAT Prod_ Posting Group] [nvarchar](10) NOT NULL,
	[Campaign No_] [nvarchar](10) NOT NULL,
	[Origin Vendor No_] [nvarchar](20) NOT NULL,
	[Actual Location Code] [nvarchar](10) NOT NULL,
	[IC Partner Code] [nvarchar](20) NOT NULL,
	[Movement Type] [int] NOT NULL,
	[Purch_ Blanket Order No_] [nvarchar](20) NOT NULL,
	[ECD Vendor No_] [nvarchar](20) NOT NULL,
	[Buy-from Vendor No_] [nvarchar](20) NOT NULL,
	[Country_Region Code] [nvarchar](10) NOT NULL,
	[Sales Discount Code] [nvarchar](10) NOT NULL,
	[Activity Code] [nvarchar](10) NOT NULL,
	[Family Code] [nvarchar](10) NOT NULL,
	[Sub-Family Code] [nvarchar](10) NOT NULL,
	[Species Code] [nvarchar](10) NOT NULL,
 CONSTRAINT [Value Entry$0] PRIMARY KEY CLUSTERED 
(
	[Entry No_] ASC
)

Pour info le lien entre les deux c'est [Value Entry].[campaign no_] [CMC Campaign].No_
Pour l'extrait du contenus ça ira ce qu'il y a au dessus que j'avais déjà mit ?

[escartefigue]

OK, donc il y a d'énormes problèmes de modélisation qui expliquent vos soucis de performances

En premier lieu, la deuxième table "Value Entry" est ce qu'on appelle une table "obèse" : elle comporte un très grand nombre de colonnes ce qui est symptomatique d'une phase de modélisation conceptuelle sautée ou bâclée.
Qui dit table obèse dit problèmes de performances, données incohérentes, redondances et contentions multiples

En deuxième lieu, la colonne de jointure n'est pas de même longueur dans les deux tables, ce faisant, aucun index n'est éligible pour satisfaire la jointure .
Donc, il y aura systématiquement un table scan des deux tables et pour satisfaire la jointure, le nombre de rapprochements à effectuer est égal au produit cartésien des deux tables. Par exemple, pour deux tables de 5 000 lignes chacune (ce qui est pouillème), on aura déjà 25 000 000 d'opérations à faire !

En troisième lieu, conséquence des deux premières remarques, il n'y a pas de contrainte d'intégrité (aucune FK déclarée). Ce modèle autorise donc des valeurs incohérentes pour la même colonne No_ entre les deux tables .

Et pour finir, comme si ça ne suffisait pas, les noms de tables et de colonnes comportent des caractères spéciaux tels que des espaces ou des noms réservés SQL tels que timestamp, ce qui nécessite l'utilisation de crochets dans les requêtes, histoire de bien emmerder les développeurs

Bref, il est urgent de revoir ce modèle de données qui ne peut pas, par construction, fournir des résultats fiables ni des requêtes performantes

[colapis]

Merci de ce retour très intéressant. Malheureusement je n'ai pas la main sur cette base de données et de plus je suis vraiment pas qualifié pour faire de tel changements.
De ce fait ma requête ne peux pas être allégé ou être optimisé ?
Et pour les lignes qui sont toutes 'NULL' ?

[escartefigue]

La requête reste possible, mais elle ne sera jamais optimale

Par contre, pour pouvoir proposer une solution, merci de communiquer un exemple de contenu comme précédemment, mais incluant la colonne de jointure [No_] prenant 3 ou 4 valeurs et avec une bonne dizaine de lignes en tout si possible pour que l'exemple soit un peu parlant.

[colapis]

No_	Starting Date	Ending Date
2020	2020-07-01 00:00:00.000	2021-06-30 00:00:00.000
2021	2021-07-01 00:00:00.000	2022-06-30 00:00:00.000
2022	2022-07-01 00:00:00.000	2023-06-30 00:00:00.000

Posting Date	Source No_	Sales Amount(Actual)	Campaign no_	Activity Code
2020-03-20 00:00:00.000	00003078	200	2020	APPRO
2021-09-15 00:00:00.000	00002209	7000	2021	APPRO
2021-09-13 00:00:00.000	00003265	2200	2021	APPRO
2022-06-01 00:00:00.000	00000871	200	2021	APPRO
2022-07-19 00:00:00.000	00001927	900	2022	APPRO
2022-08-24 00:00:00.000	00001927	500	2022	APPRO
2021-05-31 00:00:00.000	00003078	300	2020	APPRO
2022-09-20 00:00:00.000	00003078	600	2022	APPRO
2022-02-01 00:00:00.000	00000871	1000	2021	APPRO

[escartefigue]

Du coup, pour répondre au besoin, on n'a nullement besoin de la première table.

Dans la proposition ci-dessous, j'ai simplifié les noms de tables et des colonnes en virant les caractères spéciaux et les noms réservés SQL.
Ce faisant, je n'ai plus besoin de me faire ch*** avec les crochets
J'ai également simplifié le typage des colonnes, par exemple, l'exercice ne contient qu'une année (2020, 2021, 2022...) il est donc inutile et contre performant d'utiliser un datetime, un smallint suffit

Création de la table avec uniquement les colonnes utiles et insertion du jeu de données :

Code SQL :

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
CREATE TABLE TVTE
      (  Exercice    smallint      not null
       , Montant     decimal(9,2)
       , Code_Act    char(10)
       , Source      char(08)
      )
;
insert into TVTE (Exercice, Montant, Code_Act, Source)
values (2020, 0200, 'appro', '00003078')
     , (2021, 7000, 'appro', '00002209')
     , (2021, 2200, 'appro', '00003265')
     , (2021, 0200, 'appro', '00000871')
     , (2022, 0900, 'appro', '00001927')
     , (2022, 0500, 'appro', '00001927')
     , (2020, 0300, 'appro', '00003078')
     , (2022, 0600, 'appro', '00003078')
     , (2021, 1000, 'appro', '00000871')
;

Et la requête :

Code SQL :

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

1
2
3
4
5
6
7
8
9
 
    select VT.Source      
         , VT.Code_Act
         , sum(case when VT.exercice=2020 then VT.Montant end) as A2020
         , sum(case when VT.exercice=2021 then VT.Montant end) as A2021
         , sum(case when VT.exercice=2022 then VT.Montant end) as A2022
    from  TVTE as VT
    group by VT.Source      
           , VT.Code_Act

S'il est possible, pour un code source, de n'avoir aucune valeur sur les exercices 2020, 2021 et 2022, il suffit d'englober cette requête dans une autre d'alias XX par exemple, et de tester where coalesce (XX.A2020, XX.A2021, XX.A2022, 0) > 0.


Voici le résultat obtenu :

Source	Code_Act	A2020	A2021	A2022
00003078	appro	500.00	null	600.00
00002209	appro	null	7000.00	null
00003265	appro	null	2200.00	null
00000871	appro	null	1200.00	null
00001927	appro	null	null	1400.00

[colapis]

Bonjour,

Merci pour la requête. Cependant je ne vois pas à quel moment je récupère ce dont j'ai besoin. Pour rappel je dois récupéré la campagne actuelle sachant que c'est pour une automatisation donc dans la future campagne j'aurais le soucis que je devrais changer. De plus j'ai besoin du n-1 à date c-a-d qu'il me faut à la date d'aujourd'hui mais n-1 (23/09/2021) le CA de la campagne n-1 à la date du 23/09/2021 de ce fait.
C'est pour ça que j'utilise la première table pour récupérer les date de début et de fin de la campagne.

[escartefigue]

OK, du coup il faut bien les deux tables.

Voici donc la création de ces tables et du jeu d'essai :

Code SQL :

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
CREATE TABLE TCAMP
      (  Exercice  smallint        not null   primary key
	   , Dtdeb     date            not null
	   , Dtfin     date            not null
      )
;
CREATE TABLE TVTE
      (  Exercice    smallint      not null
       , Montant     decimal(9,2)
       , Code_Act    char(10)
       , Source      char(08)
      )
;
insert into TCAMP (Exercice, Dtdeb, Dtfin)
values (2020, '2020-07-01', '2021-06-30')
     , (2021, '2021-07-01', '2022-06-30')
     , (2022, '2022-07-01', '2023-06-30')
     , (2023, '2023-07-01', '2024-06-30')  
;
insert into TVTE (Exercice, Montant, Code_Act, Source)
values (2020, 0200, 'appro', '00003078')
     , (2021, 7000, 'appro', '00002209')
     , (2021, 2200, 'appro', '00003265')
     , (2021, 0200, 'appro', '00000871')
     , (2022, 0900, 'appro', '00001927')
     , (2022, 0500, 'appro', '00001927')
     , (2020, 0300, 'appro', '00003078')
     , (2022, 0600, 'appro', '00003078')
     , (2021, 1000, 'appro', '00000871')
;

La nouvelle requête :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select * from (
    select VT.Source      
         , VT.Code_Act
         , sum(case when VT.exercice=CA.exercice - 1 then VT.Montant end) as PREC
         , sum(case when VT.exercice=CA.exercice     then VT.Montant end) as ACTUEL
         , sum(case when VT.exercice=CA.exercice + 1 then VT.Montant end) as SUIV
    from TCAMP       as CA
    inner join  TVTE as VT
       on VT.Exercice between CA.exercice-1 and CA.exercice+1
    where now() between CA.Dtdeb and CA.Dtfin
    group by VT.Source      
           , VT.Code_Act
              ) as XX
where coalesce (XX.PREC, XX.ACTUEL, XX.SUIV, 0) > 0

Et le résultat obtenu :

Source	Code_Act	PREC	ACTUEL	SUIV
00002209	appro	7000.00	null	null
00003265	appro	2200.00	null	null
00000871	appro	1200.00	null	null
00001927	appro	null	1400.00	null
00003078	appro	null	600.00	null

Dans mes tables, la colonne de jointure (que j'ai appelée "exercice") est de même type et de même longueur.
La jointure est donc "sargable" (indexable), les perfs seront ainsi préservées si on crée un index sur cette colonne, même si la table contient des centaines de millions de lignes.
Ce ne sera pas le cas avec vos tables qui ont d'un coté un nvarchar(10) et de l'autre un nvarchar(20).

[colapis]

Bonjour,

Merci pour la requête. Je comprends l'idée du problème de l'index.. J'ai finalement fait une chose un peu moche que je n'aime pas, j'ai passé la campagne en dur et comme par magie on passe de 35 minutes à quelques secondes.
Je suis d'accord pour dire qu'il y a un énormes souci concernant la BDD...

Encore merci pour votre temps, je clos le sujet.