Optimisation Requete SQL avec grosse table

**macsigarev** · 26/11/2012, 11h49

Bonjour à tous,

j'ai une requete SQL sous Mysql sur une grosse table de prés de 10 millions de lignes. ma requete est constituée de 3 union, entre des requetes du type:

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
(SELECT distinct
    DATE_FORMAT(CP.Dateofanalysis,'%M %Y') AS Period,
    IF(CAST(DATE_FORMAT(CP.Dateofanalysis,'%m') AS CHAR)>3,CONCAT(CAST(DATE_FORMAT(CP.Dateofanalysis,'%Y') AS CHAR),'-',CAST(DATE_FORMAT(CP.Dateofanalysis,'%Y') AS CHAR)+1),CONCAT(CAST(DATE_FORMAT(CP.Dateofanalysis,'%Y') AS CHAR)-1,'-',CAST(DATE_FORMAT(CP.Dateofanalysis,'%Y') AS CHAR))) AS FiscalYearInternal,
    CP.Facility,
    CP.Traded_Manufactured,
    0 AS Horizon,
     CP.OrderedLastMonths AS NbLines,
    CP.OrderToDelivery AS PlanningTimeFence,
    CP.Company,
    CP.Division,
    CP.FTActivityCode,
    CP.FTActivityName,
    CP.Warehouse,
    CP.WarehouseName,
    CP.Item,
    CP.ItemDescription,
    CP.AchievementGroup,
    CP.AchievementFacility
 
 
FROM
    Big_table
WHERE CP.Traded_Manufactured IS NOT NULL
AND CP.OrderedLastMonths=1
AND CP.DayInMonth =1
)

Cette requete prend beaucoup de temps à s'executer (surtout qu'il y'a une union de 4 requêtes similaires, il n'y a que les conditions de IF qui changent, les champs extraits sont les memes).

Pouvez-vous m'aider à optimiser cette requête ?

Je vous remercie d'avance

macsigarev

**punkoff** · 26/11/2012, 12h21

Bonjour,

s'il n' y a que le IF qui change alors il vous faut déporter cette opération de mise en page côté applicatif.

Ca vous permettra de gagner 3 table scan (ou index).

Ensuite vérifiez que vous avez bien un index (s'il l'utilise) sur le couple :
DayInMonth, OrderedLastMonths, Traded_Manufactured

**macsigarev** · 26/11/2012, 16h36

Envoyé par punkoff

Bonjour,

s'il n' y a que le IF qui change alors il vous faut déporter cette opération de mise en page côté applicatif.

Ca vous permettra de gagner 3 table scan (ou index).

Ensuite vérifiez que vous avez bien un index (s'il l'utilise) sur le couple :
DayInMonth, OrderedLastMonths, Traded_Manufactured

Merci, en fait j'ai pas bien compris quand vous dites coté applicatif, car c'est une extraction à partir d'un ERP.

De plus, il existe bien des index sur les 3 champs que vous avez spécifié:

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type
F_KPI_Customer_Performance 0 PRIMARY 1 Company A 17 BTREE
F_KPI_Customer_Performance 0 PRIMARY 2 OrderNumber A 17 BTREE
F_KPI_Customer_Performance 0 PRIMARY 3 OrderLineNumber A 17 BTREE
F_KPI_Customer_Performance 0 PRIMARY 4 OrderLineSuffix A 17 BTREE
F_KPI_Customer_Performance 0 PRIMARY 5 Upload_Date A 8745080 BTREE
F_KPI_Customer_Performance 1 Index1 1 Company A 17 BTREE
F_KPI_Customer_Performance 1 Index1 2 Division A 17 YES BTREE
F_KPI_Customer_Performance 1 Index1 3 Facility A 17 BTREE
F_KPI_Customer_Performance 1 Index1 4 DayInMonth A 17 YES BTREE
F_KPI_Customer_Performance 1 Index1 5 DayInWeek A 17 YES BTREE
F_KPI_Customer_Performance 1 Index1 6 LastUpdateRecords A 17 YES BTREE
F_KPI_Customer_Performance 1 Index1 7 OrderNumber A 8745080 BTREE
F_KPI_Customer_Performance 1 Index1 8 OrderLineSuffix A 8745080 BTREE

**punkoff** · 26/11/2012, 16h38

postez la requête en entiere

