Discussion :

[VinceSSJ]

Bonjour,


je dois placer des indexs, seulement je ne sais pas comment m'y prendre.
Par exemple, j'ai deux requêtes :
Une qui va chercher dans la colonne DateInit
et une autre qui va chercher dans les colonnes DateInit et DateDeb.

C'est un exemple parmi tant d'autres, mais ma question est : dans ce cas, pour une optimisation maximale, faut-il créer :

-un index sur DateInit et un index sur DateDeb ?
-un index sur DateInit et un index sur DateInit+DateDeb ?
-un seul index sur DateInit+DateDeb ?

Merci d'avance !

[dehorter olivier]

petit tutorial à lire avant de se précipiter sur le forum

[punkoff]

Bonjour,

Pour votre exemple précis le mieux serait la solution 3.
Après si d'autres requêtes utilisent ces colonnes une autre approche pourra être effectuée.

[VinceSSJ]

Merci pour lien que j'avais déjà lu et c'est pour ça que je viens ici

Voilà l'exemple d'une requête qui pioche dans DateDeb :

Code :

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

Select t.idTache, t.tid, t.labo, t.etude, t.dateDeb, t.duree, t.libelle, t.dateInit, t.dureeInit, t.annotation, t.technicien, t.libelleInit, t.derniereModif, t.modifPar From Tache t Left Outer Join Periode p On (t.idPeriode = p.idPeriode) Where t.dateDeb >= ? And t.dateDeb < ? And p.niveau = ?

et une autre qui pioche dans les 2 :

Code :

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

Select t.idTache, t.tid, t.labo, t.etude, t.dateDeb, t.duree, t.libelle, t.dateInit, t.dureeInit, t.annotation, t.technicien, t.libelleInit, t.derniereModif, t.modifPar From Tache t Left Outer Join Periode p On (t.idPeriode = p.idPeriode) Where ((t.dateInit is not null and t.dateInit >= ? And t.dateInit < ?) or (t.dateInit is null and t.dateDeb >= ? And t.dateDeb < ?)) And p.niveau = ?

[dehorter olivier]

Au vue de ta requête 2, je dirais un index pour chaque colonne date, sans oublier un pour "niveau"

[VinceSSJ]

Mais du coup si vous me dites 50/50 je sais plus où donner de la tête

Mais si je fais un seul index pour les deux dates, est ce que par exemple pour ma première requête qui n'utilise que DateDeb l'index servira à quelque chose ?

[SQLpro]

Un index se place toujours entre le pouce et le majeur !

A +

[VinceSSJ]

C'est pas faux

[Rei Ichido]

Mais si je fais un seul index pour les deux dates, est ce que par exemple pour ma première requête qui n'utilise que DateDeb l'index servira à quelque chose ?

Tout dépend de comment est placé ton index. S'il est placé sur (DateDeb, DateFin), alors oui on peut se servir de cet index pour faire une recherche sur DateDeb. Sinon, alors ça dépend du nombre de valeurs que peut prendre DateFin.

D'une manière générale, il suffit de se représenter l'index pour savoir si on peut s'en servir. Prenons un annuaire, qui est un index (Ville, Nom, Prenom) sur une partition (Département).
Est-ce qu'il est pratique pour trouver toutes les personnes d'une même ville ? Oui.
Est-ce qu'il est pratique pour trouver toutes les personnes d'un même nom sur le département ? Oui, parce que sinon je n'aurais pas d'autre choix que de parcourir toute la table. Mais il va quand même falloir que j'aille accéder à toutes les villes, qui sont assez nombreuses, c'est pas très pratique.
Est-ce qu'il est pratique pour trouver toutes les personnes d'un même prénom ? Définitivement non, je vais de toute façon devoir scanner toute la table.

[VinceSSJ]

MMmmmmhhhh.. Je comprends bien tes exemples, mais du coup j'ai du mal à les appliquer à mon exemple


Pour la première requête, de toute façon, si la recherche s'effectue seulement sur DateDeb et que l'index est placé sur (DateBed, DateInit) , le DateInit ne sera pas pris en compte, si ?

