Discussion :

[StringBuilder]

Bonjour,

Savez-vous où trouver une liste de "petites optimisations" et règles afin d'améliorer les performances d'une requête "de façon universelle", c'est à dire indépendamment de son plan d'exécution.

J'entends par là par exemple :
- champ1 = substr(champ2, x, y) plus ou moins rapide que champ2 like '%' || champ1 || '%'
- champ1 = substr(champ2, 0, x) plus ou moins rapide que champ2 like champ1 || '%'
- champ1 = substr(champ2, x, EOF) plus ou moins rapide que champ2 like '%' || champ1
- champ1 || champ2 plus ou moins rapide que concat(champ1, champ2)
- pour un index (champ1, champ2, champ3), "champ1 = x and champ2 = y and champ3 = constante" plus ou moins rapide que "champ1 = x and champ2 = y" quand on sait que champ3 est toujours égal à "constante" ?
- "t1 inner join t2 on t2.champ1 = t1.champ1 and t2.champ2 = t1.champ2" plus ou moins rapide que "t1 inner join t2 on t2.champ1 = t1.champ1 and t2.champ2 = constante" quand on sait que t2.champ2 sera toujours égal à "constante"

Etc.

[lola06]

Salut,

Le seul tuto que je connaisse est celui de SQLPro. Je le trouve assez bien même s'il ne va pas autant dans le détail de ce que tu voudrais avoir...

[StringBuilder]

Le souci, c'est que SQLPro est plutôt orienté SQL Server.

Et entre SQL Server et Oracle, il y a quelques abérations au niveau des optimisations (quand l'un préfère certaines choses, l'autre préfère parfois le contraire).

Et là mon problème est justement qu'en désespoir de cause, je tente le tout pour le tout, pour essayer de faire des "micro-optimisations", espérant que rapporté aux centaines de millions de lignes traitées, ça change quelque chose...

