Discussion :

[fred_04510]

Bonjour,
J'ai la requête suivante de type :

Code :

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

1
2
3
4
select * 
from TABLE 
where date >= '01/01/2008' 
and date <'01/01/2009'

1) Le champs date est de type datetime.
2) Cette requête requete renvoie 500 000 lignes sur 1 000 000.
3) Il existe un unique clustered index TABLE_PK sur cette table (sur un autre champs que date).
4) Il existe un index IX_TABLE_date non clustered sur le champs date.
5) Les statistiques sont à jour.

Le plan d'exécution de la requête révèle que optimiseur a fait un clustered Index scan sur l'index TABLE_PK. Pourquoi n'utilise t'il pas l'index IX_TABLE_date ?

Merci d'avance pour votre aide.

[elsuket]

Bonjour,

2 raisons à cela :

- parce que l'optimiseur a estimé qu'il lui coûterait moins cher de parcourir toute la table (vous retournez un nombre important de lignes par rapport au nombre total de lignes de la table)

- parce que vous avez mis SELECT *, donc aucun index ne couvre la requête, donc l'optimiseur choisit de parcourir la table.

N'utilisez jamais * dans une requête de production, sauf pour le prédicat EXISTS, ou la fonction d'aggrégat COUNT, qui sont optimisées pour

@++

[fred_04510]

Merci, j'ai appris un truc.
C'est spécifique à sqlserver ?

[elsuket]

Vous voulez dire pour "*" ? Non, je ne pense pas

@++

[fred_04510]

Pourriez vous me donner une source officielle indiquant qu'il ne faut pas utiliser select * ?
Je n'arrive pas à trouver ça sur le net.

Merci encore pour votre aide.

[elsuket]

Malheureusement je ne peux pas, mais à votre avis que fait le moteur de base de données quand vous spécifiez SELECT * ?

Il va chercher dans les tables système le nom des colonnes de la table pour bâtir ensuite le plan de requête optimal.
Vous créez donc ainsi un peu plus de surcharge au serveur ...
Je vous laisse imaginer ce que cela fait quand vous spécifiez des jointures ...

@++

[fred_04510]

J'ai constaté une nette amélioration des performances en spécifiant les colonnes (le bon plan d'exécution est désormais utilisé!).
J'essaie de comprendre précisemment en quoi le select * peut perturber le plan d'exécution.

Je comprends bien que quand on met *, l'étape de parse de la requête prend plus de temps (plus de colonnes), mais quelle est l'explication concernant le bouleversement des plans d'exécution?

[elsuket]

le bon plan d'exécution est désormais utilisé!

Vous voulez dire le bon index ?

quelle est l'explication concernant le bouleversement des plans d'exécution?

Supposez qu'entre deux exécutions j'ajoute ou je supprime une colonne de votre table : l'optimiseur n'a aucun moyen de le savoir.
Or l'optimiseur se base sur les statistiques des colonnes pour calculer le plan le moins coûteux en ressources, mais avec "*" il ne peut pas le faire, puisqu'il ne sait pas quelles colonnes constituent la table, donc il ne sait pas non plus quelle est la sélectivité de celles-ci ...

Autre conséquence du SELECT * : au lieu de lire seulement les données dont vous avez besoin, vous retournez un ensemble de résultats ayant la même structure que votre table, et il faut faire passer cet ensemble à travers le réseau, donc vous faites transiter des données pour rien ...
Ensuite vous poussez tout autant de données dans le cache, donc les plans de requête y restent moins longtemps, donc vous dégradez les performances d'autres requêtes ...

Tout ça pour avoir eu la flemme de spécifier vos colonnes en les préfixant par le nom de la table ou l'alias de la table auxquelles elles appartiennent, avouez que c'est dommage

@++

[fred_04510]

