Discussion :

[stevenP]

Bonjour,

J'ai développé une algorithme PHP qui permet de calculer les données critiques d'un portefeuille de "production en cours".

Mon problème, c'est que je ne sais pas comment envoyer ces données simplement avec un simple INSERT en mysql..
Mes variables sont remplies au fur et à mesure du foreach PHP, mais jamais après un nombre "fixe" de ligne.

Actuellement j'utilise ces requêtes, qui fonctionnent bien, mais... mettent un temps fou..

Code php :

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
//Requêtes de stockage du resultat analyse
$req_gen2 = "INSERT INTO table_analyse (m_pres_tech, color_m_pres_tech, ar_opus, color_ar_opus, rol, ros, ui, color_ui, noeud_b, color_noeud_b, rdv_poc, color_rdv_poc, 
sign_poc, color_sign_poc, tvx_client, color_tvx_client, poi, color_poi, materiel, color_materiel, syno, color_syno, routage, color_routage, vol_ec_fo, color_vol_ec_fo, saisie_as, color_saisie_as, cuid)
SELECT * FROM (SELECT '$num_prest_tech' as m_pres_tech,'$color_m_pres_tech' as color_m_pres_tech,'$ar_opus' as ar_opus,'$color_ar_opus' as color_ar_opus,'$rol' as rol,'$ros' as ros,'$ui' as ui,'$color_ui' as color_ui,''as noeud_b,'' as color_noeud_b
,'' as rdv_poc,'' as color_rdv_poc,'' as sign_poc,'' as color_sign_poc,'' as tvx_client,'' as color_tvx_client,'' as poi,'' as color_poi,'' as materiel,'' as color_materiel
,'' as syno,'' as color_syno,'' as routage,'' as color_routage,'' as vol_ec_fo,'' as color_vol_ec_fo,'' as saisie_as,'' as color_saisie_as,'$cellule_cuid' as cuid) AS tmp
WHERE NOT EXISTS (
    SELECT m_pres_tech FROM table_analyse WHERE m_pres_tech = '$num_prest_tech' and cuid = '$cellule_cuid'
) LIMIT 1;";
// echo $req_gen2."<br>";
$query=mysqli_query($mysqli,$req_gen2);
 
$query_update = "UPDATE table_analyse
    SET noeud_b = (case when '$noeud_b' = '' then noeud_b else '$noeud_b' end),
        color_noeud_b = (case when '$color_noeud_b' = '' then color_noeud_b else '$color_noeud_b' end),
        rdv_poc = (case when '$rdv_poc' = '' then rdv_poc else '$rdv_poc' end),
        color_rdv_poc = (case when '$color_rdvpoc' = '' then color_rdv_poc else '$color_rdvpoc' end),
        sign_poc = (case when '$sign_poc' = '' then sign_poc else '$sign_poc' end),
        color_sign_poc = (case when '$color_signpoc' = '' then color_sign_poc else '$color_signpoc' end),
		tvx_client = (case when '$tvx_client' = '' then tvx_client else '$tvx_client' end),
		color_tvx_client = (case when '$color_tvx' = '' then color_tvx_client else '$color_tvx' end),
		poi = (case when '$etude_poi' = '' then poi else '$etude_poi' end),
		color_poi = (case when '$color_poi' = '' then color_poi else '$color_poi' end),
		materiel = (case when '$recupmat' = '' then materiel else '$recupmat' end),
		color_materiel = (case when '$color_materiel' = '' then color_materiel else '$color_materiel' end),
		syno = (case when '$syno' = '' then syno else '$syno' end),
		color_syno = (case when '$color_syno' = '' then color_syno else '$color_syno' end),
		routage = (case when '$routage' = '' then routage else '$routage' end),
		color_routage = (case when '$color_routage' = '' then color_routage else '$color_routage' end),
		vol_ec_fo = (case when '$volecfo' = '' then vol_ec_fo else '$volecfo' end),
		color_vol_ec_fo = (case when '$color_vol' = '' then color_vol_ec_fo else '$color_vol' end),
		saisie_as = (case when '$saisieas' = '' then saisie_as else '$saisieas' end),
		color_saisie_as = (case when '$color_saisieas' = '' then color_saisie_as else '$color_saisieas' end)
    WHERE m_pres_tech = '$num_prest_tech';";
// echo $query_update."<br>";
$query=mysqli_query($mysqli,$query_update);

La différence est hallucinante quand je commente les variables $query... on passe de 3s à 120s de traitement.

Quelqu'un saurait m'aider à optimiser celà ?

Merci d'avance !!
Steven

