Discussion :

[getz85]

Bonjour à tous.

J'ai une requête qui pose problème dans mon programme car elle met bien 30 secondes à s'éxecuter.Cela vient du fait qu'il y a un filtre qui permet de ne pas sélectionner des éléments qui sont dans une liste.
Mais il y a énormément d'élément à comparer!
Voici la requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT DISTINCT s.name, u.realname, dl.name 
FROM software s, users u, computers c, licenses l,inst_software ins, dropdown_locations dl
WHERE c.contact = u.name
AND ins.cID = c.ID 
AND ins.license = l.ID 
AND l.sID = s.ID 
AND dl.id = u.location 
AND s.name 
NOT IN('³öÆæÖÆʤ£*¸ß¿¼Ó¢Óï','#Supprimé','Acrobat Reader 4.0',
'WinZip','Windows 2000 Support Tools','Windows 2000 Service Pack 4',
'Windows 2000 Hotfix Special Release ...........

le NOT IN continue sur 500 logiciels environ puis fin de la requête :

Code :

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

1
2
3
4
5
 
,'Windows Driver Package - FTDI FTDI VCP Driver Package','Windows Driver Package - FTDI CDM Driver Package',' ')
 AND dl.completename 
IN('Informatique')  
ORDER BY dl.name,u.realname

Voila j'aimerais savoir comment rendre cette requête plus rapide.

Merci d'avance

[scheu]

sans infos sur le SGBD, les volumétries, les indexes existants, ... etc difficile de deviner

[getz85]

sans infos sur le SGBD, les volumétries, les indexes existants, ... etc difficile de deviner

Désolé
Le SGBD c'est MySql.
Qu'entends tu par volumétrie et indexes existants? le nombre d'occurences des tables?

[vmolines]

Execute la requête que tu nous as présentée précédée du mot clé "EXPLAIN" comme celà :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
EXPLAIN SELECT DISTINCT s.name, u.realname, dl.name 
FROM software s, users u, computers c, licenses l,inst_software ins, dropdown_locations dl
WHERE c.contact = u.name
AND ins.cID = c.ID 
AND ins.license = l.ID 
AND l.sID = s.ID 
AND dl.id = u.location 
AND s.name 
NOT IN('³öÆæÖÆʤ£*¸ß¿¼Ó¢Óï','#Supprimé','Acrobat Reader 4.0',
'WinZip','Windows 2000 Support Tools','Windows 2000 Service Pack 4',
'Windows 2000 Hotfix Special Release ...........

Explain demande à MySQL d'executer la requête, mais au lieu de retourner le résultat de cette requête, il retourne sa manière de traiter ta requête. Ainsi tu pourras voir apparaître les manques en matière d'indexes.

Donne le résultat de cette exécution ici.

[getz85]

Merci de ta réponse.
Voila le résultat

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ins ALL cID,sID NULL NULL NULL 102363 Using temporary; Using filesort
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 glpidb.ins.cID 1
1 SIMPLE u ref name,name_2,location name 766 glpidb.c.contact 10 Using where
1 SIMPLE l eq_ref PRIMARY,sID PRIMARY 4 glpidb.ins.license 1
1 SIMPLE s eq_ref PRIMARY PRIMARY 4 glpidb.l.sID 1 Using where
1 SIMPLE dl eq_ref PRIMARY PRIMARY 4 glpidb.u.location 1 Using where

Désolé, ce n'est pas très lisible...

[vmolines]

Si vous enlevez le distinct, y-a-t-il du mieux ? Et avez vous vraiment besoin du distinct ?

Tout le reste de la requête (jointures et clause where) s'execute en utilisant des indexes.

[getz85]

Je gagne 0.4 secondes si j'enleve le distinct...Et je ne pense pas qu'il soit utile au final...

Mais bon ça n'arrange rien...Une autre idée?

[vmolines]

Si c'est cette liste de valeur dans le <NOT IN> qui pose vraiment problème, il y a quelquechose à tester.

Vous créez une table temporaire en mémoire. Je ne me rappelle plus la syntaxe exacte MySQL mais je sais que ça existe. Cette table est unique pour chaque connexion donc les utilisateurs n'auront pas de problème de concurrence.

Cette table contient les valeurs sélectionnées pour votre filtre. Dans votre application, quand les utilisateurs ajoutent un critère, vous l'ajoutez dans cette table. A l'inverse, vous supprimez s'ils déselectionnent le critère.

Votre requête n'aura plus de NOT IN mais une jointure vers cette table qui sert de filtre.

Déclarez correctement cette table. Ne stockez que les identifiants sélectionnés, pas les noms, ce sera plus efficace.

[getz85]

Merci vmolines

En fait, les filtres sont ajoutés ou retirés par l'administrateur de l'application, et ils valent pour tous les utilisateurs...
Ces filtres sont enregistrés dans un fichier texte.
Donc une table commune contenant les logiciels filtrés à la place de ce fichier suffirait...


Mais ça ne m'arrange pas trop, car cela prendrait du temps pour modifier tout le code, et je dois finir cette apllication au plus vite...

Je retiens ton idée, au cas ou je ne trouve rien d'autre.
Une autre idée?

[vmolines]

- Utilisez s.id au lieu de s.name pour filtrer
- Exprimez vos jointures avec JOIN

[getz85]

Je ne peux pas utiliser s.id car le fichier texte ne comprend que l'intitulé.

Quant à mettre les jointures en join cela m'a fait gagner quelques secondes!
Merci!
Par contre ne sachant pas bien manipuler les join, peux-tu me dire si je l'ai fait correctement?

Code :

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

1
2
3
4
5
6
7
8
 
SELECT DISTINCT s.name, u.realname, dl.name
FROM software s, computers c
INNER JOIN users u ON c.contact = u.name
INNER JOIN licenses l ON l.sID = s.ID
INNER JOIN inst_software ins ON ins.cID = c.ID
AND ins.license = l.ID
INNER JOIN dropdown_locations dl ON dl.id = u.location

[vmolines]

La syntaxe est bonne mais ne connaissant pas la structure de tes tables ni le résultat que tu veux obtenir, je ne peux te dire si ton arbre de jointure est bon.

Dans les jointures, il est dommage de lier USER à COMPUTER par le nom, ce n'est pas efficace. Il faut utiliser les identifiants pour faire ses liaisons.

Par contre, ta méthode pour filtrer à partir des noms saisis dans un fichier texte est mauvaise. C'est pas grand chose en développement de lister des identifiants plutôt que des noms.

Pour tester le gain apporté par un filtrage sur identifiant plutôt que par nom (avant de se lancer dans un dev) :

- Fais une requête qui ramène les identifiants à partir des noms
- Modifie ta requête pour qu'elle utilise cette liste d'identifiants
- Execute et vois le gain apporté.

[getz85]

oui je sais bien que ce n'est pas une bonne méthode, mais je dois modifier un programme qu'une personne a fait, et elle a choisi de faire comme ça...
Je n'y suis pour rien!
Ce ne sont pas les identifiants qui sont listés car le fichier texte alimente une listbox pour les admins...

Pour la jointure entre user et computer je n'ai pas le choix, car la BDD est généré par un programme (appelé GLPI) et c'est le seul lien qu'il y a entre ces deux tables...
Je vais essayer de faire ta méthode de récupérer les id des logiciels filtrés puis comparer les id.

En tout cas merci beaucoup vmolines!

[vmolines]

Je n'avais pas vu la fin de la requête (mauvaise lecture)

Code :

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

1
2
3
AND dl.completename 
IN('Informatique')  
ORDER BY dl.name,u.realname

Je vous ai fait enlevé le distinct mais testez aussi en enlevant le <ORDER BY>. C'est la dessus que MySQL peine car, sans index sur les champs du order, il doit faire un parcours complet de la table (voir 1ere ligne de l'explain). Si je ne me trompe pas, ça sera bien plus rapide sans le <ORDER BY>, et là on verra comment améliorer.

Même remarque pour le fitlre sur dl.completename, préférez un identifiant qu'un nom.

[vmolines]

oui je sais bien que ce n'est pas une bonne méthode, mais je dois modifier un programme qu'une personne a fait, et elle a choisi de faire comme ça...
Je n'y suis pour rien!
Ce ne sont pas les identifiants qui sont listés car le fichier texte alimente une listbox pour les admins...
...

Rien ne vous empèche d'afficher des noms pour les utilisateurs et de stocker/récupérer les identifiants sous jacents pour vous. C'est toujours ainsi qu'il faut procéder. Une fois la sélection de l'utilisateur validée, vous injectez tous les identifiants récupérés dans votre IN.

[vmolines]

A chaque fois ce sont des hypothèse que je fais. Ce serait plus facile avec la description des tables présentes dans la requête et les liens entre elles. Vous devez toujours donner la structure de vos tables quand vous demandez de l'aide.

[getz85]

Ok désolé...
J'y penserais la prochaine fois!
Je vais essayer de récupérer l'ID a la place du completename.
Mais c'est déjà un peu plus rapide comme ça.Merci!

[vmolines]

Il ne faut pas que ce soit "un peu plus rapide". Une requête comme celle là devrait avoir un temps d'execution ridicule. Quelques secondes, c'est déjà trop je trouve.

[SQLpro]

Faîtes comme ceci :

Code :

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

CREATE TABLE application (NOM VARCHAR(256) PRIMARY KEY)

au besoin par une table temporaire locale

Code :

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

1
2
3
4
5
6
INSERT INTO application  VALUES ('³öÆæÖÆʤ£*¸ß¿¼Ó¢Óï')
INSERT INTO application  VALUES (','#Supprimé')
INSERT INTO application  VALUES ('Acrobat Reader 4.0')
INSERT INTO application  VALUES ('WinZip')
...
INSERT INTO application  VALUES ('Windows Driver Package - FTDI CDM Driver Package')

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT DISTINCT s.name, u.realname, dl.name 
FROM   software s
       INNER JOIN licenses l
          ON l.sID = s.ID 
       INNER JOIN inst_software ins
          ON ins.license = l.ID 
       INNER JOIN computers c
          ON ins.cID = c.ID 
       INNER JOIN users u
          ON c.contact = u.name
       INNER JOIN dropdown_locations dl
          ON dl.id = u.location
WHERE  s.name NOT IN (SELECT NOM 
                      FROM  application )

-- vérifiez la présence des index suivants :
software (id)
licenses (sid) (id)
inst_software (licence) (cid)
computers (id) (contact)
users (name) (location)
dropdown_locations (id)


A +

[getz85]

Merci SQL Pro.

Même sans crééer de table application (ce qui serait long et fastidieux étant donné le nombre de logiciel à filtrer), la requête s'execute beaucoup plus rapidement maintenant.
Les indexs sont bien tous présents.