Discussion :

[la_saucisse]

bonjour,

j'ai un petit souci avec une requête.

j'ai 2 tables :

Code :

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

1
2
CLIENT
id, nom, code_postal, ville

Code :

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

1
2
DATAS
id, client_id, type, annee, total

le champ type peut prendre 3 valeurs texte (a, b, c)
total est une donnée numérique

pour chaque client, j'ai 0 à n lignes dans DATAS où sont stockées dans 'total' des couples uniques client/type/année, si elles existent.

j'ai besoin de récupérer un tableau avec un client par ligne et, pour chaque ligne, le total d'un type déterminé (ici 'a' pour l'exemple) pour chaque année disponible (mettons ici, de 2013 à 2015)
soit en gros les colonnes suivantes :
id, nom, code_postal, ville, 'total du type a en 2013', 'total du type a en 2014', 'total du type a en 2015'

donc, ma requête actuelle :

Code :

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

1
2
3
4
5
6
7
SELECT id, nom, code_postal, ville
	,(SELECT total FROM datas WHERE client_id=id AND type='a' AND annee=2013) as t_2013
	,(SELECT total FROM datas WHERE client_id=id AND type='a' AND annee=2014) as t_2014
	,(SELECT total FROM datas WHERE client_id=id AND type='a' AND annee=2015) as t_2015
FROM clients
ORDER BY nom
LIMIT 0,20

ça marche... sauf que j'ai environ 1800 clients et 9000 lignes de datas, ça me prend une grosse dizaine de secondes pour avoir mon tableau, et c'est beaucoup trop long !

vous aurez noté que j'ai un LIMIT x,y qui ne m'affiche que 20 lignes par pages (oui, 1800 lignes sur une page, c'est trop ^^ )
j'ai donc une variable GET qui me permet de changer de page.

donc j'ai essayé ce qui suit, ça va beaucoup plus vite en 2 requêtes :
- une qui récupère uniquement les 20 données CLIENT de la page voulue, ce qui me permet de générer une chaine de client_id (1,2,3,...,20)
- la deuxième qui va chercher les données DATAS, avec

Code :

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

SELECT * FROM datas WHERE type='a' AND client_id IN(...)

(... étant ma chaine obtenue dans la 1ère requête)
j'obtiens un tableau de n lignes, avec 0 à 3 lignes par client_id, selon qu'il existe ou non des couples type/annee pour ce client_id
- enfin, j'assemble les 2 tableaux dans PHP : c'est quasi-instantané.

si ça fonctionne plus vite, youpi, me direz-vous !!!
oui, sauf que j'aimerais pouvoir changer le "ORDER BY nom" en "ORDER BY t_2013" (par exemple), ce qui est possible avec la requête unique, mais pas avec les 2 requêtes.

donc, retour à la requête unique, et ma question : est-elle rédigée comme il faut ? ou il y a t-il une façon plus efficace d'obtenir ce que je veux ?

j'espère que c'est clair, par avance merci pour vos lumières !

[la_saucisse]

et je précise que ma base n'est pas complète : elle devrait passer à environ 3000 clients et 20000 lignes de datas.
si ça mouline déjà maintenant, ça sera encore pire après !

d'où la nécessité d'optimiser le temps d'exécution

[François DORIN]

ça marche... sauf que j'ai environ 1800 clients et 9000 lignes de datas, ça me prend une grosse dizaine de secondes pour avoir mon tableau, et c'est beaucoup trop long !

Effectivement, c'est BEAUCOUP trop long pour si peu de données.

Vu la syntaxe, je suppose que tu utilises MySQL/MariaDB.

Pour commencer, au lieu d'avoir un SELECT qui en fait 3 derrières, utilise des jointures :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT id, nom, code_postal, ville
	,T2013.total as t_2013
	,T2014.total as t_2014
	,T2015.total as t_2015
FROM clients AS C
LEFT JOIN datas AS T2013 ON T2013.client_id = C.id AND T2013.annee=2013 AND T2013.type = 'a'
LEFT JOIN datas AS T2014 ON T2014.client_id = C.id AND T2014.annee=2014 AND T2014.type = 'a'
LEFT JOIN datas AS T2015 ON T2015.client_id = C.id AND T2015.annee=2015 AND T2015.type = 'a'
ORDER BY nom

Ensuite, as-tu les index qui vont bien ? Notamment un index sur la colonne client_id de ta table datas. C'est le strict minimum.

Pour optimiser au mieux cette requête en particulier, tu peux mettre en place un index sur les colonnes client_id, type, et annee (ça tu peux le faire), et qui inclue la colonne total (si c'est possible avec MySQL, je ne connais pas assez ce SGBD pour ça !). Après, vu la taille de ton jeu de données, pour savoir ce qui est le plus optimisé, il n'y a qu'une possibilité : faire des tests !

[edit]
J'avais oublié de tenir compte du champ type !!
[/edit]

[la_saucisse]

merci beaucoup pour cette réponse rapide !
oui, c'est du MySQL, désolé de n'avoir pas précisé.
je vais essayer tout ça, et reviendrai dire ce que ça a donné

[escartefigue]

Ou pour éviter les multi-jointures, comme ceci

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
  SELECT id
       , nom
       , code_postal
       , ville
       , CASE WHEN annee = 2013
              THEN total END AS T_2013
       , CASE WHEN annee = 2014
              THEN total END AS T_2014
       , CASE WHEN annee = 2015
              THEN total END AS T_2015
  FROM       clients CL
  INNER JOIN datas   DA
     ON DA.client_id=CL.id
    AND DA.type='a'
  ORDER BY nom

Ca devrait aller encore plus vite

[aieeeuuuuu]

Bonjour,

Vous pouvez même effectuer une seule jointure, en ajoutant un CASE WHEN :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT id, nom, code_postal, ville
	, MAX(CASE WHEN T.annee=2013  THEN T.total END) as t_2013
	, MAX(CASE WHEN T.annee=2014  THEN T.total END) as t_2014
	, MAX(CASE WHEN T.annee=2015  THEN T.total END) as t_2015
FROM clients AS C
LEFT JOIN datas AS T
    ON T.client_id = C.id 
    AND T.type = 'a'
WHERE T.Annee BETWEEN 2013 AND 2015
GROUP BY id, nom, code_postal, ville
ORDER BY nom

[EDIT]grillé par Escartefigue[/EDIT]
[RE-EDIT]... qui a omis le GROUP BY, normal qu'il soit allé plus vite que moi [/RE-EDIT]

[escartefigue]

[EDIT]grillé par Escartefigue[/EDIT]
[RE-EDIT]... qui a omis le GROUP BY, normal qu'il soit allé plus vite que moi [/RE-EDIT]

Et non j'y ai pensé, mais le total est utilisé directement dans la requête initiale, donc nul besoin de le calculer, c'est pourquoi pas de max ni de groupage

[aieeeuuuuu]

oui, mais la le résultat sera sur trois lignes...

Voire plus s'il y a d'autres années dans la tables des données.
Le filtre WHERE T.Annee BETWEEN 2013 AND 2015 n'est d'ailleurs pas inutile, surtout si un index le supporte...

[escartefigue]

Là d'accord

[la_saucisse]

alors :

@dorinf :
- j'ai ajouté un index à ma colonne client_id... et ça va beaaaaucoup mieux !! je mets résolu rien que pour ça, merciiii
ça semble effectivement la chose indispensable sur la colonne qui sert de liaison avec une autre table, mais on ne me l'avait jamais dit ^^
- j'ai également modifié ma requête avec les JOIN, que je n'ai pas l'habitude d'utiliser, mais je vais m'y mettre
- je n'ai pas l'habitude non plus d'utiliser des alias pour les noms de table, mais ça simplifie la lecture : adopté aussi, je vais m'y mettre !


@escartefigue, @aieeeuuuuu :
houlààà... vos échanges me dépassent un peu, c'est au-delà de mes compétences actuelles

mais je vais prendre le temps d'essayer de comprendre la logique des structures que vous proposez : si ça optimise encore la requête, je suis partant pour tout !
je suis "semi-débutant" en MySQL, je sais que j'ai encore plein de choses à apprendre, et je compte bien le faire.

merci à vous 3 !

[la_saucisse]

message supprimé : je vais plutôt créer une nouvelle discussion.

[edit] : http://www.developpez.net/forums/d16...admin-4-0-2-a/

[escartefigue]

alors :
@dorinf :
- j'ai ajouté un index à ma colonne client_id... et ça va beaaaaucoup mieux !! je mets résolu rien que pour ça, merciiii
ça semble effectivement la chose indispensable sur la colonne qui sert de liaison avec une autre table, mais on ne me l'avait jamais dit ^^
- j'ai également modifié ma requête avec les JOIN, que je n'ai pas l'habitude d'utiliser, mais je vais m'y mettre
- je n'ai pas l'habitude non plus d'utiliser des alias pour les noms de table, mais ça simplifie la lecture : adopté aussi, je vais m'y mettre !

Voilà de bonnes résolutions , pour vous aider il y a ce topic : http://www.developpez.net/forums/d68...q-langage-sql/

Attention, concernant le 1er point : effectivement les index sont le plus souvent très utiles pour les critères de jointure et de filtrage mais pas toujours.
N'allez pas créer un nouvel index à chaque fois que vous aurez un nouveau critère de jointure ou de filtrage !
Cas typique : une colonne code sexe qui prendra tout au plus 2 voire 4 valeurs distinctes (si vous gérez "non déterminé" et "inconnu") ne doit pas faire l'objet d'un index, il serait inutilisé
(sauf problématique d'index couvrant, mais on entre dans des considérations de deuxième niveau)
De plus, les index, mêmes quand ils sont pertinents d'une façon générale, ne sont pas forcément éligibles pour une requete.
Par exemple une requête avec un filtre de type différent (where Col1<>Col2) ou un masque commençant par %(where col1 like '%...') compromet l'utilisation des index
Prudence donc

[François DORIN]

Attention, concernant le 1er point : effectivement les index sont le plus souvent très utiles pour les critères de jointure et de filtrage mais pas toujours.
N'allez pas créer un nouvel index à chaque fois que vous aurez un nouveau critère de jointure ou de filtrage !

Pour compléter : les index ont également un coût. S'ils permettent des optimisations lors d'opération SELECT, ils induisent un surcoût lors des opérations INSERT, DELETE et UPDATE, car il faut les maintenir à jour. Une table avec un index ne pose pas de soucis, mais une table avec 30 colonnes où chaque colonne est indexée, nécessite la mise à jour de 30 index à chaque modification !

[SQLpro]

Pour compléter : les index ont également un coût. S'ils permettent des optimisations lors d'opération SELECT, ils induisent un surcoût lors des opérations INSERT, DELETE et UPDATE, car il faut les maintenir à jour. Une table avec un index ne pose pas de soucis, mais une table avec 30 colonnes où chaque colonne est indexée, nécessite la mise à jour de 30 index à chaque modification !

2 remarques d'ordre général...

• Les index servent aussi pour les mises à jour, notamment UPDATE et DELETE ! L'index de la PK sert aussi pour l'INSERT (effectué plus rapidement si clustered)
• Lors d'un UPDATE, seules les colonnes modifiées vont faire l'objet d'une mise à jour d'index. Donc rarement systématiquement 30...

Après tout dépend de la qualité du SGBDR et en particulier de son optimiseur... Évidemment avec MySQL c'est pas gagné, puisqu'il dispose du plus mauvais des optimiseurs !

A +