[al1_24]

Tes requêtes ne semblent pourtant pas d'une grande complexité.
Toutefois je m'interroge : est-ce normal de ne pas utiliser la même clé pour l'INSERT et pour l'UPDATE ?

[stevenP]

Alors si tu parles des clauses WHERE, effectivement j'ai fait une erreur..
Les deux devraient être identiques.

EN fait, dans le principe, ma table sources contient des données sous la forme :

Num_Prod1|Tache1| et plein de colonnes (une vingtaine) avec des infos...
Num_Prod1|Tache2|
Num_Prod1|Tache3|
Num_Prod1|Tache4|
Num_Prod1|Tache5|
Num_Prod2|Tache1|
Num_Prod2|Tache3|
Num_Prod2|Tache6|
Num_Prod2|Tache7|
Num_Prod2|Tache8|
Num_Prod2|Tache9|

Et mon algorithme doit traiter chaque ligne et envoyer le résultat vers une table destination, avec un seul "Num_ProdX" par ligne, sous la forme :

Num_Prod1|Variable_calculée1|Variable_calculée2|Variable_calculée3|....
Num_Prod2|Variable_calculée1|Variable_calculée2|Variable_calculée3|....

=> D'où mes requêtes SQL.
Le INSERT pour créer la ligne au premier balayage (mais qui s’exécute quand même à chaque fois alors qu'il n'y a plus besoin) et le UPDATE pour mettre à jour à chaque passage les variables calculées qui ont été remplie (sinon elles sont vides).
J'imagine qu'on peut faire beaucoup plus simple, mais je ne vois pas comment..

[Nazoïde]

pour ne pas créer la première ligne plusieurs fois tu peux contrôler sont exécution en faisant par exemple.

Code php :

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
$insert_table_analyse = 1;
if ($test == 1) {
//Requêtes de stockage du resultat analyse
$req_gen2 = "INSERT INTO table_analyse 
    (m_pres_tech, color_m_pres_tech, ar_opus, color_ar_opus, rol, ros, ui, color_ui, noeud_b, color_noeud_b, rdv_poc, color_rdv_poc, 
sign_poc, color_sign_poc, tvx_client, color_tvx_client, poi, color_poi, materiel, color_materiel, syno, color_syno, routage, color_routage, 
vol_ec_fo, color_vol_ec_fo, saisie_as, color_saisie_as, cuid)
SELECT * FROM (SELECT '$num_prest_tech' as m_pres_tech,'$color_m_pres_tech' as color_m_pres_tech,'$ar_opus' as ar_opus, 
        '$color_ar_opus' as color_ar_opus,'$rol' as rol,'$ros' as ros,'$ui' as ui,'$color_ui' as color_ui,''as noeud_b,'' as color_noeud_b,
'' as rdv_poc,'' as color_rdv_poc,'' as sign_poc,'' as color_sign_poc,'' as tvx_client,'' as color_tvx_client,'' as poi,
'' as color_poi,'' as materiel,'' as color_materiel
,'' as syno,'' as color_syno,'' as routage,'' as color_routage,'' as vol_ec_fo,'' as color_vol_ec_fo,'' as saisie_as,
'' as color_saisie_as,'$cellule_cuid' as cuid) AS tmp
WHERE NOT EXISTS (
    SELECT m_pres_tech FROM table_analyse WHERE m_pres_tech = '$num_prest_tech' and cuid = '$cellule_cuid'
) LIMIT 1;";
// echo $req_gen2."<br>";
$query=mysqli_query($mysqli,$req_gen2);
$insert_table_analyse ++; 
}

Tes requêtes sont-elles dans une boucle? si oui, poste le code.

[stevenP]

Le problème que j'ai c'est que je dois quand même faire l'insert, à chaque fois que mon "numprodX" (pour reprendre mon exemple) change.
Je pourrais faire un if ( $var != $num_prest_tech) mais c'est lourd non ?

mon code est assez conséquent... je poste le fichier contenant l'algorithme de calcul
new_controleur.php

[Nazoïde]

après le première insertion, tu pourrais vérifier si le numprodX est le même. si oui, pas d'insertion; sinon, insertion.

[stevenP]

après le première insertion, tu pourrais vérifier si le numprodX est le même. si oui, pas d'insertion; sinon, insertion.

L'idée n'est pas bête, je pense que ça allegerait le traitement.

Et pour la partie UPDATE, construire la requête dynamiquement en PHP en testant si la variable est vide ou non, cela ne serait pas plus rapide que la requête "lourde" actuelle ?
Avez vous un exemple de construction dynamique de requête UPDATE?

