Discussion :

[mactwist69]

Bonjour bonjour,

Voila, dans un souci de performance, je me suis intéressé a la création d'index. Voici mes tables:

Table: Item
Colonnes: ID_item, Code_bar1

Table: Alias
Colonnes: ID_alias, Item_id, Code_bar2
(Item_id étant la clé étrangère correspondant a ID_item)

Chaque "produit" possède nécessairement un Code_bar1, mais pas nécessairement un Code_bar2.
Lorsqu'un utilisateur scanne un code bar, je ne sais pas si il s'agit du code bar 1 ou 2, mais sachant que dans 75% des cas il s'agit du code bar1.

Pour l'instant, je n'ai pas d'index, et je fais la requête suivante:

Code :

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

1
2
3
4
5
 
SELECT i.ID_item, i.Code_bar1, a.Code_bar2 
     FROM Item AS i
          LEFT OUTER JOIN Alias AS a ON i.ID_item=a.Item_id
               WHERE i.Code_bar1=@MaValeur

Si ça ne retourne rien, soit le produit n'existe pas soit l'utilisateur a scanner un Code_bar2, donc je fais:

Code :

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

1
2
3
4
SELECT i.ID_item, i.Code_bar1, a.Code_bar2 
     FROM Item AS i
          LEFT OUTER JOIN Alias AS a ON i.ID_item=a.Item_id
               WHERE a.Code_bar2=@MaValeur

Mes performance pour le premier cas, sont la plupart du temps de 2 secondes mais parfois peut aller jusqu'à 10 secondes. On m'a donc suggérer de créer des index pour Item.Code_nar1 et Item.Code_bar2.

Mais en cherchant un peu, j'ai appris l'existence des indexation composite. Mon idées serait donc de créer un index du genre:

Code :

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

CREATE NONCLUSTERED INDEX i_recherche ON Item(Code_bar1), Alias(Code_bar2)

- Cela marcherait il ?

Et du coup je ne ferai plus qu'une requête:

Code :

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

1
2
3
4
SELECT i.ID_item, i.Code_bar1, a.Code_bar2 
     FROM Item AS i
          LEFT OUTER JOIN Alias AS a ON i.ID_item=a.Item_id
               WHERE i.Code_bar1=@MaValeur OR a.Code_bar2=@MaValeur

-Cela améliorerait il la performance de ma recherche ?

Merci d'avance pour toute suggestion ou commentaire.

[SQLpro]

Table: Item
Colonnes: ID_item, Code_bar1

Table: Alias
Colonnes: ID_alias, Item_id, Code_bar2
(Item_id étant la clé étrangère correspondant a ID_item)

1) Il est déjà extrêmement étrange que votre colonne ID_item n'ait pas le même nom dans une table et dans l'autre alors que c'est la même information.
Sachez que la norme AFNOR sur le traitement de l'information exige que dans un système d'information toute information doit être identifié par un nom unique. Or là vous en avez 2 !

Pour l'instant, je n'ai pas d'index, et je fais la requête suivante:

2) le fait de créer des PRIMARY KEY créé des index de type CLUSTERED avec comme clef d'index les colonnes participant à la PK

CREATE NONCLUSTERED INDEX i_recherche ON Item(Code_bar1), Alias(Code_bar2)

Cette syntxe est fausse et ce n'est pas non plus une indexation composite.
Un Index composite est un index dont la clef d'index est composé de plusieurs colonnes. Dans ce cas :

Code :

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

CREATE NONCLUSTERED INDEX i_recherche ON Item(Code_bar1, Code_bar2)

Ce que vous tentiez était sans doute un index inclusif :

Code :

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

CREATE NONCLUSTERED INDEX i_recherche ON Item(Code_bar1) INCLUDE (Code_bar2)

Ainsi Code_bar2 ne participe pas à la clef d'index, mais est une colonnes redondante pour assurer une couverture artificielle à votre requête.

Le problème est qu'un index ne peut être construit que sur une seule table, à moins d'utiliser une vue indexées. Donc ces deux cas sont impossible.

Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/quoi-indexer/

Cela améliorerait il la performance de ma recherche ?

Peu probable car vous avez un LEFT OUTER JOIN.
Dans ce cas l'index le plus efficace sera un index simple sur la clef étrangère. Même la vue indexée ne sera dans ce cas d'aucune utilité !

A +

[mactwist69]

Tout d'abord, merci de ces réponses précises.

Mais alors y'a t il tout de même une utilité a créer des Index pour mes colonnes Code_bar1 et Code_bar2 (indépendamment, bien sur), comme on me l'a auparavant suggérer?

[SQLpro]

Un outer JOIN avec une clause WHERE revient à faire un INNER JOIN. Commencez par savoir ce que vous voulez faire et non pas tâtonner dans la vague.
Si INNER JOIN alors l'indexation de Codebar x est intéressante si c'est le critère de recherche.
Si OUTER JOIN, alors placer le filtre dans la jointure auquel cas l'index n'a aucun intérêt.

Le seul index indispensable dans tous les cas est l'index sur la clef étrangère.

A +

[mactwist69]

Je comprends, et merci de ta réponse.

Juste au passage, je ne tâtonne pas a gauche a droite au hasard. Je suis d'accord quand tu dis que le inner join reviens a un outer avec un where,

mais bon, ma première requête est cohérente, la deuxième est juste une copie sauf que le teste se fait sur la deuxième variable possible. Donc il y a une certaine cohérence, dans la suite des choses.

J'aurai pu remanier la deuxième, en mettant INNER, ca aurai été plus 'jolie'. Mais cela joue t il vraiment sur la performance?