Bonsoir,
Quelques remarques en passant.
Envoyé par
SQLpro
Toute clef primaire créé un index avec des statistiques derrières
Jusqu'ici pas de problème.
Envoyé par
SQLpro
Les statistiques ne sont disponibles que pour la première colonne d'un index composite.
Il serait temps que SQL Server tienne des stats sur la clé complète, à l’instar par exemple de DB2 qui procède ainsi depuis toujours, c'est-à-dire près de 30 ans.
Extrait du document IBM DATABASE 2, Release 1.0 Reference Summary SX23-3740-1 (1984) :
.png)
La colonne FULLKEYCARD permettait déjà de connaître les stats utiles, à une époque à laquelle Kropernic se souciait plus de son pouce et de son canard
que des index...
Envoyé par
SQLpro
Dès lors que vous avez plusieurs colonnes dans un index l'optimisation est souvent moins bonne.
Conséquence de ce qui précède, mais en l’occurrence, en quoi consiste très précisément l’optimisation ? Efficacité du regroupement des téléphones dans les pages de données ?
Envoyé par
SQLpro
C'est le cas avec cette problématique d'identification relative.
Peut-être, mais sous réserve d’avoir une explication claire de ce qu’on entend par optimisation. Pour le moment on est au niveau pifométrique.
Envoyé par
SQLpro
De plus les PK créée des index CLUSTERED
Je suppose que vous voulez dire que chaque clé primaire est dotée d’un index cluster. S’il en est ainsi, je dirais : sauf si on préfère qu’il en soit autrement. Exemple (qui ne sera pas mis en œuvre !) :
1
2
3
4
5
6
7
8
9
10
11
12
13
| CREATE TABLE TELEPHONE
(
PersonneId INTEGER NOT NULL
, TelephoneId TINYINT NOT NULL
, TelephoneNumero VARCHAR(24) NOT NULL
, CONSTRAINT TEL_PK PRIMARY KEY NONCLUSTERED (PersonneId, TelephoneId)
, CONSTRAINT TEL_PSN_PK FOREIGN KEY (PersonneId) REFERENCES PERSONNE ON DELETE CASCADE
) ;
ALTER INDEX TEL_PK ON TELEPHONE REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 80) ;
CREATE CLUSTERED INDEX TEL_NO_X ON TELEPHONE (TelephoneNumero)
WITH (PAD_INDEX = ON, FILLFACTOR = 80) ; |
=>
Index_Cluster_Notel.png)
Envoyé par
SQLpro
Avec deux colonnes composant la clef d'index, ce repère est donc 2 fois plus gros et impacte tous les index
Dans le cas de la table TELEPHONE il n’en est pas ainsi (comme le plus souvent dans le cas de l’identification relative). La colonne PersonneId mesure 4 octets et la colonne TelephoneId un seul octet (soit au plus 255 occurrences de téléphones par personne, ce qui dans le cas de Kropernic est plus que très largement suffisant). Si l’on n’utilise pas l’identification relative, l’identifiant TelephoneId devient absolu, mais son type est au moins celui de PersonneId, c'est-à-dire INTEGER dans l’exemple, ce qui représente 4 octets. Maintenant, je ne sache pas que 4 + 1 soit deux fois plus « gros » que 4.
Cela dit, si l’index cluster avait une clé composite avec des colonnes de type CHARACTER(N) avec N = gros, je ne dis pas. Mais on n’est absolument pas dans ce genre de scénario, Kropernic est quelqu’un de sérieux...
Envoyé par
SQLpro
l'ordre n'est pas constant à l'insertion (défaut de monotonie)
Si les ajouts des téléphones d’une personne se font aléatoirement, au fil des jours, c’est vrai, qu’il s’agisse de l’index primaire en cas d'identification relative ou de l’index secondaire en l’absence d’identification relative. En tout cas, lors des consultations :
En mode « Joe Transaction » (direct, léger), ça n’est pas un problème dans la mesure où l’on ne cherche pas à ramener tous les téléphones à chaque transaction. En mode « Jane Query » (direct, lourd) ou « Bill Batch », les performances pourraient être dégradées, mais heureusement, le DBA aura fait son travail de surveillance et déclenché en conséquence les réorganisations dans le but de prévenir la dégradation des performances. Par ailleurs, l’effet de monotonie ne se fait pas ressentir tant que le FILLFACTOR est choisi correctement (@Kropernic : j’espère que vous avez bien prévu ce paramétrage en ce qui concerne la table des gifts et les tables des stocks...)
Envoyé par
SQLpro
chaque update dans la clef impacte tous les index secondaires
Dans le cas de Kropernic (sauf s’il n’a pas suivi mes conseils), la valeur d’une clé primaire est invariante et dépourvue de toute signification, donc en l'occurrence aucun update en vue.
Envoyé par
SQLpro
Une solution consiste donc à avoir en sus de la clef primaire de type IDENTITY une clef alternative sur PRS_ID + NUM tel, et c'est probablement cet index qui sera utilisé pour récupérer un n° de téléphone dans les jointures !
Hum... Avec un seul index (utilisation de l’identification relative), les jointures se passent très bien, mais elles sont plus coûteuses avec votre index secondaire (voir les schémas en annexe ci-dessous). Si l’on vous suivait, on aurait désormais deux index sur les bras au lieu d’un seul, avec un surcoût important en I/O c'est-à-dire en accès aux disques tant en lecture qu’en écriture (je sais : tout est dans le cache, avec la nouvelle technologie de disques Tartempion on va dix fois plus vite, le temps de verrouillage est réduit d’autant, etc.) : pour ma part, je passe volontiers cet index supplémentaire au fil du rasoir d’Ockham et j’en resterai à l’identification relative.
Cela dit, je ne généralise pas et ne prétends pas avoir raison à tout coup, l’expérience m’a appris à être prudent et ne pas me fier à ma seule intuition ou à mes habitudes : Vérité en deçà des Pyrénées, erreur au-delà... Mais il y a une chose dont je suis sûr : c’est sur la base d’un prototypage des performances sérieux que c’est telle solution qu’il faut retenir plutôt qu’une autre, résultats chiffrés en main pour une appréciation objective.
En tout cas, vu les coûts I/O (cf. annexe ci-dessous), je ne m’engage pas trop en disant que Kropernic peut conserver sans problème l’identification relative :
PERSONNE-0,n----POSSEDER----(1,1)--TELEPHONE
En effet, pour accéder à une page de données, le nombre d’I/O est égal à H + 1, tandis que sans identification relative, le coût est égal à H’ + 1 + H’’ ; par ailleurs l’index primaire n’est pas obèse (les clés mesurent 5 octets, on a vu pire), la clé primaire n’est jamais modifiée, etc.
Mais si un prototypage des performances montrait que malgré tout c’est l’autre solution qui était à retenir, au nom de l’indépendance physique (8e des 12 règles de Codd), c’est sous le capot que les transformations s’effectueraient, de façon transparente.
Annexe. Coût I/O (accès aux disques)
1) Avec utilisation de l’identification relative
Suite à réorganisation de la table TELEPHONE, la situation est synthétisée dans la figure ci-dessous. Soit H la hauteur de l’arbre, c'est-à-dire le nombre de niveaux de l’index cluster (sans les données) : le coût pour accéder à un numéro de téléphone de la personne 12345 est égal au plus à H+1 I/O. Par exemple pour donner un ordre de grandeur — observé avec DB2 for z/OS, à ajuster évidemment dans le cas de SQL Server —, H = 2 pour moins de 85 000 téléphones, et H = 3 pour moins de 25 000 000. On peut voir venir.
Index_Cluster_Notel_IdRel.png)
A supposer qu’une personne ait moins de 256 téléphones (du fait de l’utilisation du type Tinyint pour la colonne TelephoneId, cf. l’instruction CREATE TABLE ci-dessus), le coût d’une requête pour récupérer tous les téléphones d’une personne est égal au coût de la récupération d’un de ses téléphones en particulier (+ 1 I/O disons si les téléphones sont à cheval sur deux pages).
2) Sans utilisation de l’identification relative
La situation devient la suivante, dans laquelle on peut considérer que H’’ = H’ + 1, bien que la clé primaire mesure 4 octets cette fois-ci au lieu de 5 (par comparaison avec les chiffres précédents qui valent pour H, à peu de choses près (toujours avec DB2) H’ = 2 pour moins de 97 000 téléphones, et H’ = 3 pour moins de 30 000 000).
Index_Cluster_Notel_IdAbs.png)
Identification relative VS performance
Répondre avec citation
Partager