**CinePhil** · 26/11/2012, 13h54

Code :

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

IF(CAST(DATE_FORMAT(CP.Dateofanalysis,'%m') AS CHAR)>3

CASTer en CHAR et demander de comparer à un chiffre, bizarre !

Si je comprends le sens de la formule, votre condition vérifie si le mois est supérieur à 3. Pourquoi ne pas directement utiliser EXTRACT ( MONTH FROM CP.Dateofanalysis) (en SQL standard) ou bien plus simplement MONTH(CP.Dateofanalysis) (en MySQL) au lieu du CAST ?

Principe à appliquer sur l'ensemble de la ligne de code.

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
IF
(
	MONTH(CP.Dateofanalysis) > 3,
	CONCAT
	(
		YEAR(CP.Dateofanalysis),
		'-',
		YEAR(CP.Dateofanalysis) + 1
	),
	CONCAT
	(
		YEAR(CP.Dateofanalysis) - 1,
		'-',
		YEAR(CP.Dateofanalysis)
	)
) AS FiscalYearInternal

On peut avoir un aperçu des différents IF ?

**macsigarev** · 26/11/2012, 16h40

Envoyé par CinePhil

CASTer en CHAR et demander de comparer à un chiffre, bizarre !

Si je comprends le sens de la formule, votre condition vérifie si le mois est supérieur à 3. Pourquoi ne pas directement utiliser EXTRACT ( MONTH FROM CP.Dateofanalysis) (en SQL standard) ou bien plus simplement MONTH(CP.Dateofanalysis) (en MySQL) au lieu du CAST ?

Principe à appliquer sur l'ensemble de la ligne de code.

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
IF
(
	MONTH(CP.Dateofanalysis) > 3,
	CONCAT
	(
		YEAR(CP.Dateofanalysis),
		'-',
		YEAR(CP.Dateofanalysis) + 1
	),
	CONCAT
	(
		YEAR(CP.Dateofanalysis) - 1,
		'-',
		YEAR(CP.Dateofanalysis)
	)
) AS FiscalYearInternal

On peut avoir un aperçu des différents IF ?

Merci pour la réponse, j'ai appliqué ce que vous avez dit à la 1ere portion de SELECT de ma requete globale et les performances ne ce sont pas amérliorées, voici le code globale de ma requete:

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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
(SELECT  DISTINCT 
        DATE_FORMAT(CP.Dateofanalysis, '%M %Y') AS Period,
        IF(
           CAST(DATE_FORMAT(CP.Dateofanalysis, '%m') AS CHAR) > 3,
           CONCAT(CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR),
                  '-',
                  CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR) + 1),
           CONCAT(CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR) - 1,
                  '-',
                  CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR)))
           AS FiscalYearInternal,
        CP.Facility,
        CP.Traded_Manufactured,
        0 AS Horizon,
 
        CP.OrderedLastMonths AS NbLines,
        CP.OrderToDelivery AS PlanningTimeFence,
 
        CP.Company,
        CP.Division,
        CP.FTActivityCode,
        CP.FTActivityName,
        CP.Warehouse,
        CP.WarehouseName,
        CP.Item,
        CP.ItemDescription,
        CP.AchievementGroup,
        CP.AchievementFacility
   FROM global_table
  WHERE     CP.Traded_Manufactured IS NOT NULL
        AND CP.OrderedLastMonths = 1
        AND CP.DayInMonth = 1) 
UNION
/* Last 12 months current period */
(SELECT  DISTINCT 
        DATE_FORMAT(CP.Dateofanalysis, '%M %Y') AS Period,
        IF(
           CAST(DATE_FORMAT(CP.Dateofanalysis, '%m') AS CHAR) > 3,
           CONCAT(CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR),
                  '-',
                  CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR) + 1),
           CONCAT(
              CAST(
                 DATE_FORMAT(CP.InternalRequestedDeliveryDate, '%Y') AS CHAR)
              - 1,
              '-',
              CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR)))
           AS FiscalYearInternal,
        CP.Facility,
        CP.Traded_Manufactured,
        0 AS Horizon,
 
        CP.OrderedLastMonths AS NbLines,
        CP.OrderToDelivery AS PlanningTimeFence,
 
        CP.Company,
        CP.Division,
        CP.FTActivityCode,
        CP.FTActivityName,
        CP.Warehouse,
        CP.WarehouseName,
        CP.Item,
        CP.ItemDescription,
        CP.AchievementGroup,
        CP.AchievementFacility
   FROM global_table
  WHERE     CP.Traded_Manufactured IS NOT NULL
        AND CP.OrderedLastMonths = 1
        AND CP.LastUpdateRecords = 1
        AND CP.DayInMonth != 1) 
