L'ordre des colonnes dans une clause WHERE a-t-il une importance ?

Question

L'ordre des colonnes dans une clause WHERE a-t-il une incidence sur les performances ?

par exemple

Supposons que je mette en premier une colonne qui a un plus grand potentiel d'unicité ou vice versa ?

Answer 1

Avec un bon optimiseur de requêtes, cela ne devrait pas être le cas.

Mais en pratique, je pense que ça pourrait être le cas.

Vous ne pouvez le savoir pour vos cas qu'en mesurant. Et les mesures changeront probablement au fur et à mesure que la distribution des données change dans la base de données.

Answer 2

Pour Transact-SQL, il y a une préséance définie pour les opérateurs dans le fichier condition de la clause WHERE. L'optimiseur peut réordonner cette évaluation, il ne faut donc pas compter sur le comportement de court-circuitage pour garantir l'exactitude. L'ordre est généralement de gauche à droite, mais la sélectivité/disponibilité des index a probablement aussi son importance. La simplification de votre condition de recherche devrait améliorer la capacité de l'optimiseur à la traiter.

Ex :

 WHERE (a OR b) AND (b OR c)

pourrait être simplifié en

 WHERE b OR (a AND c)

Il est clair que dans ce cas, si la requête peut être construite de manière à trouver d'abord si b est valable, elle peut être en mesure de sauter l'évaluation de a et c et donc de s'exécuter plus rapidement. Je ne peux pas répondre à la question de savoir si l'optimiseur peut effectuer cette transformation simple (il pourrait le faire), mais le fait est qu'il ne peut probablement pas effectuer des transformations arbitrairement complexes et que vous pourriez être en mesure d'affecter les performances de la requête en réorganisant votre condition. Si b est plus sélectif ou possède un index, l'optimiseur sera probablement capable de construire une requête en l'utilisant en premier.

EDIT : En ce qui concerne votre question sur le classement basé sur l'unicité, je supposerais que toutes les indications que vous pouvez fournir à l'optimiseur en fonction de votre connaissance (réelle, pas supposée) des données ne pourraient pas faire de mal. Prétendez qu'il ne fera aucune optimisation et construisez votre requête comme si vous deviez la définir du plus au moins sélectif, mais n'en faites pas une obsession jusqu'à ce que les performances posent réellement problème.

Citation de la référence ci-dessus :

L'ordre de préséance des opérateurs logiques est NOT (le plus élevé), suivi de ET, puis de OU. Les parenthèses peuvent être utilisées pour remplacer cette préséance dans une condition de recherche. L'ordre d'évaluation des opérateurs logiques opérateurs logiques peut varier en fonction des choix effectués par l'optimiseur de requêtes. optimiseur de requêtes.

Answer 3

Pour SQL Server 2000 / 20005 / 2008, l'optimiseur de requêtes donne généralement des résultats identiques, quelle que soit la façon dont vous disposez les colonnes dans la clause WHERE. Cela dit, au fil des années d'écriture de milliers de commandes T-SQL, j'ai trouvé quelques cas particuliers où l'ordre modifiait les performances. Voici quelques caractéristiques des requêtes qui semblaient être sujettes à ce problème :

1. Si vous avez un grand nombre de tables dans votre requête (10 ou plus).

2. Si vous avez plusieurs instructions EXISTS, IN, NOT EXISTS, ou NOT IN dans votre clause WHERE

3. Si vous utilisez des expressions CTE (common-table expressions) imbriquées ou un grand nombre d'expressions CTE.

4. Si vous avez un grand nombre de sous-requêtes dans votre clause FROM.

Voici quelques conseils pour tenter d'évaluer la meilleure façon de résoudre rapidement le problème de performance :

1. Si le problème est lié à 1 ou 2, essayez de réordonner la clause WHERE et comparez le coût des sous-arbres des requêtes dans les plans de requête estimés.

2. Si le problème est lié à 3 ou 4, essayez de déplacer les sous-requêtes et les CTE hors de la requête et de leur faire charger des tables temporaires. L'optimiseur de plan de requête est BEAUCOUP plus efficace pour estimer les plans de requête si vous réduisez le nombre de jointures et de sous-requêtes complexes dans le corps de l'instruction T-SQL.

3. Si vous utilisez des tables temporaires, assurez-vous que vous avez spécifié des clés primaires pour les tables temporaires. Cela signifie qu'il faut éviter d'utiliser SELECT INTO FROM pour générer la table. Au lieu de cela, créez explicitement la table et spécifiez une clé primaire avant d'utiliser une instruction INSERT INTO SELECT.