Clustered index scan de l'index unique avant correction contre Index seek de l'index sur le champs date après correction (bizarre d'ailleurs quand la requête retourne a moitié de la table), ça n'est pas seulement un index différent, mais aussi un balayage de l'index différent.

Vous voulez dire le bon index ?

Il faut que j'aille me renseigner sur le fonctionnement de l'optimiseur.
En effet je ne savais pas qu'il avait besoin des colonnes qui suivent le select pour connaitre les colonnes de la table. Je pensais qu'il connaissait les colonnes de la table car le nom de la table est fournit dans l'ordre sql.
Il y a aussi un truc mysterieux que j'ai remarqué :
select col1 from TABLE where col1 = ... : donne plan d'execution 1
select col1,col2 from TABLE where col1 ... : donne plan d'execution 2
????

Supposez qu'entre deux exécutions j'ajoute ou je supprime une colonne de votre table : l'optimiseur n'a aucun moyen de le savoir.
Or l'optimiseur se base sur les statistiques des colonnes pour calculer le plan le moins coûteux en ressources, mais avec "*" il ne peut pas le faire, puisqu'il ne sait pas quelles colonnes constituent la table, donc il ne sait pas non plus quelle est la sélectivité de celles-ci ...

@++

[elsuket]

Il y a aussi un truc mysterieux que j'ai remarqué :
select col1 from TABLE where col1 = ... : donne plan d'execution 1
select col1,col2 from TABLE where col1 ... : donne plan d'execution 2

Vous comprenez que, suivant les colonnes de votre SELECT, et suivant les index qui sont posés sur la table, le moteur de base de données va choisir celui qui couvre la requête, et s'il n'en existe pas, alors il choisira de parcourir la table.
Dans tous les cas il choisira l'index qui lui assure la sélectivité la plus élevée.

Je ne comprends pas bien ce qui vous chiffonne ...

@++

[fred_04510]

Ca veut dire que si j'ai un critère de sélection sur col1 mais que ma requête ramène 10 colonnes (dont col1) alors je vais devoir créer un index sur 10 colonnes ?
De la même manière, si je crée un index i(col1) sur une table t(col1,col2) ça veut dire que l'index contient uniquement les informations de col1?

Je pensais qu'un index contenait toutes les lignes d'une table avec toutes ses colonnes rangées selon un critère (la col1 ci dessus par exemple).

Merci encore pour votre aide

Vous comprenez que, suivant les colonnes de votre SELECT, et suivant les index qui sont posés sur la table, le moteur de base de données va choisir celui qui couvre la requête, et s'il n'en existe pas, alors il choisira de parcourir la table.
Dans tous les cas il choisira l'index qui lui assure la sélectivité la plus élevée.

Je ne comprends pas bien ce qui vous chiffonne ...

@++

[elsuket]

Je pensais qu'un index contenait toutes les lignes d'une table avec toutes ses colonnes rangées selon un critère

Non, sinon cela ne servirait à rien d'indexer

- Un index cluster contient les données de la table ordonnées dans l'ordre physique des colonnes sur lesquelles il est posé. En conséquence, il ne peut y avoir qu'un seul index cluster par table.

- Un index non-cluster n'est pas ordonné physiquement suivant l'ordre physique de sa clé, et contient un pointeur vers les pages de données de la table.
C'est pourquoi il est toujours préférable de créer un index cluster avant de réer un index non-cluster.

Par analogie avec un livre :

- un index cluster est l'index que vous avez en début de livre : il montre les chapitres et les parties dans l'ordre physique du livre, c'est à dire l'ordre des pages,

- un index non-cluster est à la fin du livre : si vous cherchez une partie en particulier, vous allez chercher sur son nom pour obtenir la page, mais vous réussissez à trouver le mot rapidement parce qu'ils sont classés par ordre alphabétique, ce qui n'est pas l'ordre des pages du livre.

si je crée un index i(col1) sur une table t(col1,col2) ça veut dire que l'index contient uniquement les informations de col1?

Vous avez maintenant la réponse

@++

[fred_04510]

Encore merci pour votre aide

Non, sinon cela ne servirait à rien d'indexer

- Un index cluster contient les données de la table ordonnées dans l'ordre physique des colonnes sur lesquelles il est posé. En conséquence, il ne peut y avoir qu'un seul index cluster par table.

- Un index non-cluster n'est pas ordonné physiquement suivant l'ordre physique de sa clé, et contient un pointeur vers les pages de données de la table.
C'est pourquoi il est toujours préférable de créer un index cluster avant de réer un index non-cluster.

Par analogie avec un livre :

- un index cluster est l'index que vous avez en début de livre : il montre les chapitres et les parties dans l'ordre physique du livre, c'est à dire l'ordre des pages,

- un index non-cluster est à la fin du livre : si vous cherchez une partie en particulier, vous allez chercher sur son nom pour obtenir la page, mais vous réussissez à trouver le mot rapidement parce qu'ils sont classés par ordre alphabétique, ce qui n'est pas l'ordre des pages du livre.


Vous avez maintenant la réponse

@++