ou alors, créer une variable php tableau multidimensionnelle avec ma table de resultat, et balancer le contenu après ma boucle de calcul dans la table destination avec des simples INSERT ?

J'suis un peu paumé niveau "optimisation"

[Nazoïde]

Avez vous un exemple de construction dynamique de requête UPDATE?

ou alors, créer une variable php tableau multidimensionnelle avec ma table de resultat, et balancer le contenu après ma boucle de calcul dans la table destination avec des simples INSERT ?

Que veux-tu vraiment dire, soit plus explicite.

[Artemus24]

Salut stevenP.

Prenons l'exemple de votre requête d'insertion suivante :

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
INSERT INTO table_analyse (m_pres_tech,  color_m_pres_tech,  ar_opus,     color_ar_opus,     rol,      ros,
                           ui,           color_ui,           noeud_b,     color_noeud_b,     rdv_poc,  color_rdv_poc,
                           sign_poc,     color_sign_poc,     tvx_client,  color_tvx_client,  poi,      color_poi,
                           materiel,     color_materiel,     syno,        color_syno,        routage,  color_routage,
                           vol_ec_fo,    color_vol_ec_fo,    saisie_as,   color_saisie_as,   cuid)
   SELECT *
   FROM (SELECT '$num_prest_tech'    as m_pres_tech,
                '$color_m_pres_tech' as color_m_pres_tech,
                '$ar_opus'           as ar_opus,
                '$color_ar_opus'     as color_ar_opus,
                '$rol' as rol,'$ros' as ros,
                '$ui'                as ui,
                '$color_ui'          as color_ui,
                ''                   as noeud_b,
                ''                   as color_noeud_b,
                ''                   as rdv_poc,
                ''                   as color_rdv_poc,
                ''                   as sign_poc,
                ''                   as color_sign_poc,
                ''                   as tvx_client,
                ''                   as color_tvx_client,
                ''                   as poi,
                ''                   as color_poi,
                ''                   as materiel,
                ''                   as color_materiel,
                ''                   as syno,
                ''                   as color_syno,
                ''                   as routage,
                ''                   as color_routage,
                ''                   as vol_ec_fo,
                ''                   as color_vol_ec_fo,
                ''                   as saisie_as,
                ''                   as color_saisie_as,
                '$cellule_cuid'      as cuid) AS tmp
WHERE NOT EXISTS (  SELECT  m_pres_tech
                      FROM  table_analyse
                     WHERE  m_pres_tech = '$num_prest_tech'
                       and  cuid        = '$cellule_cuid'
) LIMIT 1;

Vous pouvez alléger votre insertion sur les points suivants :

1) vous avez vingt colonnes qui sont renseignées à vide.
Pourquoi les mettez-vous dans votre requête ?

Dans la table "table_analyse", pour ces colonnes, il suffit de mettre pour ces colonnes de types varchar, "NOT NULL DEFAULT ''".
Ainsi en ne faisant aucune référence à ces colonnes, lors de l'insertion, elles auront la valeur par défaut, soit vide.

2) vous desirez tester l'existence de la ligne avant de faire l'insertion.
C'est très bien, mais totalment inutile car dans le cas où cette ligne existe, vous faites un "update".
Comment résoudre ce problème ?

Au lieu d'utiliser en mysql un insert quand la ligne n'existe pas et un update quant elle existe, vous utilisez seulement la syntaxe de l'insert, mais au lieu de mettre "insert", vous mettez à la place "replace".
--> https://dev.mysql.com/doc/refman/5.7/en/replace.html

3) pour que le test d'existence sur vos deux colonnes fonctionnent correctement, vous devez créer un "unique index" sur (m_pres_tech ; cuid).
Ce couple doit être unique dans votre table.
Si la valeur de ce couple n'existe pas alors c'est une insertion, sinon c'est une mise à jour.

4) à quoi vous sert le "limit 1" ? D'après ce que j'ai compris, vous insérez une saisie.
A priori, vous avez une et une seule ligne ! Donc "limit 1" est superflue !

La syntaxe de l'insert et de l'update devient ceci :

Code :

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

1
2
3
4
REPLACE INTO table_analyse (m_pres_tech, color_m_pres_tech, ar_opus, color_ar_opus, rol, ros, ui, color_ui, cuid)
                    values ('$num_prest_tech', '$color_m_pres_tech', '$ar_opus',
                            '$color_ar_opus'   '$rol',               '$ros'
                            '$ui',             '$color_ui',          '$cellule_cuid');

Ce qui est beaucoup plus simple, non ?

@+

