Rendre un index unique pour l'amélioration des perf ou pas ?

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:

---

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

Bonjour,

Quelqu'un a une idée ?

Merci

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 +

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

Citation:

---

Envoyé par SQLpro

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:

---

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.

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

Citation:

---

Envoyé par StringBuilder

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:

---

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:

---

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:

---

CREATE INDEX X ON personne (sexe);

---

Code:

---

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 +

Citation:

---

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;)