[mysql 5] utilisation des tables temporaires

Version imprimable

03/11/2010, 01h30
gene69

[mysql 5] utilisation des tables temporaires
Bonjour

J'ai le problème suivant. J'ai besoin de faire une requete un peu sophistiquée et il se trouve que si je l'écris d'un seul bloc elle est tuée par l'hébergeur parce qu'elle consomme trop de ressource (en plus d'être trop longue pour une utilisation web). Si je la divise en deux, tout va mieux, j'ai moins de calculs redondants parce que j'utilise plusieurs fois les données précalculées.

J'avais envie de me tourner vers les tables temporaires, celle-ci me semblait une solution parfaite sauf qu'une table temporaire présente l'inconvénient( du moins avec MyIsam ) de ne pas pouvoir être manipulée comme une table permanente (message d'erreur: can not re-open temporary table ) qui est un bug/feature connu et débatu dans la doc officielle.

Donc j'ai besoin d'une solution de contournement. J'aimerai savoir s'il existe une façon recommandée de le faire, j'ai rien trouvé dans la faq.

Maintenant la solution que j'ai trouvée que je vous demande de critiquer:
1. création d'une table permanente vide, avec le moteur InnoDB (important pour la transaction)
2. démarrage d'une transaction
3. insertion dans la table d'usage temporaire des données précalculées
4. ma grosse requête qui lit les données de la table d'usage temporaire
5. rollback (j'ai pas besoin de conserver ses données dans la base, so... )
une mise à jour différentielle de la table me parait plus difficile à scripter.

j'ai un inconvénient, c'est que dans certain cas, j'ai un problème de verrou non libéré si le script s'interrompt avant la fin du script, ce que je gère via l'exception retournée, même si ça ne marche pas si bien que ça.
03/11/2010, 10h34
CinePhil

On peut voir la requête monstrueuse ?

ouaip:

ce qui est malheureux, c'est qu'en la réécrivant, j'ai trouvé des simplifications.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
SELECT
	idplanning1,
	idplanning2,
	SUM( nombreAssite1 ) /
	(
		SELECT COUNT(x2.idparticipation)
		FROM gestion_session as s2 INNER JOIN gestion_participation as x2  ON s2.idsession = x2.idsession
		WHERE s2.idplanning = idplanning1
			AND  "2010-08-01 00:00:00" <= s2.date_session AND s2.date_session <= "2011-08-01 00:00:00" 
	) *
	SUM( nombreAssite2 ) /
	(
		SELECT COUNT(x4.idparticipation)
		FROM gestion_session as s4 INNER JOIN gestion_participation as x4  ON s4.idsession = x4.idsession
		WHERE s4.idplanning = idplanning2
			AND 
 
 "2010-08-01
 
 00:00:00" <= s4.date_session AND s4.date_session <= "2011-08-01 00:00:00" 
	) as taux
FROM
(
	SELECT
		idplanning1,
		idplanning2,
		idmembre,
		nombreAssite1,
		nombreAssite2
	FROM
	(
		SELECT
			p1.idplanning as idplanning1,
			p2.idplanning as idplanning2,
			m.idmembre,
			(
				SELECT COUNT(x1.idparticipation)
				FROM gestion_participation as x1 INNER JOIN gestion_session as s1 ON s1.idsession = x1.idsession
				WHERE x1.idmembre = m.idmembre
					AND s1.idplanning = p1.idplanning
					AND  "2010-08-01 00:00:00" <= s1.date_session AND s1.date_session <= "2011-08-01 00:00:00" 
			) as nombreAssite1,
			(
				SELECT COUNT(x3.idparticipation)
				FROM gestion_participation as x3 INNER JOIN gestion_session as s3 ON s3.idsession = x3.idsession
				WHERE x3.idmembre = m.idmembre
					AND s3.idplanning = p2.idplanning
					AND 
 "2010-08-01
 00:00:00" <=
 
 s3.date_session AND s3.date_session <= "2011-08-01 00:00:00" 
			) as nombreAssite2
		FROM	gestion_membre as m ,
			gestion_planning as p1,
			gestion_planning as p2
		WHERE	 "2010-08-01 00:00:00" <= p1.date_fin AND p1.date_debut <= "2011-08-01 00:00:00" 
			AND  "2010-08-01 00:00:00" <= p2.date_fin AND p2.date_debut <= "2011-08-01 00:00:00" 
 
	) AS t1
	WHERE nombreAssite1 > 0
) as t2
GROUP BY idplanning1, idplanning2

doit y avoir un shéma ici:
http://www.ecole-1001danses.com/gest...ages/shema.png

03/11/2010, 14h38
CinePhil
Je vois encore des choses à améliorer dans la requête...
1)
Code:

1 2 3 FROM gestion_membre AS m , gestion_planning AS p1, gestion_planning AS p2
Il y aura ici un produit cartésien des 3 tables car il n'y a pas de condition de jointure associée ; jointures qu'il faudrait d'ailleurs écrire avec l'opérateur de la syntaxe normalisée : JOIN.

2) À plusieurs endroits :
Code:

1 2 AND "2010-08-01 00:00:00" <= s4.date_session AND s4.date_session <= "2011-08-01 00:00:00"
Il vaut mieux utiliser BETWEEN. Et les valeurs textuelles ou de date s'écrivent entre apostrophes et pas entre guillemets. Ce qui donnerait ceci :

Code:

AND s4.date_session BETWEEN '2010-08-01 00:00:00' AND '2011-08-01 00:00:00'

3) Je vois dans ton schéma que tu as des colonnes date et des colonnes heure dans la table Gestion_planning. Tu peux donc enlever la partie horaire du BETWEEN ci-dessus je pense.

=> Tu peux nous dire ce qu'est sensée faire cette requête ?
-----------------
Au niveau du modèle de données...
a) La table 'gestion_inscription' semble être une table associative entre les membres et les plannings.
Si c'est le cas, et à moins qu'un membre puisse s'inscrire plusieurs fois à un planning, l'identifiant 'idinscription' est inutile.
Si les dates de début et de fin de cette table sont celles du planning, elles sont également inutiles ; il y a redondance de données donc risque de données incohérentes.

b) Idem pour la table 'gestion_participation' concernant l'identifiant 'idparticipation' et pour la date de participation si c'est la même que la date de session.

c) la boucle formée par les relations entre les tables fait qu'un membre peut participer à une session d'un planning auquel il n'est pas inscrit.

Bref, j'ai l'impression qu'il y aurait pas mal de choses à revoir ! Mais peut-être ai-je mal interprété le schéma de données.
04/11/2010, 01h58
gene69

Merci pour cette analyse fine,
----
Au niveau du modèle de données...
mea culpas les noms sont mauvais.

Citation:

a) La table 'gestion_inscription' semble être une table associative entre les membres et les plannings.
Si c'est le cas, et à moins qu'un membre puisse s'inscrire plusieurs fois à un planning, l'identifiant 'idinscription' est inutile.

tout à fait d'accord, sauf que j'ai 3 classes qui manipulent les données via les clefs artificielles, essentiellement parce que j'aime n'avoir qu'une seule colonne pour identifier une donnée et pas une composition de colonne. C'est mal, j'assume. Je sais que c'est pas super d'avoir des indexes dans tous les sens, mais bon, faire la validation d'entier c'est super facile. La prochaine fois j'utilise un vrai framework.

Citation:

Si les dates de début et de fin de cette table sont celles du planning, elles sont également inutiles ; il y a redondance de données donc risque de données incohérentes.

Bien vu, c'est justement pas le cas, l'élève peut arriver en cours d'année, je veux garder un effectif théorique juste pour pouvoir faire des stats sur les absents.

Citation:

b) Idem pour la table 'gestion_participation' concernant l'identifiant 'idparticipation' et pour la date de participation si c'est la même que la date de session.

oui mais non, pour entre autres la même raison. Le timestamp sur gestion_participation c'est la date d'écriture dans la table, la sémantique c'est des pointages en fait. Ca permet plein de chose: de savoir si le mec est à l'heure, de lui présenter quand il a pointé par rapport à certains tarif... il arrive que le mec passe au travers des pointages ou utilise des fiches papiers... du coup il se tape la régule d'un coup, pour pas qu'il s'étonne on fait la transparence là dessus.

Citation:

c) la boucle formée par les relations entre les tables fait qu'un membre peut participer à une session d'un planning auquel il n'est pas inscrit.

it's a feature. Comme on dit. Le but c'est de pouvoir laisser les gens essayer sans payer puis quand ils ont bien essayé, régulation ou abandon (d'ou les dates de partout).

Citation:

Bref, j'ai l'impression qu'il y aurait pas mal de choses à revoir ! Mais peut-être ai-je mal interprété le schéma de données.

Quand on a un shéma, on n'a pas le CDD, ceci dit c'est sympa d'avoir un retour là dessus.

Citation:

Et les valeurs textuelles ou de date s'écrivent entre apostrophes et pas entre guillemets.

je savais pas. Comme je ne me suis jamais fait jeté ...

Citation:

Il vaut mieux utiliser BETWEEN.

C'est plus facile à relire, je sais! la défense plaide que ses conditions sont générées automatiquement par un objet. Je m'étais posé la question et je n'ai trouvé aucune mention de différence de performance entre les inégalités et les between, du coup j'ai coupé au plus court.

---

oui, pour le produit cartésien, je suis tout-à-fait d'accord, ça fait partie des choses que j'ai supprimé grâce à la table pré-calculée et un peu plus de concentration. bien vu.

Pour l'histoire du JOIN j'y crois mais je pratique pas. Depuis que je vous lis sur le forum j'ai modifié l'écriture de mes jointures naturelles (comme quoi vous prêchez pas que dans le désert). De toute façon, je crois qu'il est illusoire de penser qu'une requête peut être exécutée sans modification d'un sgbd à l'autre. J'ai eu a faire à un environement de base de données hétérogènes, il n'y a aucun sgbd qui gère ses dates de la même façon et qui manipule ses dates avec des fonctions qui portent le même nom. Sql c'est des dialectes, pas un langage. (pour mémoire Oracle11, MSSql-server 2k,MySql 5.0, Hyperfile(v12)/odbc, avec des machines configurées dans des langues diverses )

bon et pour le coup de la transaction non validée, vous en pensez quoi? ça a quel impact sur la réplication (quoi que c'est pas moi qui supporte ce cout là :roll:).
04/11/2010, 09h59
CinePhil

Il manque la réponse à la question essentielle :

Citation:

Envoyé par CinePhil

=> Tu peux nous dire ce qu'est sensée faire cette requête ?

Citation:

Envoyé par gene69

bon et pour le coup de la transaction non validée, vous en pensez quoi? ça a quel impact sur la réplication (quoi que c'est pas moi qui supporte ce cout là :roll:).

La réponse à la question ci-dessus permettrait peut-être de simplifier la requête et de ne pas avoir besoin du système que tu envisages avec les transactions.

Sinon, as-tu pensé à utiliser des vues plutôt que des tables temporaires ?
04/11/2010, 16h47
gene69

Citation:

Il manque la réponse à la question essentielle :

je suis bête.

ça calcule un indicateur dont la plage est entre 0 et 1 qui exprime la quantité d'élèves qui vont dans un autre cours et s'ils sont représentatif dans le 2e cours, pondéré par le fait que qu'ils sont représentatif ou pas dans le premier cours.

Sauf que la vie est belle, la formule présentée comporte un membre constant dans le produit final du coup ça fait que le pourcentage d'élèves qui vont dans deux cours et leur représentativité.... ce qui se calcul beaucoup plus simplement.

est-ce que utiliser des vues permet à Mysql de faire des optimisations en interne? j'ai pas étudié cette possibilité!
04/11/2010, 16h59
CinePhil

Citation:

Envoyé par gene69

ça calcule un indicateur dont la plage est entre 0 et 1 qui exprime la quantité d'élèves qui vont dans un autre cours et s'ils sont représentatif dans le 2e cours, pondéré par le fait que qu'ils sont représentatif ou pas dans le premier cours.

Sauf que la vie est belle, la formule présentée comporte un membre constant dans le produit final du coup ça fait que le pourcentage d'élèves qui vont dans deux cours et leur représentativité.... ce qui se calcul beaucoup plus simplement.

Wow ! 8O
Pas le temps de m'y pencher maintenant, j'essaierai de comprendre plus tard.

Citation:

est-ce que utiliser des vues permet à Mysql de faire des optimisations en interne? j'ai pas étudié cette possibilité!

Le mécanisme des vues fait qu'en principe la vue utilise les index des tables. Et comme, si j'ai bien compris, tu as plusieurs parties de ta monstrueuse requête qui se répètent, si à la place tu utilises une vue, elle ne sera en principe exécutée qu'une seule fois donc ça devrait optimiser.
Jamais fait l'expérience mais je pense que c'est comme ça que ça fonctionne.