Tables temporaires et usage de la mémoire

Version imprimable

Voir 40 message(s) de cette discussion en une page

06/04/2023, 22h56
binarygirl
Citation:

Envoyé par Artemus24

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.

Le nombre d'étapes ne dicte pas la performance !

Je n'ai pas dit que sa méthode était bonne et la vôtre mauvaise.
Moi non plus, je ne suis pas convaincue que cette histoire de table temporaire est une bonne chose. C'est un parti pris qu'il faut remettre en question

Oui, une sous-sous-requête peut être très performante si elle est optimisée mais cela suppose aussi que les tables soient indexées comme il faut, que les jointures se fassent de façon idéale etc. Je n'ai pas l'impression qu'on est dans ce cas de figure. Je pense donc que votre solution pourrait potentiellement être encore pire dans son cas de figure, compte tenu de la structure de ses tables. Sur un jeu de données conséquent, je soupçonne que le coût peut au contraire être exponentiel.
Ca se comprend: faire des subselect imbriqués sur des tables gigantesques non indexées, ou sans pouvoir exploiter les index présents, ça revient à faire un full table scan et le coût devient réellement prohibitif quand le nombre d'enregistrements est important.

Si vous pensez le contraire, alors testez de votre côté, injectez quelques dizaines de milliers d'enregistrements pour commencer, et tirez votre propre plan d'exécution pour démontrer que a) les tables sont judicieusement indexées, b) tous les index présents sont utilisés de manière optimale par cette requête et c) un accroissement même important du nombre d'enregistrements n'a pas d'impact significatif sur les performances.

Citation:

Envoyé par Artemus24

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

Je l'ai dit plus haut:
- apprendre à tirer un plan d'exécution, pour comprendre où se situe le goulet d'étranglement et quelle est la partie qu'il faut tenter d'améliorer. Si on veut améliorer les performances on doit apprendre à mesurer le coût de ce qu'on fait et c'est pareil en programmation
- j'ai suggéré un outil relativement accessible pour commencer
- et je recommande de lire les bases pour bien comprendre le principe: https://dev.mysql.com/doc/refman/8.0...formation.html
- moi-même je ne suis pas spécialiste mais je sais un peu ce qu'il faut chercher et c'est déjà le début de la solution
- et si vraiment il insiste pour faire des tables temporaires, mais qu'il pense que la gestion des tables temporaires est mauvaise en Mysql (ce que suggère @SQLpro par ailleurs), alors qu'il essaie avec des tables ordinaires. Pourquoi ne pas tester juste pour la valeur éducative et éliminer une hypothèse au passage (même si encore une fois je doute du bien-fondé de la démarche...). En tout cas, il n'est pas interdit d'expérimenter et c'est comme ça qu'on apprend
- envisager de saucissonner le processus - est-il vraiment nécessaire de traiter l'entièreté de la table en une passe ?
Il ne faut pas hésiter à tester différents scenarios, et la solution qui paraît la plus intuitive ne sera pas forcément la meilleure.

Que cherche-t-on à prouver ici ? Tant qu'on y est on peut prendre un serveur avec 16 processeurs et 100 Gb de RAM et charger la DB en mémoire mais il faut bien comprendre que c'est une fuite en avant et un gaspillage de ressources. Ce n'est pas le matériel qu'il faut changer mais la manière de penser.

Là où je vous rejoins, c'est sur la lisibilité du code. Je pense que si le code est difficilement lisible pour un humain c'est souvent pareil pour l'ordinateur, où dans le cas présent, le query optimizer ou whatever ça s'appelle en Mysql.

Encore une fois, il faut réfléchir à la structure des tables, le volume que l'on traite, et aussi prendre en compte certains facteurs périphériques comme le transaction log. Si vous mettez à jour un gros volume de données mais que cela doit se faire en une transaction atomique, ça peut impliquer l'écriture d'un gros paquet de données dans ce transaction log, jusqu'au commit final. Des gigas de données peut-être. Le serveur fait ce qu'on lui demande de faire mais ça ne veut pas dire qu'il ne souffre pas.
06/04/2023, 22h56
xounet

Le but ce n'est pas de mettre à jour table1 mais bien la table tt tout est marqué dans la première requête, dit autrement il faut imaginer que table1 est en lecture seule pour ceux qui ont (vraiment) du mal à suivre... ouf !

Citation:

Envoyé par Artemus24

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

y a juste à lire les 4 lignes sql écrites au début du poste en partant d'une table table1 de 6000000 de lignes, mais bon si vous persistez à vouloir me faire modifier table1 alors que ce n'est pas du tout l'objectif je ne peux rien de plus ! Je finirai dans tous les cas par trouver une réponse et je vous informerai du principe retenu.
07/04/2023, 08h31
SQLpro