UNION
 
(SELECT  DISTINCT 
        DATE_FORMAT(CP.Dateofanalysis, '%M %Y') AS Period,
        IF(
           CAST(DATE_FORMAT(CP.Dateofanalysis, '%m') AS CHAR) > 3,
           CONCAT(CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR),
                  '-',
                  CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR) + 1),
           CONCAT(
              CAST(
                 DATE_FORMAT(CP.InternalRequestedDeliveryDate, '%Y') AS CHAR)
              - 1,
              '-',
              CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR)))
           AS FiscalYearInternal,
        CP.Facility,
        CP.Traded_Manufactured,
        1 AS Horizon,
 
        CP.OrderedLastMonth AS NbLines,
        CP.OrderToDelivery AS PlanningTimeFence,
        /* Fields for drill-through details */
        CP.Company,
        CP.Division,
        CP.FTActivityCode,
        CP.FTActivityName,
        CP.Warehouse,
        CP.WarehouseName,
        CP.Item,
        CP.ItemDescription,
        CP.AchievementGroup,
        CP.AchievementFacility
   FROM global_table
  WHERE     CP.Traded_Manufactured IS NOT NULL
        AND CP.OrderedLastMonth = 1
        AND CP.DayInMonth = 1) 
UNION
 
(SELECT  DISTINCT 
        DATE_FORMAT(CP.Dateofanalysis, '%M %Y') AS Period,
        IF(
           CAST(DATE_FORMAT(CP.Dateofanalysis, '%m') AS CHAR) > 3,
           CONCAT(CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR),
                  '-',
                  CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR) + 1),
           CONCAT(CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR) - 1,
                  '-',
                  CAST(DATE_FORMAT(CP.Dateofanalysis, '%Y') AS CHAR)))
           AS FiscalYearInternal,
        CP.Facility,
        CP.Traded_Manufactured,
        1 AS Horizon,
 
        CP.OrderedLastMonth AS NbLines,
        CP.OrderToDelivery AS PlanningTimeFence,
 
        CP.Company,
        CP.Division,
        CP.FTActivityCode,
        CP.FTActivityName,
        CP.Warehouse,
        CP.WarehouseName,
        CP.Item,
        CP.ItemDescription,
        CP.AchievementGroup,
        CP.AchievementFacility
   FROM global_table
  WHERE     CP.Traded_Manufactured IS NOT NULL
        AND CP.OrderedLastMonth = 1
        AND CP.LastUpdateRecords = 1
        AND CP.DayInMonth != 1)

**Fred_34** · 26/11/2012, 17h57

- Est-ce normal d'avoir des CP.OrderedLastMonths et des CP.OrderedLastMonth ?

- Je ne vois pas d'index sur les OrderedLastMonthX et Traded_Manufactured

- J'ai l'impression que InternalRequestedDeliveryDate sort du chapeau.

- Il y a FROM global_table au lieu de probablement FROM Customer_Performance AS CP. Le fait de modifier ta requête ne nous aide pas à la simplifier.

- Combien d'enregistrements retourne chaque requête ?

**macsigarev** · 26/11/2012, 18h24

Envoyé par Fred_34

- Est-ce normal d'avoir des CP.OrderedLastMonths et des CP.OrderedLastMonth ?

oui c'est normal, il y'a un champs pour LES mois précédents et un champs pour LE mois précédent.

- Je ne vois pas d'index sur les OrderedLastMonthX et Traded_Manufactured

Pourtant j'ai bien crée les index sur ces champs!

- J'ai l'impression que InternalRequestedDeliveryDate sort du chapeau.

Non il est bien à sa place, en fait c'est également une condition de test sur 2 des requetes.

- Il y a FROM global_table au lieu de probablement FROM Customer_Performance AS CP. Le fait de modifier ta requête ne nous aide pas à la simplifier.

Désolé c'est la même table qui est interrogée dans toutes les requetes.

- Combien d'enregistrements retourne chaque requête ?

Prés de 9 millions environ