Sinon, aucune requête n'a de clause WHERE uniquement sur DateInit.
C'est soit DateDeb+DateInit, soit seulement DateDeb

[tatayo]

Bonjour,

Pour la première requête, de toute façon, si la recherche s'effectue seulement sur DateDeb et que l'index est placé sur (DateBed, DateInit) , le DateInit ne sera pas pris en compte, si ?

Si ton index porte sur (DateBed, DateInit) dans cet ordre, il sera utilisé pour toute recherche sur DateDeb.

Sinon, aucune requête n'a de clause WHERE uniquement sur DateInit.
C'est soit DateDeb+DateInit, soit seulement DateDeb

A mon avis tu peux créer un index sur (DateBed, DateInit). Ainsi il sera utilisé dans les deux cas, et accélèrera encore plus les recherches sur les deux dates.

Tatayo.

[SQLpro]

Pour poser des index il faut connaître plusieurs choses :
1) les requêtes concernées. Vous les avez indiquées, c'est bien
2) les index déjà existants (et notamment les contraintes PK et UIQUE).
3) le SGBDR et sa version et effet, si Oracle ou SQL Server disposent d'un optimiseur haut de gamme capable d'utiliser très souvent les index à bon escient, ce n'est pas le cas de postGreSQL qui est souvent pris en faute et encore moins de MySQL...)
4) il est parfois nécessaire de récrire la requête si votre SGBDR ne le fait pas, afin de rendre les prédicats de recherches "sargable". Lisez l'article que j'ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p1...ql-sargable-c/

Enfin, dans la 4e édition de mon ouvrage sur SQL, vous aurez le bonheur de constater qu'un chapitre nouveau d'environ 40 pages est consacré à ce sujet...
Cette nouvelle édition doit paraître à la fin du mois...
http://www.amazon.fr/SQL-%C3%A9ditio...9582752&sr=1-2

A +

[dehorter olivier]

et hop

je viens de le commander

[VinceSSJ]

Merci pour toutes ses précisions et ces liens !

Le SGBD est SQL Server 2005 (et 2000 pour les autres serveurs).
Le seul index créé sur la Table Tache était celui sur les pk (idTache+idPeriode).
Sinon, pour la réécriture de la requête, je ne me lance pas là-dedans, l'application n'était pas de moi et ça serait risquer de toucher à tout ça

[CinePhil]

Remarque quand même sur les requêtes : Elles font des jointures externes qui sont en fait des jointures internes parce qu'il y a une condition du WHERE qui porte sur la table externe.

Voir mon blog pour plus d'explications.

[VinceSSJ]

Et est ce qu'une jointure externe coute plus en "ressources" qu'un jointure interne ?

[CinePhil]

Une jointure interne ne ramène que les lignes satisfaisant à la condition de jointure.

Code :

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

1
2
FROM A
INNER JOIN B ON condition

=> Seules les lignes de A et B satisfaisant la condition seront ramenées.

Une jointure externe ramène toutes les lignes de la table interne.

Code :

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

1
2
FROM A
LEFT OUTER JOIN B ON condition

=> Toutes les lignes de A seront ramenées et seulement celles de B satisfaisant la condition.

Ce serait à vérifier mais je pense qu'il est possible que l'index sur la table A ne soit pas systématiquement utilisé dans la jointure externe alors qu'il a toutes les chances d'être utilisé pour rechercher les lignes à lire dans la jointure interne.

Il faudrait aussi tester si le comportement change avec une jointure externe + une condition de restriction dans le WHERE par rapport au transfert de la condition de restriction dans la jointure (dans le ON).

Si des experts ont étudié la question, qu'ils se manifestent.

[SQLpro]

Il faudrait aussi tester si le comportement change avec une jointure externe + une condition de restriction dans le WHERE par rapport au transfert de la condition de restriction dans la jointure (dans le ON).

Bien sûr !!! pas du tout le même sens... Démonstration :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE CLIENT (NOM VARCHAR(8))
 
