Tables temporaires et usage de la mémoire

**xounet** · 29/03/2023, 09h47

Bonjour,

J'utilise très souvent de multiples tables temporaires pour effectuer des calculs sur une même table principalement à cause du fait qu'il n'est pas possible d'utiliser deux fois la même table temporaire dans une requête (par exemple pour une mise à jour d'une colonne en fonction des valeurs d'autres colonnes). Mais cela devient vite problématique pour des tables avec plusieurs dizaines de milliers de lignes (la table ne fait qu'une dizaine de Mo et est construite depuis une autre table non temporaire de quelques Go), car la duplication de la table met un temps infini et semble parfois impossible. J'ai 24 Go de mémoire vive.
Une première solution trouvée est d'augmenter la taille des tables temporaires à 4 Go :
tmp_table_size
temptable_max_ram
max_heap_table_size

Mais cela ne suffit toujours pas Mysql met très très longtemps à dupliquer les tables temporaires lorsque les tables dépassent certains seuils (lesquels?), comment éviter ce problème ?

**escartefigue** · 29/03/2023, 10h20

Bonjour,

Pouvez-vous donner des cas concrets de vos utilisations de tables temporaires ?
Peut-être que dans votre cas, plutôt que de dupliquer les données dans une table temporaire, vous pourriez utiliser des CTE (sous réserve d'avoir une version 8 de MySQL bien sûr).

**xounet** · 29/03/2023, 12h27

Bonjour,

J'ai essayé "WITH ..." c'est le même problème, j'ai créé un sujet il y a plusieurs mois, il n'y a pas de solution. WITH sert à utiliser des tables créées à la volée ou des résultats de requêtes pour résumer, mais ne permet pas de contourner le problème des tables temporaires.
C'est trop compliqué de tout "dumper sur le forum" je cherche surtout des idées plus qu'une requête.
Le principe est simple je créé une table temporaire TEMP1 à partir d'une table source (réelle comportant des millions d'enregistrements).
Je modifie cette table TEMP1 créée en ajoutant des colonnes construites/calculées à partir des autres colonnes de cette même table (donc j'utilise 2 fois la table temporaire TEMP1 dans une requête update c'est pour cela que je duplique les tables temporaires).
Cela fonctionne très bien sauf à partir d'une certaine taille (peut être 100000 lignes et 5 colonnes) où cela devient tellement long (plusieurs minutes) que cela devient presque impossible. En augmentant la taille des tables temporaires ça fonctionne mieux mais avec des valeurs démesurées : 4Go !!

**SQLpro** · 29/03/2023, 15h08

MySQL est très mauvais dans la gestion des tables temporaires qu'il considère comme des tables en mémoire... Dans un système comme SQL Server il existe une base de données systèmes particulière (tempdb) spécialement organisée pour la gestion des objets temporaires afin de ne pas trop encombrer le cache. Dans oracle, même genre de chose sauf qu'à l'origine Oracle n'est pas multibase, et donc c'est dans un espace de stockage particulier que cela se fait....

Bref deux solutions :
1) changer votre stratégie et utiliser des sous requête par exemple avec des WITH
2) changer de SGBDR !
Pour ce denier cas, vous avez une version gratuite de SQL Server, a ne pas utiliser en production qui est la version developper, que vous pouvez télécharger ici :
https://go.microsoft.com/fwlink/p/?l...-fr&country=fr

A +

**Artemus24** · 29/03/2023, 23h39

Salut à tous.

Sans exemple, il est difficile de vous répondre.
Je ne comprends même pas pourquoi vous faites l'usage de tables temporaires pour effectuer des calculs.
La solution n'est pas d'augmenter l'espace de stockage mais de revoir le traitement que vous appliquez.

Comme le dit Escartefigue, donnez nous un cas concret.

Cordialement.
Artemus24.
@+

**xounet** · 31/03/2023, 09h45

Bonjour à tous et merci pour vos retours.

En essayant de rester simple pour faciliter les échanges voici typiquement le type de requêtes que je réalise sur un très grand nombre de lignes :

Je pars d'une table 'table1' possédant différentes colonnes de valeurs numériques dont test 2.
Voici un exemple sur lequel je m'entraîne (faut pas chercher à comprendre ce qui peut paraître inutile ) :

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
-- Structure de la table `table1`
--
 
