Optimisation d'une requête

**colapis** · 22/09/2022, 09h06

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** · 22/09/2022, 09h18

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** · 22/09/2022, 10h52

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** · 22/09/2022, 11h18

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** · 22/09/2022, 13h48

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** · 22/09/2022, 14h21

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

Optimisation d'une requête

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager