Salut.
 
Y'a un moment déjà, j'avais indiqué que ce qui a le plus d'importance après les index, c'est l'ordre des tables dans la clause FROM.
 
Je reviens à la charge avec une petite modification : dans certains cas (j'ai pu tester), c'est bien plus important que les index, y compris pour les tables insignifiantes (celles qu'on ne se soucie généralement pas trop d'optimiser).
 
Comment ça marche ?
 
Les SGBD (tous ?), du moins, Oracle et SQL Server sont TRES sensibles à l'ordre des tables dans la clause FROM. Avec ou sans clés étrangères explicites, ces deux SGBD en tout cas, font confiance à l'ordre des tables dans la clause FROM pour optimiser leur plan d'éxécution.
 
Première chose qu'on remarque : les SGBD lisent cette clause "à l'envers". En effet, alors qu'on serait tenté de mettre "le point d'entrée" de la requête en premier, c'est au contraire en dernier qu'il faut le mettre !
 
Exemple basic :
J'ai une table "SOCIETE", avec les champs "SOC_ID" et "NOM". J'ai une table "EMPLOYE" avec les champs "SOC_ID", "EMP_ID" et "NOM". J'ai ensuite une table "CONGES" avec les champs "EMP_ID", "DATE_DEB" et "DATE_FIN".
 
Je veux retouver la durée totale des congés de tous les employés toutes sociétés confondues, pour l'année 2004.
Voici ce qu'on serait tenté d'écrire, ici avec la syntaxe SQL Server.
Vous noterez que je n'utilise pas la syntaxe "... INNER JOIN ... ON ...", pour deux raisons :
- Le parseur SQL se moque éperduement de cette syntaxe, ça ne l'aide en rien à faire les requêtes.
- Je trouve cette syntaxe lourde, et particulièrement difficile à lire, notamment quand on a un grand nombre de tables ou/et des jointures entre plusieurs tables à la fois.
- Pour moi, le critère de jointure est un filtre comme un autre. Je préfère donc le spécifier dans la clause WHERE dédiée aux filtres.
- Contrôler l'ordre des tables dans la clause FROM quand on utilise une telle syntaxe est quasi-impossible à faire.
 

 
Cette requête marche très bien. Etant donné la simplicité de cette dernière, l'odre des tables dans la clause FROM n'impactera que très peu la rapidité globale. Cependant, c'est une véritable catastrophe niveau optimisation !
 
Rappelez-vous, j'ai dit que le point d'entrée de la requête devait se trouver en dernier. Penser que "CONGES" avec le filtre sur l'année est le point d'entrée est une hérésie. Un point d'entrée est par définition la table la plus restrictive. Il y a normalement moins de sociétés que d'employés et moins d'employés que de congés (ou alors y'a un problème )
La requête optimisée aura alors pour syntaxe :
 

 
Le changement n'est pas flagrant, mais il est suffisant pour améliorer considérablement les performances.
Vous noterez que l'odre des éléments de la clause SELECT et de la clause WHERE n'ont strictement aucun impact sur les performances.
 
Maintenant, un cas moins évident.
J'ai une table "EMPLOYE", avec la même structure que précédement. J'ai une table "DROITS" avec les champs "EMP_ID", "PROG_ID" et "TYPE_DROIT". Une autre table "PROGRAMME", avec les champs "PROG_ID" et "NOM", et enfin une table "TYPE_DROITS" avec les champs "TYPE_DROIT" et "LIBELLE".
 
Je veux retrouver tous les droits qu'on les utilisateurs de la société "Microsoft" sur le programme "Excel".
 
Premier jet :

 
Maintenant, on va réfléchir un peu.
 
J'ai 50 programmes différents.
J'ai 10 sociétés
J'ai 50 000 employés
J'ai 2 types de droits
J'ai 250 000 droits
 
Quel est le point d'entrée ? (donc qu'on va mettre en dernier, puis précéder des tables les plus restrictives suivantes)
Du tac au tac, on se dit "table la plus restrictive = table où y'a le moins de lignes, donc c'est type_droits".
MIIIP ! Mauvaise réponse.
La table la plus restrictive est la table qui, AVANT filtre contient un maximum de données, mais APRES filtre en contient le moins.
C'est donc soit PROGRAMME, soit SOCIETE. Pour programme, on grade seulement 2% des lignes (1/50) alors que pour la table société, on en garde 10% (1/10).
On garde donc la table "PROGRAMME", qui est la plus restrictive.
Ensuite, on est tenté de mettre la table "SOCIETE" en second. En effet, c'est la seconde la plus restrictive.
Ratté. En effet, les SGBD n'aiment pas trop devoir boucler sur plusieurs tables à la fois sans savoir qui est rattaché avec qui. La seconde sera donc "DROITS". Entre "EMPLOYE" et "TYPE_DROITS", qui est la plus restrictive ensuite ? Type_droits ? Non. Pourquoi ? Parcequ'on ne va pas la filter du tout. A partir de là, elle n'apporte rien à l'optimisation, mise à par ajouter un varchar en mémoire pour chaque ligne de droits répondant à l'application "Excel". "EMPLOYE", par contre, va nous premettre de filtrer énormément dès qu'on va le lier à "SOCIETE". Allez, zou, on ajoute donc "EMPLOYE" et "SOCIETE". Et enfin, on rajoute "TYPE_DROITS". C'est pas vraiment ce qu'on avait imaginé au début !
 
Ca donne donc, après optimisation :
 

 
Vous noterez que "par chance", la version "optimisée" est quasi identique à la requête qui était venue littéralement. Ne vous y fiez pas, c'est uniquement parcequ'il y avait plus de programmes de que sociétés. Si les nombres de lignes de ces deux tables avait été inversés, ça aurait été totalement différent :
 

 
Voilà. Je sais pas si ça va être utile à certains, en tout cas, après avoir été confronté à une requête qui mettais plus de 2 minutes à s'éxécuter hier, j'ai juste interverti deux tables en appliquant cette logique, et le temps d'éxécution est tombé à 3 secondes ! Pourtant, les index utilisés notamment n'ont pas changés entre les deux versions, et les deux tables interchangées contenaient respectivement 68 et 12 lignes, sâchant que dans les deux cas, je ne gardais qu'une seule ligne, retrouvée en utilisant l'index unique de la primary key clusterée. Comme quoi, c'est pas de l'omptimisation en l'air !

c'est plus long que je croyais, je lirai ça demain mating

Flemmard

C'est interressant mais t'as des chiffres ?
Si tu retestes avec des chiffres essaies de le faire sur une base dont t'as les scripts et file tes sql de test que jpuisse tenter sur mes bases (je bosse sur sql server, oracle et firebird régulièrement, et sur d'autres du style mysql et access de temps en temps...) j'aimerai bien vérifier tout ça

Ben là, ça va être chaud, parceque la base en question fait quelques centaines de méga, et certaines tables font plusieurs centaintes de milliers de lignes...
Sans parler de la requête avec laquelle j'ai obtenu le résutlat (passer de 2 minutes à 3 secondes, c'est quand même pas mal ) qui fait plus d'une cinquantaine de lignes (jointures sur une douzaine de tables, avec un joli union au milieu )
 
Mais demain, si j'y pense, je vais me faire une petite base de test pour valider ces résultats, et pourquoi pas affiner la méthode. En effet, j'ai remarqué en écrivant le topic qu'il y a une légère incohérence par rapport à la requête que j'ai ainsi optimisé. M'enfin globalement, ça reste vrai tout de même, mais pas parfait.

comme Glod2, je veux des chiffres !
 
mais si les chiffres sont probants, ce serait un bon plan que je m'y plonge
 
quid des Sybase, DB2/400 et mysql ?

L'idéal ce serait que tu essayes sous Oracle aussi, car il me semble que c'est un peu différent, et puis ça dépend du mode d'optimisation choisi (stats / rule).
Il me semblait que le SGBD ne prenait pas nécessairement les tables dans l'ordre inverse à celui indiqué ... mais j'ai fait quelques test simples et c'est apparemment le cas aussi sous Oracle
 
En tout cas, sous Oracle, on peut utiliser un "hint" pour s'assurer de l'ordre d'utilisation des tables :
 

 
Cette requête lira CUSTOMER d'abord, puis ACCOUNT ...

Jamais bossé sur ces 3 bases, donc je ne peux rien dire
 
Cela dit, si Oracle et MS SQL Server, qui sont deux des ténors des SGBD courants, en profitent, je suppose que ce fonctionnement est applicable à la plupart des autres SGBD.
 
A vérifier tout de même.
 
Là, j'ai rien à faire au boulot, je ne sais pas pour combien de temps. Je vais commencer à me faire une base de test, et si j'ai le temps de faire des mesures, je vous préviens, sinon il faudra attendre ce soir

Bon, le script d'initialisation d'une base simple est lancé.
 
Le volume de données ne va pas être énorme, mais vu que je tourne sur un Pii 400 avec 384 Mo de mémoire et un HD à 5400 trm
 
Arf, c'est marrant, au boulot il vient d'y avoir une micro-coupure de courant. Mon PC est tellement lent qu'il s'en est même pas rendu compte Tous les autres ont rebooté... Ironique, puisque je suis le seul à pas travailler
 
 
Le script d'allimentation de la base (SQL Server) :
J'espère que ça sera quand même représentatif d'une vraie base. (je mettrai le script de la base quand il aura fini, parceque là, ça ramme à mort, dans le forum ça écrit ce que j'écrit 20 secondes après, c'est pratique pour corriger les fautes de frappe )
 

 
En 6 minutes, il a fait 52 passages dans la boucle principale. C'est pas gagné

Ca ne s'applique pas a MySQL, c'est l'optimizer qui va decider dans quel sens doit se faire la jointure.
Si tu veux specifier explicitement l'ordre de la jointure et court-circuiter l'optimizer il faut utiliser la syntaxe  A STRAIGHT_JOIN B au lieu de A,B. (donc la majorite des cas MySQL ordonne les tables de facon optimale, sauf cas assez rare et un peu tordu )

En effet, comme je l'ai souligné, l'ordre des tables est fortement influencé par leur contenu. Ainsi, les optimisations qui sont vraies aujourd'hui ne le sont pas forcément demain (d'où ma réaction aux questions du genre "donnez-moi vos règles principales pour optimiser un serveur SQL", phrase dénuée de sens, puisque ça dépend de "tout" ).
 
Comme l'a souligné Beegee, sous Oracle (peut-être sur SQL Server aussi, je ne maîtrise pas suffisament) on peut indiquer au parseur SQL différents modes d'optimisation. Tous ne sont pas aussi impactés par l'ordre des tables. Cela dit, cela reste le paramètre par défaut de ces deux SGBD, et pas conséquent, ce qu'on trouve en exploitation sur une très large majorité des serveurs.
Sinon, pour ce qui est de l'automatisation, c'est pas si évident à faire. Moi, je connais d'un point de vue logique ma base, et j'ai une idée précise du volume traîté et du volume attendu.
Le SGBD est incapable d'avoir une vision aussi globale. Donc pour optimiser, il faudrait qu'il fasse la requête par tous les moyens d'optimisation connus, puis qu'il garde la meilleur pour les utilisations ultérieures, ce qui est un peu lent
Deplus, la phase d'optimisation étant, dans la majorité des cas, la phase la plus lente de l'éxécution d'une requête, faciliter la tâche au parseur n'est pas plus mal, rien que là, on gagne en temps, sans compter sur l'exécution elle-même.

C'est le cas aussi d'Oracle et SQL Server normalement. Sur une requête "simple", on a rarement des problèmes de ce genre, l'optimisation montre des résultats très faibles.
Cela dit, comme tu dis, dans les cas "tordus" (et quand on bosse sur des applis comme celles sur lesquelles je bosse, y'a que ça ), je pense que MySQL va lui aussi s'emmêler les pinceaux, et à ce moment, l'optimiseur prend les tables dans l'ordre indiqué dans la clause FROM.
Je pense que dans tous les cas, étant donné que cette optimisation est parmis les plus simples possibles, il vaut mieu la faire systématiquement, au moins on est sûr d'avoir le meilleur résultat possible
Allez Joce, réécris tout de suis le forum

Joce, d'ailleurs, tu devrais pouvoir confirmer. Il me semble avoir lu (mais ça fait un bail) que pour l'optimiseur, en réalité, il tente de trouver le moyen d'exécuter la requête le plus rapidement possible (logique).
 
Mais si au bout d'un délais X il n'a pas encore trouvé d'optimisation satisfaisante, il annule tout, et lance la requête sans optimisation, afin d'éviter de perdre trop de temps à optimiser (en fin de compte, c'est con de passer 5 minutes à optimiser une jointure super complexe entre 100 tables, si chacune contient 2 lignes )
 
Je pense que si c'est bien le fonctionnement, c'est à ce moment que cette syntaxe devient vraiment utile.

Sinon, quand je parle de requêtes "complexes", c'est de ce type de truc que je parle
 
C'est cette requête qui est passée de 2 minutes à 3 secondes après modification
 

 
Vous comprendrez que je vais avoir du mal à vous fournir une base de test correspondant

t'inquietes le forum est deja ecrit de facon optimale au niveau des requetes
 
Pour MySQL, y a bcp de changement qui ont ete introduit dans MySQL 5 et qui permettent de controller le comportement de l'optimizer et l'heuristique qu'il va utiliser dans le cas des jointures.
Je te recommande de lire :
 
http://dev.mysql.com/doc/mysql/en/ [...] mizer.html
 
 

Je lirai ça plus tard Je bosse pas du tout avec MySQL
 
Bon, voilà ma base de test :

 
Vais boire un café, et après je fais quelques tests, mais j'ai peur qu'il y ait trop peu de tables pour que ce soit significatif

j'ai deja eu un cas ou sur deux tables l'optimizer se plantait dans l'ordre de la jointure a cause d'un mauvais calcul de la cardinalite par l'optimizer une honte

Bon, c'est chiant, la base est trop petite. Vu que toutes les requêtes (sauf une) mettent 0 secondes à tourner, je ne peux rien mesurer.
 
Par contre, j'ai remarqué un point relativement important dans le domaine des optimisation.
 
T1
-----
t1_id PK / Culstered
nom
 
T2
-----
t1_id PK / Clustered / FK vers t1.t1_id
t2_id PK / Clustered
nom
 
T1
-----
t3_id PK / Culstered
nom
 
T23
-----
t1_id PK / Clustered / FK vers t2.t1_id et t2.t2_id
t2_id PK / Clustered / FK vers t2.t1_id et t2.t2_id
t3_id PK / Clustered / FK vers t3.t3_id
libelle
 
Il y a quelques temps, j'ai annoncé, sûr de moi, que les FK ne servaient à rien au niveau optimisation des requêtes de type SELECT. En réponse, on m'a volé dans les plumes en me disant que c'était totalement faux.
J'ai la preuve, chiffres à l'appui que si, ça ne sert ABSOLUMENT à rien. Même pire, ça peu, dans certains cas, ralentir les requêtes, à cause de l'ajout de tables de correspondances inutiles.
 
Si je suis mes FK, je dois passer par T2, puis vers T1 pour lier T23 à T1.
Mais vu que T23 contient t1_id, je peux aussi me passer du passage par T2, et faire fi des FK.
 
Requêtes :

 
Les résultats sont sans appel : passer par la FK est énormément plus lent que de passer outre.

les FK servent surtout de garde-fou quand on fait joujou avec les datas (non ?)

Ben oui. Mais les index générés par les FK n'apportent rien à l'optimisation.

J'ai quelques nouveaux chiffres.
 
Je viens de changer une procédure du même type que celle que j'avais posté. Je suis passé de 19 secondes à 2 secondes, en prenant comme point d'entrée une table qui à première vue n'y avait rien à faire.
 
J'ai un user. Un user a accès à plusieurs organisations. Une organisation contient des schedule. Un schedule contient des contract. Un contract contient des contractline. Une contractline correspond à un loyer. Un loyer correspond à un asset. (plus quelques autres tables, mais ça c'est les principales).
 
Dans l'asset, j'ai toutes les FK correspondant aux autres tables (sauf loyer, qui est une table de correspondance).
 
Au départ, j'étais parti de l'utilisateur, puis des organisations, des schedules, etc.
 
Finalement, je suis parti d'Asset. Et c'est plus rapide !
 
En fait, plus ça va, et j'ai l'impression que la méthode est légèrement différente : il faut que la table contenant le plus de lignes soit la première. Ca expliquerait pourquoi MySQL peut optimiser de façon plus efficace que les autres SGBD, puisque le COUNT(*) est stocké dans la base pour chaque table, alors qu'aucun autre SGBD ne stocke cette infos, résultat, il ne peuvent que faire une approximation du nombre de ligne lors de l'optimisation.

Pour résumer le bordel :
 

j'ai aussi des pbs de lenteur avec MySql pour une requête issue d'un moteur de recherche multicritères. Pourtant, j'ai pas tant que ça d'enregistrements :
- 5000 demandes
- pour chaque demande, un historique de 8 enregistrements en moyenne
 
Ca doit bien prendre 7 ou 8 secondes pour trouver les demandes et encore, je pagine avec LIMIT, mais ça ne me fait pas gagner tant que ça de temps. Mon principal pb, c'est que je travaille avec un mysql 3.23.58 qui ne gère pas les requêtes imbriquées : bilan, je dois passer par une table temporaire J'ai tenté une optimisation, mais elle marche que dans certains cas (ça dépend des critères sélectionnés par l'utilisateur)... Ta piste est intéressant en tout cas et je vais regarder mysql 5.

Question bête : t'as des LIKE ?
Si oui, alors c'est certainement eux qui plombent ta requête.
Essaie de voir si tu peux implémenter (et utiliser ) les fonctions d'indexation de texte, afin d'utiliser FREETEXT et CONTAINS notamment. C'est infiniment plus rapide, et ça permet de faire des recherhces plus naturelles.

 
Oui, j'ai du LIKE (plusieurs même). Le soucis, c'est que je ne veux pas faire du SQL trop spécifique à MySQl, l'un des principes de l'appli étant d'être indépendante du SGBD...

Les fonctions de texte intégral sont relativement similaires d'un SGBD à l'autre.
 
Je te conseille de faire ce que j'ai fait pour mon site Manga-Torii.
Une fonction PHP qui génère le code SQL correspondant à l'utilisation des index de texte, que tu colles au reste de ta requête plus générique.
Ainsi, tu pourras aisément faire les adaptations selon les SGBD utilisés (voir une version avec like) sans casser la requête.
 
Pour information, SQL Server utilise les mêmes procédures que MySQL, plus d'autres supplémentaires (avec quelques déviances niveau syntaxe cependant)
Oracle, quant à lui, a une syntaxe plus spécifique.

j'ai une petite question concernant ta requete au dessus. Y'a combien d'enregistrements à 'scanner'? Quelle est le volume de la plus grosse tables?
 
Bref, des details permettant de mettre en relation les 2 secondes d'execution et le volume.
 

Comme autre SGBD, je pensais entre autre à Oracle, mais surtout à PostgreSql.
 
T'aurais un ex de ta fonction de cote sql à me montrer? je pense avoir saisi le truc, mais je voudrais m'en assurer avant de recoder cette partie. Merci

cinocks:

 
Tiens, y'en a beaucoup moins que ce que j'avais prévu
 
Ca va faire mal, parcequ'on va pas tarder à avoir plus de 500 000 lignes dans la table asset, avec au minimum le double dans la vue sdViewAttributesValues
 
Va falloir optimiser encore un peu je crois

J'ai rien compris du tout
 
Tu veux quoi ?

Ah, ok, je viens de piger. Euh... Si j'arrive à me connecter au FTP de mon site ce soir, je te poste ma fonction (elle est fait un peur, et est améliorable )

 
 
je parle de cette fct

 
bah, même pas peur

 
Tout depend du serveur et de sa puissance derriere. Mais ta requete ne tiendra pas longtemps une montée en charge. Car pour le moment, il n'y a pas bcp de lignes dans les tables. Etant donné que tu passes par une procedure stockée, autant en profiter pour decouper ta requete en plusieurs sous-requetes et tables de travail.  
 
Avec ton union, tu as deux blocs requetes qui au debut recupere les memes données. Ce n'est qu'à partir du and ls.userights = 1 qu'elles se distinguent. Du coup, ce qui se fait avant est identique entre les 2 blocs.  
Pourquoi ne pas mutualiser ce qui est identique par requetes intermediaires et tables de travail, puis partir de ce resultat pour construire le reste.
 
Personnellement, c'est ce que l'on utilise au boulot, et ce sont des volumes variant entre 20 000 000 et 30 000 000 de lignes  dans les tables.

Parceque potentiellement, la requête peut retourner TOUTES les lignes (tous les filtres sont facultatifs). Par conséquent, si je fait ça dans des tables temporaires, je fais passer 2 heures à recopier toutes les données dans la base tempdb, jusqu'à planter le serveur par manque d'espace.
 
Deplus, étant donné que les filtres se trouvent à tous les niveaux, et que la notion de droit impact les filtres sur plusieurs tables, stocker des infos selon un nombre limité de filtres va provoquer le stockage d'un nombre important de lignes qui ne seront très certainement pas retenues dans les filtres finaux.

Tout depend du nombre de lignes retournée depuis asset quand les filtres sont inactifs. Quel est le % d'enregistrements retournés si tu executes:
 
# and a.contractlineid = (select max(cl2.contractlineid)
#                      from contractline cl2
#                      where cl2.leasecontractid = a.leasecontractid
#                      and cl2.active = 'A')
 
 
idem cette sous-requete peut se preparer pour devenir
 
select cl2.leasecontractid, max(cl2.contractlineid)
into #contractline  
from contractline cl2
where cl2.active = 'A'
group by cl2.leasecontractid
 
Ca evitera de faire autant d'aggregats qu'il n'y a d'enregistrement dans a.

bon je profite de ce topic pour reposer une question auquel personne ne ma encore repondu : comment peut on faire pour implementer facilement dans sql server un systeme de gestion des droits des utilisateurs ??? ex: tel user a le droit de modifier tel colonne ou tel ligne ........

cinocks > il y a très peu de "doublons" dans la table contractline. En fait, chaque nouvelle ligne correspond à un avenant à un contrat. Hors, un contrat qui arrive à échéance, dans le mode de fonctionnement de la bdd dans laquelle va tourner ma requête, un nouveau contrat est créé. Donc je dirais que ce "max" va rammener entre 90 et 100% des lignes. Je pense qu'il n'est donc pas utile de le compiler à l'avance.
 
Ensuite, il va y avoir environ 40 000 clients. Chaque client ayant 1 à 5 contrats actifs dans la base, donc ça fait pas mal de lignes à mettre dans la table temporaire.
 
Quand j'aurai les données réelles, je ferais peut-être le test, mais je pense que je vais perdre du temps plutôt qu'en gagner avec le sous-select. En effet, les tables étant bien indexées, le nombre de lignes n'influe que très peu sur la vitesse d'éxécution de la requête. Par contre, dès que le volume de données augmente, l'utilisation de tables temporaire plombe tous les traîtements.

red faction > ben à partir de l'interface, ça marche tout seul. sinon, il y a des fonction systèmes qui permettent de gérer les droits dans sql server.