4. Si vous utilisez des tables temporaires et que de nombreux processus sur le serveur utilisent également des tables temporaires, il est préférable de créer une table d'attente plus permanente qui est tronquée et rechargée pendant le processus de requête. Vous êtes plus susceptible de rencontrer des problèmes de contention de disque si vous utilisez la base de données temporaire pour stocker vos tables de travail/stage.

5. Déplacez les déclarations de la clause WHERE qui filtreront le plus de données au début de la clause WHERE. Notez que si vous résolvez le problème de cette manière, vous obtiendrez probablement de mauvaises performances lorsque le plan de requête ne saura plus comment générer et choisir le meilleur plan d'exécution. Il est préférable de trouver un moyen de réduire la complexité de la requête afin que l'ordre de la clause WHERE ne soit plus pertinent.

J'espère que ces informations vous seront utiles. Bonne chance !

Answer 4

Tout dépend du SGBD, de l'optimiseur de requêtes et des règles, mais en général, cela affecte les performances.

Si une clause where est ordonnée de telle sorte que la première condition réduit considérablement l'ensemble de résultats, les autres conditions ne devront être évaluées que pour un ensemble plus petit. En suivant cette logique, vous pouvez optimiser une requête en fonction de l'ordre des conditions dans une clause where.

Answer 5

En théorie deux requêtes équivalentes doivent produire des plans de requête identiques. Comme l'ordre des WHERE n'a aucun effet sur la signification logique de la requête, cela devrait signifier que l'ordre des clauses WHERE ne devrait avoir aucun effet.

Cela est dû à la façon dont l'optimiseur de requêtes fonctionne. Dans un grandement simplifié vue d'ensemble :

1. Tout d'abord, SQL Server analyse la requête et construit un arbre d'opérateurs logiques (par ex. JOIN o SELECT ).
2. Il traduit ensuite ces opérateurs logiques en un "arbre d'opérations physiques" (par exemple, des "boucles imbriquées" ou un "balayage d'index", c'est-à-dire un plan d'exécution).
3. Ensuite, il permute dans l'ensemble des "arbres d'opérations physiques" équivalents (c'est-à-dire les plans d'exécution) en échangeant les opérations équivalentes, en estimant le coût de chaque plan jusqu'à ce qu'il trouve le plan optimal.

La deuxième étape est réalisée de manière complètement naïve - elle choisit simplement le premier arbre physique / le plus évident qu'elle peut, cependant dans la troisième étape l'optimiseur de requête est capable de regarder à travers todo arbres physiques équivalents (c'est-à-dire des plans d'exécution), et donc tant que les requêtes sont réellement équivalentes, peu importe le plan initial que nous obtenons à l'étape 2, l'ensemble des plans à considérer à l'étape 3 est le même.

(Je ne me souviens pas des noms réels des arbres logiques/physiques, ils sont dans un livre mais malheureusement le livre est à l'autre bout du monde pour le moment).

Pour plus de détails, voir la série d'articles de blog suivants Dans l'Optimiseur : Construire un plan - Partie 1

En réalité cependant, l'optimiseur de requêtes n'a souvent pas la possibilité de prendre en compte todo (pour les requêtes complexes, il peut y avoir un très grand nombre de plans possibles), et donc, après un certain temps, l'étape 3 est interrompue et l'optimiseur de requêtes doit choisir le meilleur plan qu'il a trouvé jusqu'à présent - dans ce cas, il ne s'agit pas d'un plan d'exécution. todo seront pris en considération.

Il y a beaucoup de magie derrière la scène qui se passe pour s'assurer que l'optimiseur de requêtes choisit sélectivement et intelligemment les plans à prendre en compte, et donc la plupart du temps le plan choisi est "suffisamment bon" - même si ce n'est pas le plan le plus rapide dans l'absolu, il n'est probablement pas beaucoup plus lent que le plus rapide théorique,

Cela signifie toutefois que si nous avons un plan de départ différent à l'étape 2 (ce qui peut arriver si nous écrivons notre requête différemment), cela signifie potentiellement qu'un sous-ensemble différent de plans est considéré à l'étape 3, et donc que en théorie Le serveur SQL peut proposer des plans de requête différents pour des requêtes équivalentes, en fonction de la manière dont elles ont été écrites.

En réalité, dans 99 % des cas, vous ne remarquerez pas la différence (pour de nombreux plans simples, il n'y a pas de différence entre les deux). être aucune différence puisque l'optimiseur prendra en compte tous les plans). De plus, il est impossible de prévoir comment tout cela va fonctionner, et donc des choses qui pourraient sembler judicieuses (comme mettre la balise WHERE dans un certain ordre), pourrait ne pas avoir l'effet escompté.