[Artemus24]

Salut stevenP.

Il y a aussi cette forme d'écriture qui permet, comme vous le désirez, de mettre à jour chaque colonne.
Si la valeur est vide, on laisse l'ancienne valeur, sinon on l'écrase par la nouvelle.
Voici un exemple de ce fonctionnement :

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `clef`  integer unsigned  NOT NULL primary key,
  `col1`  varchar(255)      NOT NULL DEFAULT '',
  `col2`  varchar(255)      NOT NULL DEFAULT '',
  `col3`  varchar(255)      NOT NULL DEFAULT '',
  `col4`  varchar(255)      NOT NULL DEFAULT '',
  `col5`  varchar(255)      NOT NULL DEFAULT ''
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert INTO `test` (`clef`,`col1`,`col2`,`col3`,`col4`,`col5`) VALUES (1, '',  'c', 'e', '',  'h')
  on duplicate key update col1 = case when values(col1) = '' then col1 else values(col1) end,
                          col2 = case when values(col2) = '' then col2 else values(col2) end,
                          col3 = case when values(col3) = '' then col3 else values(col3) end,
                          col4 = case when values(col4) = '' then col4 else values(col4) end,
                          col5 = case when values(col5) = '' then col5 else values(col5) end
--------------
 
--------------
insert INTO `test` (`clef`,`col1`,`col2`,`col3`,`col4`,`col5`) VALUES (1, 'a', 'd', '',  'g', '')
  on duplicate key update col1 = case when values(col1) = '' then col1 else values(col1) end,
                          col2 = case when values(col2) = '' then col2 else values(col2) end,
                          col3 = case when values(col3) = '' then col3 else values(col3) end,
                          col4 = case when values(col4) = '' then col4 else values(col4) end,
                          col5 = case when values(col5) = '' then col5 else values(col5) end
--------------
 
--------------
insert INTO `test` (`clef`,`col1`,`col2`,`col3`,`col4`,`col5`) VALUES (1, 'b', '',  'f', '',  'i')
  on duplicate key update col1 = case when values(col1) = '' then col1 else values(col1) end,
                          col2 = case when values(col2) = '' then col2 else values(col2) end,
                          col3 = case when values(col3) = '' then col3 else values(col3) end,
                          col4 = case when values(col4) = '' then col4 else values(col4) end,
                          col5 = case when values(col5) = '' then col5 else values(col5) end
--------------
 
--------------
insert INTO `test` (`clef`,`col1`,`col2`,`col3`,`col4`,`col5`) VALUES (2, 'j', '',  '',  'm', 'n')
  on duplicate key update col1 = case when values(col1) = '' then col1 else values(col1) end,
                          col2 = case when values(col2) = '' then col2 else values(col2) end,
                          col3 = case when values(col3) = '' then col3 else values(col3) end,
                          col4 = case when values(col4) = '' then col4 else values(col4) end,
                          col5 = case when values(col5) = '' then col5 else values(col5) end
--------------
 
--------------
insert INTO `test` (`clef`,`col1`,`col2`,`col3`,`col4`,`col5`) VALUES (2, '',  '',  'l', '',  '')
  on duplicate key update col1 = case when values(col1) = '' then col1 else values(col1) end,
                          col2 = case when values(col2) = '' then col2 else values(col2) end,
                          col3 = case when values(col3) = '' then col3 else values(col3) end,
                          col4 = case when values(col4) = '' then col4 else values(col4) end,
                          col5 = case when values(col5) = '' then col5 else values(col5) end
--------------
 
--------------
insert INTO `test` (`clef`,`col1`,`col2`,`col3`,`col4`,`col5`) VALUES (2, '',  'k', '',  '',  'o')
  on duplicate key update col1 = case when values(col1) = '' then col1 else values(col1) end,
                          col2 = case when values(col2) = '' then col2 else values(col2) end,
                          col3 = case when values(col3) = '' then col3 else values(col3) end,
                          col4 = case when values(col4) = '' then col4 else values(col4) end,
                          col5 = case when values(col5) = '' then col5 else values(col5) end
--------------
 
--------------
select * from `test`
--------------
 
+------+------+------+------+------+------+
| clef | col1 | col2 | col3 | col4 | col5 |
+------+------+------+------+------+------+
|    1 | b    | d    | f    | g    | i    |
|    2 | j    | k    | l    | m    | o    |
+------+------+------+------+------+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Cela permet de mieux gérer l'insertion et la duplication de la clef, autrement dit la mise à jour.
L'introduction du case permet, comme vous le faites déjà, de gérer le vide.

@+