Optimisation Requete SQL avec grosse table

Version imprimable

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:

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

26/11/2012, 12h21
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

Citation:

Code:

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

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

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:

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 ?

26/11/2012, 16h36
macsigarev

Citation:

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
26/11/2012, 16h38
punkoff

postez la requête en entiere

Citation:

Envoyé par CinePhil

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

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:

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:

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)

26/11/2012, 17h57
Fred_34

- 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 ?
26/11/2012, 18h24
macsigarev

Citation:

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
27/11/2012, 12h37
Fred_34

Citation:

Envoyé par macsigarev

Prés de 9 millions environ

Quel peut bien être l’intérêt d'une requête qui retourne 9 millions d'enregistrements ?
27/11/2012, 15h33
macsigarev

Citation:

Envoyé par Fred_34

Quel peut bien être l’intérêt d'une requête qui retourne 9 millions d'enregistrements ?

Bonjour,

En fait, c'est pour des rapports sous PALO (outil de reporting sous Excel).
27/11/2012, 15h48
CinePhil

Si tu nous donnes la structure des tables impliquées et l'expression en français du besoin, on pourra peut-être simplifier ta requête parce que si c'est pour envoyer vers Excel, je suppose qu'il y a encore d'autres traitements derrière étant donné que tu ne peux pas envoyer 9 millions de lignes dans Excel !