INSERT INTO CLIENT 
VALUES ('DUPONT'),
       ('MARTIN'), 
       ('MULLER');
 
CREATE TABLE ADRESSE (NOM VARCHAR(8), VILLE VARCHAR(16))
 
INSERT INTO ADRESSE 
VALUES ('DUPONT', 'LYON'), 
       ('MARTIN', 'PARIS');

Requête 1 :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM   CLIENT AS C
       INNER JOIN ADRESSE AS A
             ON C.NOM = A.NOM;
 
NOM      NOM      VILLE
-------- -------- ----------------
DUPONT   DUPONT   LYON
MARTIN   MARTIN   PARIS
--> 2 lignes

Requête 2 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT *
FROM   CLIENT AS C
       LEFT OUTER JOIN ADRESSE AS A
             ON C.NOM = A.NOM;
 
NOM      NOM      VILLE
-------- -------- ----------------
DUPONT   DUPONT   LYON
MARTIN   MARTIN   PARIS
MULLER   NULL     NULL
--> 3 lignes

Requête 3 :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM   CLIENT AS C
       INNER JOIN ADRESSE AS A
             ON C.NOM = A.NOM
WHERE  A.VILLE = 'LYON';
 
NOM      NOM      VILLE
-------- -------- ----------------
DUPONT   DUPONT   LYON 
--> une ligne

Requête 4 :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM   CLIENT AS C
       LEFT OUTER JOIN ADRESSE AS A
             ON C.NOM = A.NOM
WHERE  A.VILLE = 'LYON';
 
NOM      NOM      VILLE
-------- -------- ----------------
DUPONT   DUPONT   LYON
--> une ligne

Requête 5 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT *
FROM   CLIENT AS C
       LEFT OUTER JOIN ADRESSE AS A
             ON C.NOM = A.NOM
WHERE  A.VILLE = 'LYON' OR A.VILLE IS NULL;
 
NOM      NOM      VILLE
-------- -------- ----------------
DUPONT   DUPONT   LYON
MULLER   NULL     NULL
--> 2 lignes

Requête 6 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT *
FROM   CLIENT AS C
       LEFT OUTER JOIN ADRESSE AS A
             ON C.NOM = A.NOM 
                AND A.VILLE = 'LYON';
 
--> 3 lignes !!!
NOM      NOM      VILLE
-------- -------- ----------------
DUPONT   DUPONT   LYON
MARTIN   NULL     NULL
MULLER   NULL     NULL

Quelle différence entre requête 2 et 6 ?

La première répond à la question : donnez moi tous les clients avec leurs éventuelles adresses.
La seconde répond à la question : donnez moi tous les clients mais ne me montrez que les adresses de LYON !.

A +

[CinePhil]

Ta démonstration correspond à l'article que j'ai écrit.

Mais ce n'est pas de ce comportement là dont je demandais un avis d'expert, c'était sur celui de l'utilisation des index.

Par exemple, si j'ajoute à la table CLIENT une colonne "COMMERCIAL" qui serait en fait une clé étrangère référençant le commercial qui suit le client et si je demande :
"Sélectionne les clients suivis par BROUARD et avec leur adresse si tu la connais, Ô SGBD talentueux !"

Requête 1 :

Code :

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

1
2
3
4
SELECT C.NOM, A.VILLE
FROM CLIENT C
LEFT OUTER JOIN ADRESSE A ON A.NOM = C.NOM
WHERE C.COMMERCIAL = 'BROUARD'

Requête 2 :

Code :

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

1
2
3
4
5
SELECT C.NOM, A.VILLE
FROM CLIENT C
LEFT OUTER JOIN ADRESSE A 
	ON A.NOM = C.NOM
	AND C.COMMERCIAL = 'BROUARD'

Sur le plan de la logique de la requête, je préfère la première mais pourrait-il y avoir une différence entre les deux sur le plan de l'utilisation des index ?

[SQLpro]

Aucune, car cela dépend essentiellement des cardinalités estimées par les stats.

A +