Citation:

Envoyé par xounet

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

C'est bien ça le problème.... Vous ajoutez des données 6 Go + quelques Mo en pensant naïvement que cela va aller plus vite. Plus de volume = moins de performances... D'autant que vos tables temporaires n'ont pas d'index...

Encore une fois vous ne savez pas comment fonctionne un SGBDR et en extrapolant, vous pensez à tort que cela ira mieux. C'est toute le contraire !
Les SGBDR travaillant en mémoire il faut donc ajouter en RAM les quelques Mo et les 6 Go pour traiter votre requête !

Commencez pas prendre des cours sur le fonctionnement des SGBDR et la langage SQL !
Mon site web comme mes bouquins peuvent vous y aider.

A +

Salut skuatamad.

J'ai fait des tests de performances sur 10 000 lignes.
Pour les insertions, j'ai fait une procédure stockée où je mets aléatoirement le NULL dans la colonne "val".
Ma requête a pris 1mn10s tandis que la votre à pris 7s.

J'ai découvert la fonction "lag()" que je ne connaissais pas. :)

J'ai refais le test sur 100 000 lignes, avec un nouveau jeu d'essai, et votre requête a pris 3mn56.
Je n'ai même pas tenté la mienne qui est largement plus lente que la votre.

J'ai gagné un peu temps en changeant juste une ligne :
Code:

1 2 3 4 5 6 7 8 9 10 11 update `test` as t1 inner join ( select `date`, `val`, lag(`date`,1,'1999-12-31') over(order by `date`) as prev from `test` where `val` is not null ) as t2 on t1.`date` between t2.`prev` and t2.`date` set t1.`val` = t2.`val` where t1.`val` is null;
Voici la table :
Code:

1 2 3 4 5 6 7 CREATE TABLE `test` ( `date` date NOT NULL primary key, `val` decimal(6,2) NULL DEFAULT NULL, UNIQUE KEY `idx` (`val`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED;
En clef primaire, j'ai mis la date et crée un index sur le couple (val, date).
Je ne sais pas si l'on peut faire mieux.

Cordialement.
Artemus24.
@+

07/04/2023, 15h42
Artemus24
Salut à tous.

J'ai trouvé une solution bien plus performante, ainsi que plus concise, pour résoudre le problème.
Je précise que ce sont des temps elaps et non des temps cpu.
Pour 100 000 lignes, le chargement de la table (les insert) a pris 2 minutes 14,48 secondes.
Le test de skuatamad a pris 5 minutes 2,9 secondes. Cela dépend du nombre de NULL dans la colonne "val".

Ma nouvelle requête, quand à elle a pris 4,52 secondes.
Code:

1 2 3 update `test` set `val` = @prev:=coalesce(`val`,@prev) order by `date` desc;
Je passe par une "user defined variable" où celle-ci contient la dernière valeur non NULL de la colonne "val" selon l'ordre du tri.

Comme je l'ai dit, pas besoin de table temporaire.
Et j'espère que mon test va satisfaire BinaryGirl. :D

Cordialement.
Artemus24.
@+
17/04/2023, 19h30
Artemus24

Salut xounet.

Problème résolu ou pas ?

Cordialement.
Artemus24.
@+
23/04/2023, 17h03
xounet

Citation:

Envoyé par SQLpro

C'est bien ça le problème.... Vous ajoutez des données 6 Go + quelques Mo en pensant naïvement que cela va aller plus vite. Plus de volume = moins de performances... D'autant que vos tables temporaires n'ont pas d'index...

Encore une fois vous ne savez pas comment fonctionne un SGBDR et en extrapolant, vous pensez à tort que cela ira mieux. C'est toute le contraire !
Les SGBDR travaillant en mémoire il faut donc ajouter en RAM les quelques Mo et les 6 Go pour traiter votre requête !

Commencez pas prendre des cours sur le fonctionnement des SGBDR et la langage SQL !
Mon site web comme mes bouquins peuvent vous y aider.

A +

Si si les tables temporaires ont bien des index je ne peux pas tout mettre ici c'est un bonne remarque, c'est la première chose à faire pour exécuter plus vite ! Après certaines tables temporaires n'ont qu'une seule ligne je ne vois pas l'intérêt de faire un index systématiquement... Je ne comprends pas la suite, mais Mysql n'a pas besoin de 6 Go de ram pour traiter les données d'une table de 6 Go. Paratage svp si tu as de bons tutoriels !!

@Artemus24

Merci pour tes multiples recherches. :), mais je ne vois pas vraiment le rapport avec le problème que j'ai exposé. Ce n'est une seule requête qui pose problème mais la façon d'enchaîner 20-30 requêtes en utilisant des tables intermédiaires "éphémères"...

Au final j'avance à grands pas :
D'abord dans mon cas les requêtes sont transmises au server MYSQL par PHP et c'est sûrement un point à améliorer pour la gestion de la mémoire vive mais bon ça fonctionne assez bien globalement :

Code:

mysqli_multi_query($base,$sql) or exit(mysqli_error($base))

J'ai fait des tests du coup sans PHP pour la suite pour être plus sûr des performances.

J'ai fait une belle progression en remplaçant toutes les tables temporaires par des VUES (sauf la table tt qui est une table résultat mise à jour et ne peut pas être une vue) et cela fonctionne bien mieux :
-avec les tables TEMPORARY, le temps d'exécution affiché par MYSQL arrive à 14 minutes !!

Citation:

Query OK, 282 rows affected (8 min 41.47 sec) / ..... / Query OK, 282 rows affected (4 min 50.12 sec)

-avec les VUES Mysql affiche

Citation:

Query OK, 282 rows affected, 108 warnings (1 min 35.73 sec)

Soit un facteur 10 !! à epsilon près CQFD et c'est une solution globale.

Donc ça démontre dans mon cas qu'il ne faut pas multiplier la création de tables temporaires dans une série de requêtes mais plutôt utiliser des vues (même plusieurs) qui peuvent être réutilisées plusieurs fois dans une requête (et cela semble plus souple pour les requêtes UPDATE qui sont parfois un problème avec les tables temporaires)

Certes il y a une marge de progression importante encore car 2 minutes d'exécution en ayant fait quelques calculs sur un sous ensemble de 45000 lignes c'est bien mais un peu long quand même.

Je vois quand même des inconvénients importants pour l'utilisation des VUES :
-les index ne peuvent pas être créés mais je pense que si la table source possède un index c'est suffisant ??
-celles ci sont permanentes pour toutes les sessions (les tables temporaires ne sont visibles que dans la session où elles ont été créées)
-risquées si utilisées dans update : on peut effacer les données de la table source
-si l'on souhaite les rendre temporaires il faut gérer des noms de vues etc... les effacer à la fin de la session
connaissez vous d'autres moyens permettant d'éviter ces problèmes ?
23/04/2023, 23h10
Artemus24

Salut Xounet.

Citation:

Envoyé par Xounet

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

J'ai relu le fil de ta discussion. Tu dis avoir créé un sujet, il y a plusieurs mois, où tu n'as pas eu de solutions. Peux-tu nous communiquer le lien, svp, si cet ancien sujet est encore d'actualité ?

Tu nous as exposé un exemple, et comme tu as pu le constater, Skuatamad et moi-même avons trouvé plusieurs solutions. Le but de l'exercice était de boucher les trous (les NULL) d'une colonne avec la valeur juste au dessus. Je sais que ta problématique reposait sur la performance et les tables temporaires.

J'ignore le genre de travail que tu fais. Tu ne nous as pas donné la véritable raison de l'usage de ces tables temporaires. J'ai supposé que tu rencontrais l'erreur "ERROR 1093 (HY000) at line 65: You can't specify target table 't1' for update in FROM clause", qui peut facilement être contournée.

Ton insatisfaction vient de ton exemple qui n'est pas représentatif des problèmes de performances que tu rencontres. A vouloir trop simplifier, on finit par exposer un problème qui n'en est plus un.

Citation:

Envoyé par Xounet

mais bon si vous persistez à vouloir me faire modifier table1 alors que ce n'est pas du tout l'objectif je ne peux rien de plus !

Ma dernière requête fonctionne tout aussi bien sur les tables temporaires que sur la table d'origine. Je ne vois pas où il y a un problème.

Citation:

Envoyé par Xounet

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

Le problème ne concerne pas les tables temporaires ou les View ou quoi que ce soit d'autre. Le problème est de revoir, si cela est nécessaire, la modélisation de ta base de données afin de résoudre les problèmes liés à la structuration des données. De mauvaises performances sont souvent liées à une mauvaise modélisation ainsi qu'à une méconnaissance des SGBDR.

Dans MySql, avec le paramétrage adéquate, et une bonne modélisation, je suis passé d'un chargement de tables d'environ 45 minutes à moins de 1 minute. Il faut parfois trouver des astuces pour améliorer les performances, quitte à dégrader un peu la modélisation, sans rien changer à la fonctionnalité de la base de données.

Citation:

Envoyé par Xounet

Si si les tables temporaires ont bien des index je ne peux pas tout mettre ici c'est un bonne remarque, c'est la première chose à faire pour exécuter plus vite !

Comme l'indique BinaryGirl, il faut faire un plan d'exécution et tester chaque solution afin de savoir si les index sont pertinents et surtout correctement utilisés. Ce qui est valable pour une petite volumétrie peut devenir problématique pour une plus grand. On ne peut pas répondre à la problématique en mettant des index sans faire des testes avant.

Citation:

Envoyé par Xounet

je ne vois pas vraiment le rapport avec le problème que j'ai exposé. Ce n'est une seule requête qui pose problème mais la façon d'enchaîner 20-30 requêtes en utilisant des tables intermédiaires "éphémères"...

Comme les autres membres de ce forum, je ne peux pas deviner ce que tu n'exposes pas clairement dans ce fil de discussion. Pourquoi as-tu 20 à 30 requêtes qui s'enchainent ? Si c'est le cas, je peux comprendre tes problèmes de performances.

Citation:

Envoyé par Xounet

Au final j'avance à grands pas :

Tel que je comprends ce que tu nous exposes, je dirais NON car tu n'as pas résolu ton problème de modélisation ainsi que ces enchainements de traitements qui n'ont pas lieu d'être. Ce qui est le plus couteux en terme de performances, ce sont les accès aux disques.

Citation:

Envoyé par Xounet

D'abord dans mon cas les requêtes sont transmises au server MYSQL par PHP et c'est sûrement un point à améliorer pour la gestion de la mémoire vive mais bon ça fonctionne assez bien globalement :

Ma première idée en te lisant, a été de te proposer une procédure stocké. Elles ont été inventés pour faire en sorte que le traitement se passe coté serveur SGBDR exclusivement et non faire des aller-retour entre MySql et Php qui peut prendre beaucoup de temps. Skuatamad l'a très bien dit :

Citation:

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.

Si vous travaillez en Php, vous faites un traitement itératif !

Citation:

Envoyé par Xounet

Donc ça démontre dans mon cas qu'il ne faut pas multiplier la création de tables temporaires dans une série de requêtes ...

C'est ce que je vous ai dit dès le départ de ce fil de discussion, mais vous ne m'avez pas cru.

Citation:

Envoyé par Xounet

... mais plutôt utiliser des vues (même plusieurs) qui peuvent être réutilisées plusieurs fois dans une requête (et cela semble plus souple pour les requêtes UPDATE qui sont parfois un problème avec les tables temporaires)

La view est une façon d'écrire une requête sans faire de la redondance dans une application. C'est une bonne façon de travailler. Mais les view sont aussi des tables temporaires, comme les CTE.

Pour les delete et update, il y a une façon de les écrire pour ne pas rencontrer des erreurs comme celle que j'ai indiqué ci-dessus (error #1093). Il existe dans MySql des restrictions dues à la façon d'accéder aux données, et il faut en tenir compte. Ce n'est pas spécifique à MySql ou MariaDB mais à tous les SGBDR.

Citation:

Envoyé par Xounet

Certes il y a une marge de progression importante encore car 2 minutes d'exécution en ayant fait quelques calculs sur un sous ensemble de 45000 lignes c'est bien mais un peu long quand même.

C'est mieux, j'en conviens, mais vous n'avez que 45 000 lignes.

Citation:

Envoyé par Xounet

les index ne peuvent pas être créés mais je pense que si la table source possède un index c'est suffisant ??

Si vous faites un balayage de la table, les index ne vous servent à rien. La solution que je vous ai donné ne se sert pas des index et est très performantes.

Citation:

Envoyé par Xounet

risquées si utilisées dans update : on peut effacer les données de la table source

Savez vous ce qu'est le mode transactionnel ? Cela permet avec le moteur InnoDB d'avoir une bonne gestion de l'intégrité des données.

Citation:

Envoyé par Xounet

si l'on souhaite les rendre temporaires il faut gérer des noms de vues etc... les effacer à la fin de la session

Vous ne devez pas accéder à votre base de données en tant que "root" (super utilisateur ayant tous les privilèges), mais en tant que simple utilisateur ayant quelques privilèges comme les "select", les "delete", les "update" et les "insert". Vous pouvez vous octroyer celle de créer et d'utiliser des view avec vos privilèges de simples utilisateurs.

Ainsi vous ne pourrez pas détruire les view qui ne vous appartiennent pas mais uniquement les votre. Créez les en début de session, puis détruisez les quand vous n'en n'aurez plus besoin.

Par contre, je ne vois pas l'intérêt de créer des view temporaires. Si elles ont une utilité, vous devez les conserver !

Citation:

Envoyé par Xounet

connaissez vous d'autres moyens permettant d'éviter ces problèmes ?

Nous sommes là pour répondre à des questions de faisabilités mais c'est à vous de faire l'effort d'apprendre les rudiments des SGBDR.

Cordialement.
Artemus24
@+

Voir 40 message(s) de cette discussion en une page