Par exemple, la sous-requête sur un IN est plus rapide sous Oracle que le EXISTS (ceci dit, il me semble que c'est le cas aussi pour SQL Server).

Après des tests fais il y a quelques années, c'est même 2 ou 3 plus plus rapide d'utiliser un IN qu'un EXISTS.

A moins que ça ait encore changé, ce qui ne m'étonnerait pas plus que ça :/

[Scriuiw]

Attention au IN et au EXISTS qui ne te donneront pas nécessairement les mêmes résultats

[StringBuilder]

En tout cas, y'a un truc avec Oracle que je ne comprends pas...

... et je suis à deux doigts de dire des grossièretés à l'encontre de ce "SGBD".

Depuis des années, tout le monde s'égosille (moi le premier) quand il voit une requête du type :

Code sql :

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

1
2
3
4
 
select t1.champ1, t2.champ2
from t1, t2
where t2.t1_id = t1.id;

Et moi, systématiquement, je converti :

Code sql :

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

1
2
3
4
 
select t1.champ1, t2.champ2
from t1
inner join t2 on t2.t1_id = t1.id;

Arguments :
- C'est la syntaxe standard
- Vu qu'on stipule explicitement les jointures, le SGBD est capable d'optimiser au mieux les jointures

C'est parfaitement vrai avec SQL Server.

Mais franchement, et c'est là que ça me gonfle vraiment, c'est absolument FAUX pour Oracle.

Pire :

Code sql :

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

1
2
3
4
 
select t1.champ1, t2.champ2
from t1, t2
where t2.t1_id = t1.id;

Peut mettre le double de temps à s'exécuter que :

Code sql :

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

1
2
3
4
 
select t1.champ1, t2.champ2
from t2, t1
where t2.t1_id = t1.id;

=> Notamment si t1 contient moins de lignes que t2, ou que les clauses de filtre permettent de filtrer plus fortement t1 que t2.

Il est alors très facile de tester divers ordres pour optimiser les requêtes, et ça marche.

Tandis que :

Code sql :

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

1
2
3
4
 
select t1.champ1, t2.champ2
from t1
inner join t2 on t2.t1_id = t1.id;

et

Code sql :

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

1
2
3
4
 
select t1.champ1, t2.champ2
from t2
inner join t1 on t2.t1_id = t1.id;

Ça merder à plein tuble, il ne sait plus utiliser les index (je viens de tester).

Du coup, là ça fait plusieurs mois que je suis en clientèle.
Je suis vendu par ma boîte comme CP sur un ERP, mais moi j'ai bien insisté sur ma bonne maîtrise du SQL (et même si je me sens souvent petit pandawa sur ce forum, je pense largement mieux maîtriser SQL qu'une majorité de développeurs, alors que je ne suis qu'un chef de projet, donc pas censé pisser du code toute la journée).
Depuis que je suis ici, je modifie systématiquement les requêtes sur lesquelles je travaille, afin d'utiliser la syntaxe normalisée, et résultat, tout est à chaque fois plus lent. Je passe pour un incompétent fini, le client n'a plus confiance en moi, et franchement, ça me gonfle.

Oracle, t'as vraiment de la chance que tu sois leader du marché, et que ta réputation suffise à vendre des licences. Parce que si c'était les développeurs qui choisissait, tout le monde se tournerais vers MySQL : au moins c'est de la merde, mais tout le monde le sait, et au moins, c'est de la merde qui va vite et qui évolue, pas une bouse infâme qui se complaît dans sa médiocrité (voilà c'est dit).

[pachot]

Bonjour,

La grosse erreur, à mon avis, c'est de comparer le temps d'exécution.
La même exécution peut avoir des temps différents: données en cache, changement du plan d'exécution, etc.

Pour voir ce qui est plus optimal il faut:
1. regarder le plan d'exécution
2. mesurer les statistiques basiques avec autotrace par exemple: consistent gets principalement.

Les règles que tu cherche n'existent pas, en tout cas pas de manière générale. Ca dépendra beaucoup du SGBD, de sa version, du contexte (volume de données), etc.

Sur les exemples que tu donnes,
- il n'y a aucune raison pour que l'ordre des tables dans le 'from' change quelque chose - sauf utilisation de hints particulers.
- il n'y a aucune raison, sauf bug éventuel d'une version, pour que la syntaxed de jointure ANSI soit différente de la syntaxe historique d'Oracle
- il n'y a aucune raison pour que IN et EXISTS de donne pas la même chose en terme de performance, du mois sur les versions pas trop anciennes.

Tout ça ne devrait pas changer le plan d'exécution choisi. Le SQL est un langage déclaratif: tu définit le résultat que tu veux, et pas comment aller chercher les données. C'est l'optimiseur qui fera ce travail. Et il va tout remettre dans l'ordre qu'il choisit.

Il y a quelques cas que l'optimiseur arrive mieux à traiter que d'autres. Par exemple il peut utiliser un index pour un LIKE 'xxx%' alors qu'il n'y pensera pas si tu fais SUBSTR(...,1,...) dans les version actuelles.

Depuis que je suis ici, je modifie systématiquement les requêtes sur lesquelles je travaille, afin d'utiliser la syntaxe normalisée, et résultat, tout est à chaque fois plus lent. Je passe pour un incompétent fini, le client n'a plus confiance en moi, et franchement, ça me gonfle.

Quelle version ? Quel OPTIMIZER_MODE ?

Si l'appli utilise l'OPTIMIZER_MODE=RULE (ce qui ne devrait plus être le cas depuis plusieurs années) alors l'ordre des tables peut avoir un impact. Et le IN/EXIST aussi. Ce que je disais plus haut concerne l'optimiseur statistique (CBO). Donc si ta base est en mode RULE, lorque tu utilise une syntaxe ANSI, alors il passera automatiquement en CBO. Parce que RULE est obsolete et toutes les fonctionnalités qui sont arrivées ensuite (comme la syntaxe ANSI) ne fonctionne pas avec. C'est peut-être une idée si ton problème est si général. En RULE ça marchait bien et en CBO ce n'est plus le cas parce que les statistiques sur les tables sont mauvaises.

Si c'est le cas, il faudra penser à abandonner RULE une fois que les statistiques sont calculées correctement. Et alors, meilleures performances et syntaxe ANSI, et support (parce que RULE plus supporté).

Mais la bonne démarche pour comprendre le problème: comparer les plans d'exécutions.

Si c'est le mêmes, alors c'est que les mesures du temps de réponse de sont pas bonne et ça n'a rien à voir avec tes modifications.

S'ils sont différents, alors il faut investiguer. N'hésite pas à les poster sur ce forum.

Oracle essaie de plus en plus d'optimiser lui même les requêtes. L'avantage: arriver à de bonnes performances sans trop se poser de questions. Inconvénient: grosse instabilité du résultat - donc impossible de juste regarder le temps de réponse d'une exécution. Il faut soit regarder les temps de réponse sur plusieurs exécutions réelles, soit regarder le plan d'exécution et les statistiques de base (blocs lus, volume manipulé).

Le mode 'RULE' dont je parlais était beaucoup plus stable, mais limité en terme de performance. Comme les SGBD que tu as l'air de préférer Mais Oracle a dû évoluer pour traiter des bases de plusieurs teraoctets, avec des milliers de transactions par seconde. Le CBO est mieux à condition que les statistiques des tables soient à jour.

Cordialement,
Franck.

[mnitu]

...
Depuis que je suis ici, je modifie systématiquement les requêtes sur lesquelles je travaille, afin d'utiliser la syntaxe normalisée, et résultat, tout est à chaque fois plus lent. Je passe pour un incompétent fini, le client n'a plus confiance en moi, et franchement, ça me gonfle.
...

Vous perdez votre temps et celui du client aussi. D’une manière évidente vous ne savez pas optimiser les requêtes pour Oracle et le pire est que vous ne voulez pas du tout apprendre une démarche correcte. Vous cherchez toujours la solution magique comme au temps du moyen âge on cherchait la pierre philosophale.

[pacmann]

Salut,

Du coup, là ça fait plusieurs mois que je suis en clientèle.
Je suis vendu par ma boîte comme CP sur un ERP, mais moi j'ai bien insisté sur ma bonne maîtrise du SQL (et même si je me sens souvent petit pandawa sur ce forum, je pense largement mieux maîtriser SQL qu'une majorité de développeurs, alors que je ne suis qu'un chef de projet, donc pas censé pisser du code toute la journée).
Depuis que je suis ici, je modifie systématiquement les requêtes sur lesquelles je travaille, afin d'utiliser la syntaxe normalisée, et résultat, tout est à chaque fois plus lent. Je passe pour un incompétent fini, le client n'a plus confiance en moi, et franchement, ça me gonfle.

Oracle, t'as vraiment de la chance que tu sois leader du marché, et que ta réputation suffise à vendre des licences. Parce que si c'était les développeurs qui choisissait, tout le monde se tournerais vers MySQL : au moins c'est de la merde, mais tout le monde le sait, et au moins, c'est de la merde qui va vite et qui évolue, pas une bouse infâme qui se complaît dans sa médiocrité (voilà c'est dit).

Sincèrement, si j'ai bien compris ta situation, c'est ton arrogance qui te mène à ta perte.
Tu connais un peu / bien le SQL, et du coup tu prends l'initiative de t'imposer en tant que référent, et visiblement tu oublies de te dégager de la responsabilité sur les performances.

Coder, optimiser, c'est un vrai boulot.

On me demande régulièrement de l'assistance sur du SQL alors que je ne suis que MOA. Bien que comme toi je connaisse un peu / bien le SQL et Oracle, jamais il ne me viendrais à l'idée de promettre quoi que ce soit sur ce domaine où je ne suis pas expert (qui n'est plus mon métier depuis longtemps).

Ma réponse standard : "T'es tu mis en contact avec le DBA ? Pour ma part, je peux regarder un peu pour voir si quelque chose me saute aux yeux... mais je ne peux rien te garantir"

Parce que j'ai peur ? Non... juste parce que j'ai conscience que c'est un vrai métier, et que ça ne s'improvise pas.

PS : Lola, ne prends pas le tuto de SQLPro au sérieux, en tous cas pas pour Oracle. Il n'y a que des règles magiques de transformation, alors que comme on le voit tous les jours, la réalité de l'optimiseur est tout sauf du tour de passe-passe