Discussion :

[Hammeron]

Bonjour à tous,

Je me posais une question bête sur l'ajout d'un index pour accélérer les perfs de requêtes de jointure.

Je suis sous SQL Server 2008 R2.

J'ai une table CONTACT avec un champ [ID], IDENTITY, PRIMARY KEY donc indexé en cluster.

J'ai ensuite une table CONTRAT avec également un champ [ID] clusterisé.

Chaque CONTRAT concerne un CONTACT.
Dans la table CONTRAT il y a donc un champ ID_CONTACT qui fait référence à un ID de la table CONTACT.
En revanche, un contact peut avoir plusieurs contrats, donc il peut y avoir plusieurs fois le même ID_CONTACT dans la table CONTRAT.
Je précise que le champ ID_CONTACT n'a aucune contrainte SQL : il n'a ni contrainte SQL, ni relation SQL avec le champ CONTACT.ID.

Ce modèle de données simple est exploité par un logiciel qui sera susceptible de rechercher des CONTACT en fonction des caractéristiques de ses contrats.

La requête de base qui sera envoyée sera du type :

Code :

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

1
2
SELECT ID FROM CONTACT
WHERE EXISTS (SELECT 1 FROM CONTRAT (NOLOCK) WHERE  CONTRAT.NUM_IDENTIFIANT_OGC=CONTACT.ID AND (<clause where contrat>))

Je cherche à optimiser la jointure CONTRAT.NUM_IDENTIFIANT_OGC=CONTACT.ID du WHERE EXISTSpar l'ajout d'un index sur CONTRAT.NUM_IDENTIFIANT_OGC.

Etant donné que le champ NUM_IDENTIFIANT_OGC n'est pas unique, est-il plus performant d'ajouter un index non unique sur ce champ ou un index unique composé de NUM_IDENTIFIANT_OGC et du champ ID (ce dernier étant unique donc la combinaison des deux forcément aussi) ?

D'avance merci

[Hammeron]

Bonjour,

Quelqu'un a une idée ?

Merci

[SQLpro]

1) Déjà la présence du NOLOCK est une hérésie et ne sera pas supportée dans les versions futures (je suppose que vous êtes en 2000 et cela ne passe pas en 2005 et suivant !

2) un index ne se pose pas à la légère et dépends des statistiques de données.
Si vous avez besoin de poser un index unique, alors c'est généralement stupide... car cela signifie que vous n'avez pas modélisé correctement votre base ! En effet, cet index unique aurait dû être créé sous forme de contrainte d'unicité lors de la phase de conception de votre base au niveau du modèle conceptuel. En l'occurrence maintenant il ne vous reste plus qu'à tester vos différentes hypothèses en regardant la consommation des ressources de chaque cas et en prenant le bon au final.
Pour cela vous pouvez "voir" la consommation des ressources avec les options :
SET STATISTICS IO ON
et
SET STATISTICS TIME ON


A +

[StringBuilder]

Bonjour SQLPro, je détourne un peu le topic initial, pour me focaliser sur cette partie de votre réponse :

Si vous avez besoin de poser un index unique, alors c'est généralement stupide... car cela signifie que vous n'avez pas modélisé correctement votre base ! En effet, cet index unique aurait dû être créé sous forme de contrainte d'unicité lors de la phase de conception de votre base au niveau du modèle conceptuel.

J'ai par exemple le modèle suivant :

sexe
id
nom

personne
id
nom
prenom
sexe (fk sexe.id)

voiture
id
proprietaire (fk personne.id)
immatriculation
nbportes
etc.

Je cherche toutes personnes de sexe "féminin" propriétaires de voitures 5 portes.

Code sql :

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

1
2
3
4
5
6
7
 
select p.nom, p.prenom
from personne p
inner join voiture v on v.proprietaire = p.id
inner join sexe s on s.id = p.sexe
where v.nbportes = 3
and s.nom = "féminin";

Donc la table "personne" sera filtrée à la fois par sont id (unique) et le sexe (non unique).

Seulement, je peux avoir des millions de propriétaires de voitures 5 portes.
Et je peux être amené à me dire que filtrer une personne sur deux en retenant son sexe pourrait être intéressant.

A ce moment, je suis tenté de créer l'index suivant :

personne (id, sexe) ou alors personne (sexe, id)

A ce moment, moi je sais que l'index ne pourra contenir que des données absolument uniques, puisqu'il y a la clé primaire dedans.

Mais je sais aussi qu'il sera absolument impossible de créer des doublons (puisqu'il y a la PK dedans).

A ce moment, est-il plus performant de créer l'index comme "unique" ou non ?

En effet, l'unicité de l'index va permettre des accès en lecture certainement plus performant, mais en revanche, je vais ralentir toutes les opération de mise à jour dans la table, afin de vérifier l'unicité de l'index.

PS : Bon, mon exemple n'est peut-être pas le plus pertinent. Mais la question reste entière.

[iberserk]

Vous ne précisez pas les index CLUSTER sur vos différentes tables...

[SQLpro]

Bonjour SQLPro, je détourne un peu le topic initial, pour me focaliser sur cette partie de votre réponse :
[...]

A ce moment, je suis tenté de créer l'index suivant :

personne (id, sexe) ou alors personne (sexe, id)

.

la remarque de Ibersek est très importante car si votre clef est CLUSTERED alors l'index sur sexe seul suffit, car par nature cet index comportera l'id.

Pour vous en convaincre :

Code :

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

1
2
3
4
5
CREATE TABLE personne
(id int CONSTRAINT PK PRIMARY KEY CLUSTERED,
nom VARCHAR(32),
prenom VARCHAR(32),
sexe CHAR(1))

Code :

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

1
2
3
INSERT INTO personne VALUES (1, 'toto', 'marcel', 'F');
INSERT INTO personne VALUES (2, 'titi', 'georges', 'F');
INSERT INTO personne VALUES (3, 'tata', 'cunéconde', 'M');

Code :

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

CREATE INDEX X ON personne (sexe);

Code :

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

1
2
3
4
5
6
7
8
DBCC SHOW_STATISTICS ('personne', 'X');
 
[...]
 
All density   Average Length Columns
------------- -------------- --------
0,5           1              sexe
0,3333333     5              sexe, id

A +

[iberserk]

la remarque de Ibersek est très importante car si votre clef est CLUSTERED alors l'index sur sexe seul suffit, car par nature cet index comportera l'id.

C'est exactement ou je voulais en venir