Discussion :

[vmolines]

Bonjour,

je suis en train de créer mes tables pour une base sous SQL Server. J'ai des tables d'association du genre :

T_UTILISATEUR_GROUPE : {#ID_UTILISATEUR, #ID_GROUPE}

Lors de la création de ma PK, je peux spécifier si c'est clustered ou non. Dans l'aide, ils disent qu'en clustered, ça a une incidence sur l'ordre physique des données dans la table et que ça convient très bien aux auto incréments.

Au feeling, je dirais qu'une PK composée ne devrait pas être clustered mais j'aimerais un éclairage sur ce point.

Merci

[SQLpro]

Difficile de répondre sans connaître le fonctionnel derrière. Par exemple si les clefs sont ordonnées naturellement (monotonie), alors pas de problème.

Exemple :
Table COMMANDE => CMD_ID : clef auto
Table PRODUIT => PRD_ID : clef auto
TABLE COMMANE_PRODUIT => PRD_ID, CMD_ID : clef

Dans ce cas il vaudrait mieux avoir : CMD_ID, PRD_ID en clef car les commandes vont croissante.

A +

[vmolines]

J'ai plusieurs tables d'associations dans mon modèle, j'ai présenté T_UTILISATEUR_GROUPE à titre d'exemple car elle ne figure pas dans mon modèle. Cependant j'aimerais vraiment bien déclarer mes clés composées car je comprends qu'en fonction des besoins, on pourra avoir des solutions physiques différentes.

Si je reprends T_UTILISATEUR_GROUPE, on peut se demander si on interroger cette table plutôt par utilisateur ou plutôt par groupe :

- "Tous les groupes auquel appartient un utilisateur" > servirait pour dire à quoi a accès un utilisateur par exemple
ou
- "Tous les utilisateurs d'un groupe" > avoir une vue d'ensemble de l'affectation à ce groupe

On peut aussi se poser la question pour l'insertion :

- "Ajout d'utilisateurs dans un groupe"
ou
- "Affectation de groupes à un utilisateur"

Dans l'application finale il y aura un de ces axe plus souvent utilisé mais l'autre , dans une moindre mesure, pourra aussi être utilisé.

Dans ce type de reflexion, quelles sont les bonnes questions à se poser ? Quelles sont les solutions physiques à mettre en place : ordre des champs dans l'index multicolonne ? index clustered ?


Ces questions de sens me font tourner en rond et j'aimerais bien avoir des éléments supplémentaires de réponse car je n'arrive à aucune conclusion tout seul.

[fsmrel]

Bonjour,

Je suis plus familier de DB2 que de SQL Server, mais je soupçonne ce dernier d’avoir recopié son grand cousin, qui permet la mise en oeuvre des index "cluster" depuis déjà 25 ans. Dans ce qui suit, je me situe dans un contexte DB2.

Un tel index mérite une attention toute particulière, puisque, tant qu’on n’est pas dans un univers quantique, il ne peut y en avoir qu’un par table. Là réside le secret de la performance des traitements transactionnels lourds (si je puis dire) et surtout batch.

En effet, considérez la composition de la clé d’un tel index : {Col1, Col2, ..., Coln}. Appelons XC cet index. Considérez encore que je réorganise la table T sur laquelle est branché XC : DB2 range physiquement les lignes de la table T selon la séquence des clés de XC.

Illustration. Considérez maintenant les deux tables suivantes des commandes et des lignes de commande (clés primaires soulignées) :

Commande {CdeId, NumeroCde, Date, CLientId, ...}

LigneCommande {LigneId, CdeId, ProduitId, Quantité, ...}

CdeId étant clé étrangère au sein de LigneCommande.

Supposons encore, au niveau physique cette fois-ci, qu’il y ait un index XLCdeCde branché sur la colonne CdeId de la table LigneCommande et que cet index soit déclaré cluster :

Si au cours d’une transaction on récupère les données de la commande NumeroCde = 1234, y-compris les lignes de commande correspondantes, DB2 ira chercher directement ces lignes dans la page physique où ces lignes ont été regroupées. Coût de l’opération relativement aux lignes de commande : de l’ordre de deux lectures "physiques" pour l’index XLCdeCde et une seule lecture pour récupérer l’ensemble les lignes de commande (en admettant qu’elles tiennent toutes dans une page physique).

Au contraire, si l’index XLCdeCde n’est pas cluster, vous pouvez en théorie avoir autant de lectures physiques que de lignes de commande, puisque celles-ci peuvent être éparpillées dans des pages distinctes. A 10 millisecondes la lecture de page, cela peut finir par coûter cher.

Maintenant, vous pouvez avoir intérêt (et je vous engage vivement à procéder ainsi) à identifier LigneCommande relativement à Commande, car au fond, la ligne de commande n’est jamais qu’une propriété (multivaluée) de la commande. Le jeu de clés devient le suivant :

Commande {CdeId, NumeroCde, Date, TauxTVA, ...}

LigneCommande {CdeId, LigneId, ProduitId, Quantité, ...}

La clé primaire de LigneCommande est alors la suivante : {CdeId, LigneId} et devient la clé de l’index XLCdeCde (toujours cluster).

Vous me direz : quelle est la valeur ajoutée par rapport à l’organisation précédente ? Elle existe et, à titre d’exercice, je vous demande de la découvrir, quand par exemple chaque ligne de commande se décompose elle-même en engagements sur ligne de commande et que transactions et batch doivent engendrer un minimum de lectures à 10 millisecondes.

Ce qui vaut pour les lectures vaut bien entendu pour les écritures (création, modification, suppression de commandes).

Si quelqu’un connaît bien SQL Server, merci de confirmer que le comportement des index cluster est comparable à celui que je viens de décrire.

[vmolines]

Oui il s'agit bien de la même notion :

Source MSDN

A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.

Je comprends parfaitement l'intérêt dans le cadre d'une entité faible (si je m'exprime bien) avec l'exemple commande/lignecommande (que j'ai dans mon modèle).

Par contre, dans le cadre d'une table d'association telle utilisateur/groupe dans mon autre exemple, quelle serait votre manière de procéder ?

- Vous étudiez quel axe d'approche de cette table sera le plus fréquent et vous mettez un index cluster pour cet axe
- Vous n'utilisez pas d'index clustered
- Autre


De plus vous soulevez l'impact d'un index clustered sur les opération d'insertion/suppression. Etant donné que les données sont physiquement écrites dans l'ordre de la clé composée, dans le cas où on ajouterait un enregistrement qui ne serait pas dans la dernière séquence, il faut alors que le SGBD décalle physiquement tous les enregistrements suivants pour y mettre le nouveau ?

Si c'est vrai, alors il faut vraiment considérer l'ordre naturel des ajouts/suppression dans l'application ainsi que leur fréquence. Cette considération irait de paire avec l'axe de lecture le plus souvent utilisé.

Pas évident tout ça...

edit : cette discussion est très intéressante pour moi. Je vous remercie déjà de votre participation.

[vmolines]

Après avoir donné le lien pour confirmer qu'il s'agissait bien de la même notion sur DB2 et SQL Server, j'ai lu l'article complet.

Et effectivement, il y a bien la problématique des insertions/suppressions :

Clustered indexes are not a good choice for:

* Columns that undergo frequent changes

This results in the entire row moving (because SQL Server must keep the data values of a row in physical order). This is an important consideration in high-volume transaction processing systems where data tends to be volatile.

Pour les cas favorables, ça va totalement dans le sens que vous exprimiez. Je vais donc méditer sur mes cas pour faire mes choix.

Mais si vous voulez compléter, n'hésitez pas car c'est toujours très intéressant.