Discussion :

[xounet]

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]

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]

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]

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]

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]

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 <=