CREATE TABLE `table1` (
  `num_ligne` int NOT NULL,
  `date` datetime DEFAULT NULL,
  `test` decimal(10,1) DEFAULT NULL COMMENT 'ceci est un commentaire',
  `test2` decimal(65,30) DEFAULT NULL COMMENT 'ceci est un commentaire',
  `aberrant` tinyint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
 
--
-- Déchargement des données de la table `table1`
--
 
INSERT INTO `table1` (`num_ligne`, `date`, `test`, `test2`, `aberrant`) VALUES
(1, '2022-01-01 00:00:00', 4.0, NULL, NULL),
(2, '2022-01-05 00:00:00', 4.0, NULL, NULL),
(3, '2022-01-08 00:00:00', 489.0, 76.000000078974698400000000000000, NULL),
(4, '2022-01-10 10:54:03', -456.0, -500.000000000000000000000000000000, NULL),
(5, '2022-01-15 00:00:00', 7445.0, NULL, NULL),
(6, '2022-01-17 00:00:00', 45.0, 325.046860000000000000000000000000, NULL),
(7, '2022-01-19 00:00:00', -4.0, NULL, NULL),
(8, '2022-01-20 00:00:00', -5000.0, 164.000000000000000000000000000000, NULL),
(9, '2022-01-22 00:00:00', -4.0, 5.000000000000000000000000000000, NULL),
(10, '2022-01-23 00:00:00', 78.0, -30.000000000000000000000000000000, NULL),
(11, '2022-01-24 00:00:00', NULL, 4999.000000000000000000000000000000, NULL),
(12, '2022-01-25 00:00:00', 7.0, 289.000000000000000000000000000000, NULL),
(13, '2022-01-26 00:00:00', NULL, 456.000000000000000000000000000000, NULL),
(14, '2022-01-28 00:00:00', 78.0, NULL, NULL),
(15, '2022-01-30 00:00:00', 12600.0, NULL, NULL);
 
--
-- Index pour les tables déchargées
--
 
--
-- Index pour la table `table1`
--
ALTER TABLE `table1`
  ADD PRIMARY KEY (`num_ligne`),
  ADD UNIQUE KEY `test num ligne` (`test`,`num_ligne`);
 
--
-- AUTO_INCREMENT pour les tables déchargées
--
 
--
-- AUTO_INCREMENT pour la table `table1`
--
ALTER TABLE `table1`
  MODIFY `num_ligne` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;
COMMIT;

Voici ce que je cherche à faire par exemple (ici on remplace les permières lignes NULL par la première valeur NON NULL trouvée dans l'ordre de classement déterminé ) :

Code :

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

1
2
3
4
5
6
CREATE TEMPORARY TABLE tt LIKE table1 ;
INSERT INTO tt SELECT * FROM table1  ;
#WITH debut as (SELECT test2 as valeur , (FIRST_VALUE(num_ligne) OVER ( ORDER BY num_ligne ASC) ) as num_ligne FROM tt WHERE test2 IS NOT NULL LIMIT 1)
CREATE TEMPORARY TABLE IF NOT EXISTS debut (SELECT test2 as valeur , (FIRST_VALUE(num_ligne) OVER ( ORDER BY num_ligne ASC) ) as num_ligne FROM tt WHERE test2 IS NOT NULL LIMIT 1);
UPDATE tt INNER JOIN debut ON tt.num_ligne<debut.num_ligne SET tt.test2=debut.valeur;
SELECT * FROM tt

Si vous essayez de décommenter la ligne avec la CTE "WITH..." mysql ne peut pas exécuter la requête.
En soit ce n'est pas forcément important car une CTE est presque une table temporaire, donc ça ne change pas grand chose (une table temporaire est sûrement meilleure).

Si table1 est très très grand alors on restreint 'tt' avec WHERE..., la création de table ne dure qu'une fraction de seconde mais dès qu'on fait des requêtes un peu plus compliquées, c'est long à exécuter si tt dépasse les 100000 lignes par exemple , ce qui laisse penser (je suppose) que mysql cherche à stocker les données sur le disque au lieu de la mémoire vive.

Autre problème : si je remplace fonction window FIRST VALUE par une autre requête la table mise à jour tt passe de 4Mo à 32ko d'après phpmyadmin !!! et apparemment les valeurs sont identiques !!
Si vous avez des idées pour expliquer ceci merci d'avance !

voici la modification qui génère ceci (seule modification : table debut) :

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

1
2
3
4
5
6
7
CREATE TEMPORARY TABLE tt LIKE table1 ; INSERT INTO tt SELECT * FROM table1 ; #WITH debut as (SELECT test2 as valeur , (FIRST_VALUE(num_ligne) OVER ( ORDER BY num_ligne ASC) ) as num_ligne FROM tt WHERE test2 IS NOT NULL LIMIT 1) #CREATE TEMPORARY TABLE IF NOT EXISTS debut (SELECT test2 as valeur , (FIRST_VALUE(num_ligne) OVER ( ORDER BY num_ligne ASC) ) as num_ligne FROM tt WHERE test2 IS NOT NULL LIMIT 1); CREATE TEMPORARY TABLE IF NOT EXISTS debut (SELECT test2 as valeur , num_ligne FROM tt WHERE test2 IS NOT NULL ORDER BY num_ligne ASC LIMIT 1); UPDATE tt INNER JOIN debut ON tt.num_ligne<debut.num_ligne SET tt.test2=debut.valeur; SELECT * FROM tt

=> ceci devait être un bug <=

**Artemus24** · 31/03/2023, 14h50

Salut à tous.

Dans quel SGBDR êtes-vous MariabDB ou Mysql ? Précisez aussi la version.

Je vais traiter la solution pour MySql 8.0.

Envoyé par xounet

Voici ce que je cherche à faire par exemple (ici on remplace les permières lignes NULL par la première valeur NON NULL trouvée dans l'ordre de classement déterminé ) :

Si j'ai bien compris, vous cherchez dans votre table, la première ligne dont la colonne "test2" ne soit pas marquée à NULL.
Vous récupérez cette valeur que vous allez ensuite appliquer à la même colonne pour toutes celles qui sont marquées à NULL mais inférieur strictement à sa clef.

Puis vous recommencez afin de remplir tous les valeurs ayant été marquées à NULL.

Mauvaise nouvelle, MySql n'autorise pas une requête "update" sur la même table.
Vous obtiendrez ceci : "ERROR 1093 (HY000) at line 65: You can't specify target table 't1' for update in FROM clause".

La solution consiste à passer par une procédure stockée que voici :

Code mysql :

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
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `num_ligne` integer unsigned NOT NULL auto_increment primary key,
  `date`      datetime             NULL DEFAULT NULL,
  `test`      decimal(10,1)        NULL DEFAULT NULL,
  `test2`     decimal(65,30)       NULL DEFAULT NULL,
  `aberrant`  tinyint              NULL DEFAULT NULL,
  UNIQUE KEY `idx1` (`test`,`num_ligne`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`date`,`test`,`test2`,`aberrant`) VALUES
('2022-01-01 00:00:00',     4.0,                                NULL, NULL),
('2022-01-05 00:00:00',     4.0,                                NULL, NULL),
('2022-01-08 00:00:00',   489.0,   76.000000078974698400000000000000, NULL),
('2022-01-10 10:54:03',  -456.0, -500.000000000000000000000000000000, NULL),
('2022-01-15 00:00:00',  7445.0,                                NULL, NULL),
('2022-01-17 00:00:00',    45.0,  325.046860000000000000000000000000, NULL),
('2022-01-19 00:00:00',    -4.0,                                NULL, NULL),
('2022-01-20 00:00:00', -5000.0,  164.000000000000000000000000000000, NULL),
('2022-01-22 00:00:00',    -4.0,    5.000000000000000000000000000000, NULL),
('2022-01-23 00:00:00',    78.0,  -30.000000000000000000000000000000, NULL),
('2022-01-24 00:00:00',    NULL, 4999.000000000000000000000000000000, NULL),
('2022-01-25 00:00:00',     7.0,  289.000000000000000000000000000000, NULL),
('2022-01-26 00:00:00',    NULL,  456.000000000000000000000000000000, NULL),
('2022-01-28 00:00:00',    78.0,                                NULL, NULL),
('2022-01-30 00:00:00', 12600.0,                                NULL, NULL)
--------------
 
--------------
select * from `test`
--------------
 
+-----------+---------------------+---------+-------------------------------------+----------+
| num_ligne | date                | test    | test2                               | aberrant |
+-----------+---------------------+---------+-------------------------------------+----------+
|         1 | 2022-01-01 00:00:00 |     4.0 |                                NULL |     NULL |
|         2 | 2022-01-05 00:00:00 |     4.0 |                                NULL |     NULL |
|         3 | 2022-01-08 00:00:00 |   489.0 |   76.000000078974698400000000000000 |     NULL |
|         4 | 2022-01-10 10:54:03 |  -456.0 | -500.000000000000000000000000000000 |     NULL |
|         5 | 2022-01-15 00:00:00 |  7445.0 |                                NULL |     NULL |
|         6 | 2022-01-17 00:00:00 |    45.0 |  325.046860000000000000000000000000 |     NULL |
|         7 | 2022-01-19 00:00:00 |    -4.0 |                                NULL |     NULL |
|         8 | 2022-01-20 00:00:00 | -5000.0 |  164.000000000000000000000000000000 |     NULL |
|         9 | 2022-01-22 00:00:00 |    -4.0 |    5.000000000000000000000000000000 |     NULL |
|        10 | 2022-01-23 00:00:00 |    78.0 |  -30.000000000000000000000000000000 |     NULL |
|        11 | 2022-01-24 00:00:00 |    NULL | 4999.000000000000000000000000000000 |     NULL |
|        12 | 2022-01-25 00:00:00 |     7.0 |  289.000000000000000000000000000000 |     NULL |
|        13 | 2022-01-26 00:00:00 |    NULL |  456.000000000000000000000000000000 |     NULL |
|        14 | 2022-01-28 00:00:00 |    78.0 |                                NULL |     NULL |
|        15 | 2022-01-30 00:00:00 | 12600.0 |                                NULL |     NULL |
+-----------+---------------------+---------+-------------------------------------+----------+
--------------
DROP PROCEDURE IF EXISTS `remplir`
--------------
 
--------------
CREATE PROCEDURE `remplir`()
BEGIN
  DECLARE _fin  INTEGER        DEFAULT 1;
  DECLARE _date DATETIME       DEFAULT NULL;
  DECLARE _val  DECIMAL(65,30) DEFAULT NULL;
 
  DECLARE _tab CURSOR FOR SELECT `date`, `test2` from `test` where `test2` is not NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
 
   OPEN _tab;
  FETCH _tab INTO _date, _val;
 
  WHILE (_fin)
  DO
    UPDATE `test` set `test2` = _val where `date` < _date and `test2` is null;
    FETCH _tab INTO _date, _val;
    COMMIT;
END WHILE;
 
CLOSE _tab;
END
--------------
 
--------------
COMMIT
--------------
 
--------------
call `remplir`()
--------------
 
--------------
select * from `test`
--------------
 
+-----------+---------------------+---------+-------------------------------------+----------+
| num_ligne | date                | test    | test2                               | aberrant |
+-----------+---------------------+---------+-------------------------------------+----------+
|         1 | 2022-01-01 00:00:00 |     4.0 |   76.000000078974698400000000000000 |     NULL |
|         2 | 2022-01-05 00:00:00 |     4.0 |   76.000000078974698400000000000000 |     NULL |
|         3 | 2022-01-08 00:00:00 |   489.0 |   76.000000078974698400000000000000 |     NULL |
|         4 | 2022-01-10 10:54:03 |  -456.0 | -500.000000000000000000000000000000 |     NULL |
|         5 | 2022-01-15 00:00:00 |  7445.0 |  325.046860000000000000000000000000 |     NULL |
|         6 | 2022-01-17 00:00:00 |    45.0 |  325.046860000000000000000000000000 |     NULL |
|         7 | 2022-01-19 00:00:00 |    -4.0 |  164.000000000000000000000000000000 |     NULL |
|         8 | 2022-01-20 00:00:00 | -5000.0 |  164.000000000000000000000000000000 |     NULL |
|         9 | 2022-01-22 00:00:00 |    -4.0 |    5.000000000000000000000000000000 |     NULL |
|        10 | 2022-01-23 00:00:00 |    78.0 |  -30.000000000000000000000000000000 |     NULL |
|        11 | 2022-01-24 00:00:00 |    NULL | 4999.000000000000000000000000000000 |     NULL |
|        12 | 2022-01-25 00:00:00 |     7.0 |  289.000000000000000000000000000000 |     NULL |
|        13 | 2022-01-26 00:00:00 |    NULL |  456.000000000000000000000000000000 |     NULL |
|        14 | 2022-01-28 00:00:00 |    78.0 |                                NULL |     NULL |
|        15 | 2022-01-30 00:00:00 | 12600.0 |                                NULL |     NULL |
+-----------+---------------------+---------+-------------------------------------+----------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Ou bien en utilisant une table dérivée :

Code mysql :

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
--------------
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 IF NOT EXISTS `test`
( `id`   integer unsigned NOT NULL auto_increment primary key,
  `date` date             NOT NULL,
  `val`  decimal (5, 2)       NULL DEFAULT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`id`,`date`,`val`) values
  ( 1,'2023-01-01',NULL),( 2,'2023-01-02', 5.0),( 3,'2023-01-03',NULL),( 4,'2023-01-04',NULL),
  ( 5,'2023-02-01', 7.0),( 6,'2023-02-02',NULL),( 7,'2023-02-03',NULL),( 8,'2023-02-04',NULL),
  ( 9,'2023-03-01', 8.0),(10,'2023-03-02',NULL),(11,'2023-03-03',NULL),(12,'2023-03-04', 9.0),
  (13,'2023-04-01',NULL),(14,'2023-04-02',NULL),(15,'2023-04-03',12.0),(16,'2023-04-04',NULL)
--------------
 
--------------
select * from test
--------------
 
+----+------------+-------+
| id | date       | val   |
+----+------------+-------+
|  1 | 2023-01-01 |  NULL |
|  2 | 2023-01-02 |  5.00 |
|  3 | 2023-01-03 |  NULL |
|  4 | 2023-01-04 |  NULL |
|  5 | 2023-02-01 |  7.00 |
|  6 | 2023-02-02 |  NULL |
|  7 | 2023-02-03 |  NULL |
|  8 | 2023-02-04 |  NULL |
|  9 | 2023-03-01 |  8.00 |
| 10 | 2023-03-02 |  NULL |
| 11 | 2023-03-03 |  NULL |
| 12 | 2023-03-04 |  9.00 |
| 13 | 2023-04-01 |  NULL |
| 14 | 2023-04-02 |  NULL |
| 15 | 2023-04-03 | 12.00 |
| 16 | 2023-04-04 |  NULL |
+----+------------+-------+
--------------
update `test` as t1
   set `val` = ( select `val` from ( select min(`val`) as val from `test` where `date` >= t1.`date` and `val` is not null ) as x )
 where `val` is null
--------------
 
--------------
select * from test
--------------
 
+----+------------+-------+
| id | date       | val   |
+----+------------+-------+
|  1 | 2023-01-01 |  5.00 |
|  2 | 2023-01-02 |  5.00 |
|  3 | 2023-01-03 |  7.00 |
|  4 | 2023-01-04 |  7.00 |
|  5 | 2023-02-01 |  7.00 |
|  6 | 2023-02-02 |  8.00 |
|  7 | 2023-02-03 |  8.00 |
|  8 | 2023-02-04 |  8.00 |
|  9 | 2023-03-01 |  8.00 |
| 10 | 2023-03-02 |  9.00 |
| 11 | 2023-03-03 |  9.00 |
| 12 | 2023-03-04 |  9.00 |
| 13 | 2023-04-01 | 12.00 |
| 14 | 2023-04-02 | 12.00 |
| 15 | 2023-04-03 | 12.00 |
| 16 | 2023-04-04 |  NULL |
+----+------------+-------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Pour les questions performances, on verra ça plus tard.

Cordialement.
Artemus24.
@+

**xounet** · 01/04/2023, 09h47

Envoyé par Artemus24

Salut à tous.

Je vais traiter la solution pour MySql 8.0.

Si j'ai bien compris, vous cherchez dans votre table, la première ligne dont la colonne "test2" ne soit pas marquée à NULL.
Vous récupérez cette valeur que vous allez ensuite appliquer à la même colonne pour toutes celles qui sont marquées à NULL mais inférieur strictement à sa clef.

@+

Tu as bien compris le principe, mais tu n'utilises aucune table temporaire dans tes deux exemples et le deuxième exemple ne permet pas d'obtenir le résultat : min(val) : ce n'est pas cela que je cherche.
Merci pour ces idées qui sont certainement valables mais nécessitent un temps de codage qui me semble trop important surtout dans cet exemple précis qui est très simple (comme le SQL de base).

Par contre, y aurait il à votre avis un avantage à utiliser des vues au lieu des tables temporaires ? Qu'est qui serait le moins gourmand en ressources et le plus rapide surtout dans le cas où l'on doit utiliser ces "tables/vues" de nombreuses fois ?

**Artemus24** · 01/04/2023, 14h13

Salut xounet.

Envoyé par xounet

Tu as bien compris le principe, mais tu n'utilises aucune table temporaire dans tes deux exemples ...

J'utilise aucune table temporaire car je peux très bien m'en passer, même pour résoudre des manipulations complexes de données. Elles sont contraignantes en terme de performances et de réutilisation dans les sous-requêtes. Si j'ai besoin d'extérioriser mes données, je crée une table normale et non temporaire.

Je me suis concentré sur l'écriture d'une requête faisant exactement la même chose que ton exemple. Mes solutions sont bien plus performante et font le même travail que ton exemple.

Envoyé par xounet

.. et le deuxième exemple ne permet pas d'obtenir le résultat : min(val) : ce n'est pas cela que je cherche.

En quoi la seconde solution ne permet pas d'obtenir la valeur du min(val) ? Je l'ai testé et elle fonctionne.
Explique moi en quoi elle ne te donne pas le résultat que tu recherches ?
Je t'ai donné cette solution pour te montrer comment on utilise une table dérivée.

J'ai bien compris que tu veux faire l'usage de tes tables temporaires, mais tu n'auras pas les performances qui vont avec. Et cela va rester des usines à gaz. Merci pour le cadeau que tu fais à ton successeur en maintenance. Si tu ne veux pas modifier tes requêtes, la solution restante est de modifier le fichier "my.ini".

Je pense que tu n'exploites pas toutes les potentialités de MySql et que tu te retrouves à faire des usines à gaz qui n'ont pas lieu d'être. L'erreur 1093 est bloquante quand tu crées une sous-requête qui utilise la même table que la principale requête.

Je te conseil de lire ce sujet sur l'optimisation et sur les restrictions dans MySql.

Envoyé par xounet

Merci pour ces idées qui sont certainement valables mais nécessitent un temps de codage qui me semble trop important surtout dans cet exemple précis qui est très simple (comme le SQL de base).

Tu es obligé de passer par la réécriture de tes requêtes et faire des tests de performances pour avoir quelque chose qui tient la route. Une requête doit prendre moins de 1 seconde à l'exécution en temps normal. Au delà de ce temps théorique, tu as un problème soit pour l'accès à tes données, soit de modélisation.

Ou alors, tu fais du traitement de masse, mais tu ne l'as pas explicitement dit dans ton premier sujet. Est-ce que tu le fais chaque jour ou à la demande ? Quelle est la volumétrie et combien de temps cela prend pour effectuer tes traitements ? Et surtout pourquoi as-tu besoin de bidouiller dans tes bases de données ? N'aurais tu pas un sérieux problème dans la conception de ta base de données ?

Envoyé par xounet

Par contre, y aurait il à votre avis un avantage à utiliser des vues au lieu des tables temporaires ?

Dans ton exemple, le seul avantage se trouve dans la réutilisation de ta vue pour des questions de maintenance. Tu peux la décomposer en sous-requête si cela te permet de gérer indépendamment chaque partie.

Envoyé par xounet

Qu'est qui serait le moins gourmand en ressources et le plus rapide surtout dans le cas où l'on doit utiliser ces "tables/vues" de nombreuses fois ?

De procéder à la réécriture de tes requêtes ! Il n'y a pas d'autres solutions pour obtenir une meilleure performance. Ou bien la solution de SQLPRO, changer de SGBDR.

Pas besoin de tables temporaires. C'est cette solution que tu recherchais :

Code mysql :

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
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `num_ligne` integer unsigned NOT NULL auto_increment primary key,
  `date`      datetime             NULL DEFAULT NULL,
  `test`      decimal(10,1)        NULL DEFAULT NULL,
  `test2`     decimal(65,30)       NULL DEFAULT NULL,
  `aberrant`  tinyint              NULL DEFAULT NULL,
  UNIQUE KEY `idx1` (`test2`,`date`),
  UNIQUE KEY `idx2` (`date`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`date`,`test`,`test2`,`aberrant`) VALUES
('2022-01-01 00:00:00',     4.0,                                NULL, NULL),
('2022-01-05 00:00:00',     4.0,                                NULL, NULL),
('2022-01-08 00:00:00',   489.0,   76.000000078974698400000000000000, NULL),
('2022-01-10 10:54:03',  -456.0, -500.000000000000000000000000000000, NULL),
('2022-01-15 00:00:00',  7445.0,                                NULL, NULL),
('2022-01-17 00:00:00',    45.0,  325.046860000000000000000000000000, NULL),
('2022-01-19 00:00:00',    -4.0,                                NULL, NULL),
('2022-01-20 00:00:00', -5000.0,  164.000000000000000000000000000000, NULL),
('2022-01-22 00:00:00',    -4.0,    5.000000000000000000000000000000, NULL),
('2022-01-23 00:00:00',    78.0,  -30.000000000000000000000000000000, NULL),
('2022-01-24 00:00:00',    NULL, 4999.000000000000000000000000000000, NULL),
('2022-01-25 00:00:00',     7.0,  289.000000000000000000000000000000, NULL),
('2022-01-26 00:00:00',    NULL,  456.000000000000000000000000000000, NULL),
('2022-01-28 00:00:00',    78.0,                                NULL, NULL),
('2022-01-30 00:00:00', 12600.0,                                NULL, NULL)
--------------
 
--------------
select * from `test`
--------------
 
+-----------+---------------------+---------+-------------------------------------+----------+
| num_ligne | date                | test    | test2                               | aberrant |
+-----------+---------------------+---------+-------------------------------------+----------+
|         1 | 2022-01-01 00:00:00 |     4.0 |                                NULL |     NULL |
|         2 | 2022-01-05 00:00:00 |     4.0 |                                NULL |     NULL |
|         3 | 2022-01-08 00:00:00 |   489.0 |   76.000000078974698400000000000000 |     NULL |
|         4 | 2022-01-10 10:54:03 |  -456.0 | -500.000000000000000000000000000000 |     NULL |
|         5 | 2022-01-15 00:00:00 |  7445.0 |                                NULL |     NULL |
|         6 | 2022-01-17 00:00:00 |    45.0 |  325.046860000000000000000000000000 |     NULL |
|         7 | 2022-01-19 00:00:00 |    -4.0 |                                NULL |     NULL |
|         8 | 2022-01-20 00:00:00 | -5000.0 |  164.000000000000000000000000000000 |     NULL |
|         9 | 2022-01-22 00:00:00 |    -4.0 |    5.000000000000000000000000000000 |     NULL |
|        10 | 2022-01-23 00:00:00 |    78.0 |  -30.000000000000000000000000000000 |     NULL |
|        11 | 2022-01-24 00:00:00 |    NULL | 4999.000000000000000000000000000000 |     NULL |
|        12 | 2022-01-25 00:00:00 |     7.0 |  289.000000000000000000000000000000 |     NULL |
|        13 | 2022-01-26 00:00:00 |    NULL |  456.000000000000000000000000000000 |     NULL |
|        14 | 2022-01-28 00:00:00 |    78.0 |                                NULL |     NULL |
|        15 | 2022-01-30 00:00:00 | 12600.0 |                                NULL |     NULL |
+-----------+---------------------+---------+-------------------------------------+----------+
--------------
update `test` as t1
     set `test2` = ( select `val`
                       from ( select `test2` as val
                                from `test`
                               where `date` = ( select min(`date`)
                                                  from `test`
                                                 where `date` > t1.`date`
                                                   and `test2` is not null
                                              )
                            ) as x
                   )
   where `test2` is null
order by `date`
--------------
 
--------------
select * from `test`
--------------
 
+-----------+---------------------+---------+-------------------------------------+----------+
| num_ligne | date                | test    | test2                               | aberrant |
+-----------+---------------------+---------+-------------------------------------+----------+
|         1 | 2022-01-01 00:00:00 |     4.0 |   76.000000078974698400000000000000 |     NULL |
|         2 | 2022-01-05 00:00:00 |     4.0 |   76.000000078974698400000000000000 |     NULL |
|         3 | 2022-01-08 00:00:00 |   489.0 |   76.000000078974698400000000000000 |     NULL |
|         4 | 2022-01-10 10:54:03 |  -456.0 | -500.000000000000000000000000000000 |     NULL |
|         5 | 2022-01-15 00:00:00 |  7445.0 |  325.046860000000000000000000000000 |     NULL |
|         6 | 2022-01-17 00:00:00 |    45.0 |  325.046860000000000000000000000000 |     NULL |
|         7 | 2022-01-19 00:00:00 |    -4.0 |  164.000000000000000000000000000000 |     NULL |
|         8 | 2022-01-20 00:00:00 | -5000.0 |  164.000000000000000000000000000000 |     NULL |
|         9 | 2022-01-22 00:00:00 |    -4.0 |    5.000000000000000000000000000000 |     NULL |
|        10 | 2022-01-23 00:00:00 |    78.0 |  -30.000000000000000000000000000000 |     NULL |
|        11 | 2022-01-24 00:00:00 |    NULL | 4999.000000000000000000000000000000 |     NULL |
|        12 | 2022-01-25 00:00:00 |     7.0 |  289.000000000000000000000000000000 |     NULL |
|        13 | 2022-01-26 00:00:00 |    NULL |  456.000000000000000000000000000000 |     NULL |
|        14 | 2022-01-28 00:00:00 |    78.0 |                                NULL |     NULL |
|        15 | 2022-01-30 00:00:00 | 12600.0 |                                NULL |     NULL |
+-----------+---------------------+---------+-------------------------------------+----------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Cordialement.
Artemus24.
@+

**xounet** · 02/04/2023, 23h33

Artemus,

Merci de faire partager tes idées, c'est toujours mieux que rien mais je ne peux pas me passer de tables temporaires. Est que tu penses réellement qu'elles sont inutiles ? Quel est l'intérêt de stocker sur un disque des valeurs de calculs intermédiaires qui seront effacées quelques micro secondes après ?

**binarygirl** · 03/04/2023, 00h50

Envoyé par xounet

Si table1 est très très grand alors on restreint 'tt' avec WHERE..., la création de table ne dure qu'une fraction de seconde

C'est déjà un indice en soi.

Envoyé par xounet

mais dès qu'on fait des requêtes un peu plus compliquées, c'est long à exécuter si tt dépasse les 100000 lignes par exemple , ce qui laisse penser (je suppose) que mysql cherche à stocker les données sur le disque au lieu de la mémoire vive.

C'est une possibilité, et même l'OS peut utiliser le swap dès lors que la RAM disponible diminue.
Il semble très probable que votre script SQL demande beaucoup de mémoire pour pouvoir s'éxecuter en tant que transaction atomique.

Mais je ne suis pas forcément persuadée que c'est un problème de table temporaire. Je vous conseille de faire un plan d'exécution pour chaque statement. Par exemple, pour s'assurer les indexes sont effectivement utilisés de manière optimale et comprendre où se situe le goulet d'étranglement.

Ou bien au lieu de créer une table temporaire, faites une copie pure vers une table ordinaire et vous verrez bien s'il y a une différence de performance. Je ne tablerais pas trop dessus mais ça peut être un test intéressant.

Mais il n'y a pas de miracle, même si vous avez une requête bien optimisée mais que vous tirez des Gb de données d'un coup ça va forcément se ressentir quelque part. A mon avis, ce n'est pas très raisonnable, sans même parler de l'empreinte écologique

Il faudrait peut-être envisager de saucissonner le processus.

Accessoirement, faites en sorte d'obtenir des statistiques de performances de vos queries - pas ma spécialité mais je sais qu'il y a des outils par exemple: https://dev.mysql.com/doc/workbench/...tatistics.html
En passant, peut-être jeter un coup d'oeil à l'équivalent du transaction log en Mysql (les fichiers ib_logfile*). Regardez la taille des fichiers pour avoir une idée. Encore une fois, c'est pas ma spécialité mais il faut comprendre le coût de ce que vous essayez de faire, et identifier le goulet d'étranglement.

Hormis le fait que certaines requêtes comme celles faisant appel aux window functions sont probablement lourdes en elles-même, je ne serais pas étonnée que le problème c'est tout simplement le volume de données qui est tiré d'un coup.

**binarygirl** · 03/04/2023, 21h42

Envoyé par Artemus24

L'exemple que tu nous as donné montre qu'on peut le faire en une seule requête.

Peut-être mais quid de la performance ?
C'est quand même le problème qui se pose ici et je doute que la recette proposée va améliorer les choses.
Bien sûr, on peut tester différentes approches et établir à chaque fois un plan d'exécution pour voir si on est sur la bonne voie.

**SQLpro** · 03/04/2023, 22h36

Envoyé par xounet

Artemus,

Merci de faire partager tes idées, c'est toujours mieux que rien mais je ne peux pas me passer de tables temporaires. Est que tu penses réellement qu'elles sont inutiles ? Quel est l'intérêt de stocker sur un disque des valeurs de calculs intermédiaires qui seront effacées quelques micro secondes après ?

Le problème est que vous ne savez pas du tout comment fonctionne un SGBDR, mais que vous affirmez des choses qui sont des extrapolation d'une supposition de fonctionnement de MySQL !

Donc, vous avez tout faux, mais vous persistez dans votre ignorance !

Vous n'irez pas loin en continuant de la sorte...

A +

**xounet** · 06/04/2023, 00h20

Mais je ne suis pas forcément persuadée que c'est un problème de table temporaire. Je vous conseille de faire un plan d'exécution pour chaque statement. Par exemple, pour s'assurer les indexes sont effectivement utilisés de manière optimale et comprendre où se situe le goulet d'étranglement.

binarygirl , Comment faire un plan d'exécution ? L'écologie n'est peut être pas parfaite mais relativement bien respectée je l'ai vérifié, j'ai simplement un problème d'utilisation ou de configuration des tables temporaires avec MYSQL.

SQLPRO, c'est un peu fort d'en demander plus aux utilisateurs du forum que ce pourquoi ils viennent poster... par contre j'en apprends pas beaucoup avec ton dernier post concernant les tables temporaires et je ne comprends pas votre dernière remarque en outre !

Je vais tenter de faire un comparatif (mémoire ram temporaire vs mémoire disque dur) et faire un retour..., ou essayer une variante avec les vues en attendant de trouver "le problème".

**escartefigue** · 06/04/2023, 08h10

On se perd dans des considérations techniques et je n'ai toujours pas compris le besoin fonctionnel exact.
Un exemple de contenu de la table avant et après serait le bienvenu, en tout cas pour moi

**skuatamad** · 06/04/2023, 11h05

L'idée d'une table temporaire c'est de stocker une étape intermédiaire d'un calcul complexe, mais il faut mettre un maximum de logique dedans.

Par exemple :

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
CREATE temporary TABLE tt (
      `num_ligne` int NOT NULL,      
      `test2` decimal(65,30),
      prev_ligne int
    );
 
INSERT INTO tt 
SELECT num_ligne
     , test2
     , lag(num_ligne,1,0) over(order by num_ligne) as prev_ligne
  FROM table1
 WHERE test2 IS NOT NULL;
 
 select * from tt;
 
UPDATE table1 t1
  JOIN tt 
    ON t1.num_ligne > tt.prev_ligne 
   and t1.num_ligne < tt.num_ligne 
   SET t1.test2 = tt.test2
 where t1.test2 is null;
 
SELECT * FROM table1;

Mais personnellement, je passerais par un WITH :

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
  with tt as (
SELECT num_ligne
     , test2
     , lag(num_ligne,1,0) over(order by num_ligne) as prev_ligne
  FROM table1
 WHERE test2 IS NOT NULL
 )
UPDATE table1 t1
  JOIN tt 
    ON t1.num_ligne > tt.prev_ligne 
   and t1.num_ligne < tt.num_ligne 
   SET t1.test2 = tt.test2
 where t1.test2 is null;
 
SELECT * FROM table1;

L'objectif en terme de performance c'est généralement d'être le plus ensembliste et le moins itératiste possible.

**Artemus24** · 06/04/2023, 13h23

Salut à tous.

Envoyé par binarygirl

Peut-être mais quid de la performance ?

Tu ne vas pas me faire croire que :
--> Créer une table temporaire,
--> effectuer la recopie dans la table temporaire,
--> préparer le résultat,
--> et enfin recopier dans la principale table

c'est plus performant que :
--> une sous-requête dans une requête,
--> avec un index sur la jointure.

Il y a quand même quatre étapes contre une, sachant en plus que xounet à des problèmes de stockage avec ses tables temporaires.

Maintenant, je ne peux répondre que partiellement à sa question car je n'ai pas un exemple complet.
S'il pouvait donner un export de sa base de données ainsi que la requête qu'il désire faire, je pourrais lui retourner une requête qui soit opérationnelle en terme de performance.
Mais avant de traiter la performance, il faudrait améliorer l'écriture de sa requête.

Sauf que xounet ne répond pas aux questions qu'on lui pose, et donne un exemple très restreint qui ne reflète pas la réalité de son problème.

Envoyé par binarygirl

C'est quand même le problème qui se pose ici et je doute que la recette proposée va améliorer les choses.

Il est très facile de critiquer mais ou se trouve ta solution ? Je ne la vois pas.

Envoyé par binarygirl

Bien sûr, on peut tester différentes approches et établir à chaque fois un plan d'exécution pour voir si on est sur la bonne voie.

Et à ton avis, qu'est-ce que je fais quand je propose ma solution ?
Je ne prétends pas que ma solution est la meilleure, mais au moins, j'ai fait l'effort d'en trouver une.

Envoyé par Escartefigue

On se perd dans des considérations techniques et je n'ai toujours pas compris le besoin fonctionnel exact.

J'aurai dû poser la question. Merci de l'avoir fait.
Je pense que ce sont des manipulations à la demande afin de corriger des erreurs ou absences dans ses tables.
Autrement dit, il bouche les trous qu'il rencontre.

Envoyé par Escartefigue

Un exemple de contenu de la table avant et après serait le bienvenu, en tout cas pour moi

Un exemple complet avec les explications qui vont bien. Jusqu'à présent il faut deviner.

Comme je l'ai dit, on peut se passer des tables temporaires car trop couteuses en terme de volumétrie, ainsi que de performance.

Envoyé par skuatamad

L'idée d'une table temporaire c'est de stocker une étape intermédiaire d'un calcul complexe, mais il faut mettre un maximum de logique dedans.

Expliquez moi en quoi passer par une table temporaire est synonyme de performance ?
On peut s'en passer car on peut créer une sous-requête dans une requête.
J'ai repris votre exemple :

Code mysql :

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
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `num`   integer unsigned NOT NULL auto_increment primary key,
  `val`   decimal(6,2)         NULL DEFAULT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`val`) VALUES
(   NULL),(   NULL),(  76.00),(-500.00),(   NULL),( 325.00),(   NULL),( 164.00),
(   5.00),( -30.00),(4999.00),( 289.00),( 456.00),(   NULL),(   NULL)
--------------
 
--------------
select * from `test`
--------------
 
+-----+---------+
| num | val     |
+-----+---------+
|   1 |    NULL |
|   2 |    NULL |
|   3 |   76.00 |
|   4 | -500.00 |
|   5 |    NULL |
|   6 |  325.00 |
|   7 |    NULL |
|   8 |  164.00 |
|   9 |    5.00 |
|  10 |  -30.00 |
|  11 | 4999.00 |
|  12 |  289.00 |
|  13 |  456.00 |
|  14 |    NULL |
|  15 |    NULL |
+-----+---------+
--------------
update `test` as t1
inner join ( select `num`,
                    `val`,
                    lag(`num`,1,0) over(order by `num`) as prev
               from `test`
              where `val` is not null
           )  as t2
        on t1.`num` > t2.`prev`
       and t1.`num` < t2.`num`
       set t1.`val` = t2.`val`
     where t1.`val` is null
--------------
 
--------------
select * from `test`
--------------
 
+-----+---------+
| num | val     |
+-----+---------+
|   1 |   76.00 |
|   2 |   76.00 |
|   3 |   76.00 |
|   4 | -500.00 |
|   5 |  325.00 |
|   6 |  325.00 |
|   7 |  164.00 |
|   8 |  164.00 |
|   9 |    5.00 |
|  10 |  -30.00 |
|  11 | 4999.00 |
|  12 |  289.00 |
|  13 |  456.00 |
|  14 |    NULL |
|  15 |    NULL |
+-----+---------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Elle est plus concise que ma précédente solution.
En terme de performance, je n'en sais rien, car ce n'est pas sur 15 lignes qu'on peut le savoir.
Il y a quand même un point en votre défaveur, on ne peut pas créer un index sur la colonne "prev".

Envoyé par skuatamad

L'objectif en terme de performance c'est généralement d'être le plus ensembliste et le moins itératiste possible.

Le premier objectif est de répondre à une spécification fonctionnelle. Laquelle ? On ne sait pas.
Ensuite, il y a les performances. Combien de temps dure le traitement ? On ne sait pas, non plus.
L'écriture de la requête doit être concise, facilement maintenable et bien documenter. Dans l'exemple de xounet, c'est plutôt le contraire.
Si pour des raisons de performances, on doit dégrader la base de données ou l'écriture en plusieurs requêtes, cela doit se justifier.
C'est, je suppose, ce que nomme Binarygirl "un plan d'exécution".
Pour finir, la maintenance. Il ne faut jamais négliger cet aspect qui est très couteuse pour les entreprises.

Pour faire des tests de performances, il faut travailler sur des cas réelles.
Dans un environnement de test, cela peut ne pas être suffisant.
La principale raison est que vous êtes mono-utilisateur et mono-tâches
Vous avez toutes les ressources à votre disposition pour le faire.
Ce n'est pas le cas en production ou il peut y avoir des conflits d'accès aux données ainsi que le partage des ressources en plusieurs utilisateurs.
Ce qui peut ralentir grandement les temps d'exécutions.

Cordialement.
Artemus24.
@+

**skuatamad** · 06/04/2023, 14h25

Envoyé par Artemus24

Expliquez moi en quoi passer par une table temporaire est synonyme de performance ?

Ben j'aurais bien du mal puisque je ne vois pas l'intérêt de la table temporaire dans ce cas...
Je précise bien que j'utiliserais une CTE pour ce problème. Je préfère la CTE à la sous-requête car je trouve ça plus lisible.

J'avais pas vu votre requête du 01/04, qui est sur le même type d'approche, un seul UPDATE ensembliste !

Je propose une table temporaire à xounet parce qu'il veut des table temporaire, en lui indiquant qu'il faut y implémenter un minimum de logique métier dans cette étape couteuse.

Mais bien sûr que l'exemple proposé, même s'il n'est pas trivial, n'est pas suffisamment complexe pour justifier l'utilisation d'une table temporaire.

**Artemus24** · 06/04/2023, 14h41

Salut skuatamad.

Envoyé par skuatamad

Ben j'aurais bien du mal puisque je ne vois pas l'intérêt de la table temporaire dans ce cas...

Nous sommes d'accord. Sinon, c'est l'art de compliquer quand on peut faire simple !

Envoyé par skuatamad

Je préfère la CTE à la sous-requête car je trouve ça plus lisible.

La maintenance, et donc la lisibilité a toujours été un de mes critères.

Envoyé par skuatamad

Je propose une table temporaire à xounet parce qu'il veut des table temporaire, en lui indiquant qu'il faut y implémenter un minimum de logique métier dans cette étape couteuse.

Je ne comprends pas pourquoi xounet utilise des tables temporaires.
Il n'y a aucune raison à décomposer un traitement en plusieurs phases.
Il ne le dit pas, mais on peut supposer que son traitement est dans une boucle itérative.

Est-ce due à l'erreur 1093 qui est bloquante ? Il suffit de passer par une table dérivée.

Je pense que xounet est débutant et n'a pas les compétence requises pour faire fonctionner dans de bonne condition son SGBDR.

Et en plus, résoudre un problème de performance ne se fait pas en cinq minutes.
Cela prend beaucoup de temps et nécessite de faire beaucoup d'essais avant de trouver une solution.

Cordialement.
Artemus24.
@+

**xounet** · 06/04/2023, 21h43

Envoyé par skuatamad

L'idée d'une table temporaire c'est de stocker une étape intermédiaire d'un calcul complexe, mais il faut mettre un maximum de logique dedans.

Par exemple :

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
CREATE temporary TABLE tt (
      `num_ligne` int NOT NULL,      
      `test2` decimal(65,30),
      prev_ligne int
    );
 
INSERT INTO tt 
SELECT num_ligne
     , test2
     , lag(num_ligne,1,0) over(order by num_ligne) as prev_ligne
  FROM table1
 WHERE test2 IS NOT NULL;
 
 select * from tt;
 
UPDATE table1 t1
  JOIN tt 
    ON t1.num_ligne > tt.prev_ligne 
   and t1.num_ligne < tt.num_ligne 
   SET t1.test2 = tt.test2
 where t1.test2 is null;
 
SELECT * FROM table1;

Mais personnellement, je passerais par un WITH :

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
  with tt as (
SELECT num_ligne
     , test2
     , lag(num_ligne,1,0) over(order by num_ligne) as prev_ligne
  FROM table1
 WHERE test2 IS NOT NULL
 )
UPDATE table1 t1
  JOIN tt 
    ON t1.num_ligne > tt.prev_ligne 
   and t1.num_ligne < tt.num_ligne 
   SET t1.test2 = tt.test2
 where t1.test2 is null;
 
SELECT * FROM table1;

L'objectif en terme de performance c'est généralement d'être le plus ensembliste et le moins itératiste possible.

On est bien d'accord sauf que dans mon cas j'ai choisi d'utiliser des tables temporaires pour passer d'une table de 6 Go à une table de quelques Mo pour finalement effectuer à partir de là les calculs, désolé mais je peux pas répéter 100 fois la même chose le problème est bien posé il suffit de lire les tous premiers post, je ne peux pas faire plus il faut lire attentivement... svp Il est évident que with remplit cette fonction avec des tables "dures" mais vous ne l'ignorez peut être les tables temporaires ne peuvent pas être utilisées 2 fois dans une seule requête !

Tables temporaires et usage de la mémoire

Administration MySQL

Discussions similaires

Partager

Partager