Introduction
On observe de plus en plus souvent la création de modèles superposant clé technique avec clé fonctionnelle. Ce choix est justifié par le fait que : "réaliser une jointure sur une seule colonne est plus efficace que sur plusieurs".
Cette expression de bon sens mérite néanmoins d’être soumise à la loi des chiffres… qui vont contredire quelque peu cette vérité.
La normalisation dans ce test est partielle dans le sens ou le même objet contient à la fois sa clé technique et sa clé fonctionnelle. Il est clair qu’une normalisation complète de la table - en remplaçant une clé par une autre - va fournir de meilleures performances, c’est la nature même d’un modèle relationnel. En l’occurence ce n’est pas le propos ici.
Conditions du test
Le modèle
Soit une table classique sans typage exotique.
exec sp_help DATA_FONColumn name Type -------------------- ------------ ... SECURITY_ID int SECURITY_ACCRINT_ID int AI_UPDATE_USER int AI_UPDATE_DATE datetime AI_UPDATE_TIME char(8 ) ACCRINT_AMOUNT float ACCRINT_DATE_END datetime ACCRINT_FLAG int ACCRINT_INTEREST_LEG int ACCRINT_PROVIDER int ACCRINT_RATE float ACCRINT_STATUS int ACCRINT_TRADE_DATE datetime ACCRINT_VALUE_DATE datetime
Cette table contient plus de 25 millions de lignes pour 2,5 Go de données.
La clé fonctionnelle est composée des deux colonnes ACCRINT_PROVIDER ( int )
et ACCRINT_VALUE_DATE ( datetime ) .
Le test va consister à dupliquer cette structure en lui ajoutant une clé technique, entier correspondant à toutes les valeurs différentes du couple
Génération de la clé technique
On decrit ici la manière dont est générée la clé technique.
select distinct
ACCRINT_PROVIDER,
ACCRINT_VALUE_DATE,
KEY_ID=identity(5),
PERIMETER=convert(int,0)
into KEY_REF
from DATA_FON
goLa table KEY_REF ainsi créée va également nous servir lors du test, à
déterminer la portée de la jointure, par le biais de la colonne PERIMETER.
La table contenant à la fois la clé fonctionnelle et la clé technique va être créée, et alimentée, à partir d’une vue decrivant la structure cible. Elle sera manipulée par bcp afin d’éviter d’avoir à gérer des contraintes de taille journal de transaction, et surtout pour accéler le traitement
create view v_data
as
select d.*,
k.KEY_ID
from DATA_FON d
inner join KEY_REF k on
k.ACCRINT_PROVIDER = d.ACCRINT_PROVIDER
and k.ACCRINT_VALUE_DATE=d.ACCRINT_VALUE_DATE
goLes donnees sont extraites avec bcp
bcp test..v_data out data.bcpn -n -S ... -U ... -P ...… la table cible est créée en utilisant la structure de la vue…
select * into DATA_TEC from v_data where 1=2… puis les données insérées dans la structure ainsi créée par bcp.
bcp test..DATA_TEC in data.bcpn -n -S ... -U ... -P ... -b 10000Reste à créer l’index equivalent sur la clé technique
Comparaison des objets
Table DATA_FON
Cette table est la structure originale, une clé fonctionnelle est posée sur 2 colonnes
exec sp_spaceused DATA_FON,1index_name size reserved unused ---------- --------- --------- ------ IDX_FON 509272 KB 509418 KB 146 KB (1 row affected) name rowtotal reserved data index_size unused -------- -------- ---------- ---------- ---------- ------ DATA_FON 26677334 2835610 KB 2326140 KB 509272 KB 198 KB
optdiag statistics test..DATA_FON -U ... -P ..Statistics for table: "DATA_FON" Data page count: 1163070 Empty data page count: 0 Data row count: 26677334 Forwarded row count: 0 Deleted row count: 0 Data page CR count: 146055 OAM + allocation page count: 5497 First extent data pages: 0 Data row size: 82.4941... Parallel join degree: 0 Unused page count: 3 OAM page count: 23 Derived statistics: Data page cluster ratio: 0.9993... Space utilization: 0.9385... Large I/O efficiency: 0.9954... Statistics for index: "IDX_FON" (nonclustered) Index column list: "ACCRINT_PROVIDER", "ACCRINT_VALUE_DATE" Leaf count: 251673 Empty leaf page count: 0 Data page CR count: 725870 Index page CR count: 31830 Data row CR count: 3816057 First extent leaf pages: 0 Leaf row size: 19 Index height: 3 Derived statistics: Data page cluster ratio: 0.8418... Index page cluster ratio: 099831... Data row cluster ratio: 0.8960... Space utilization: 0.9990... Large I/O efficiency: 0.9883...
Table DATA_TEC
Cette table correspond à DATA_FON, avec la clé technique en plus.
exec sp_spaceused DATA_TEC,1;index_name size reserved unused ---------- --------- --------- ------ IDX_TEC 293770 KB 293960 KB 190 KB (1 row affected) name rowtotal reserved data index_size unused -------- -------- ---------- ---------- ---------- ------- DATA_TEC 26677334 2734878 KB 2435598 KB 293770 KB 5510 KB
optdiag statistics test..DATA_TEC -U ... -P ...Statistics for table: "DATA_TEC" Data page count: 1217799 Empty data page count: 0 Data row count: 26677334 Forwarded row count: 0 Deleted row count: 0 Data page CR count: 153141 OAM + allocation page count: 5075 First extent data pages: 0 Data row size: 86.4941... Parallel join degree: 0 Unused page count: 2639 OAM page count: 21 Derived statistics: Data page cluster ratio: 0.9991... Space utilization: 0.9398... Large I/O efficiency: 0.9940... Statistics for index: "IDX_TEC" (nonclustered) Index column list: "KEY_ID" Leaf count: 145778 Empty leaf page count: 0 Data page CR count: 753471 Index page CR count: 18361 Data row CR count: 3959331 First extent leaf pages: 0 Leaf row size: 11 Index height: 3 Derived statistics: Data page cluster ratio: 0.8420... Index page cluster ratio: 0.9989... Data row cluster ratio: 0.8923... Space utilization: 0.9985... Large I/O efficiency: 0.9924...
Analyse
Comparons les structures :
| Paramètre | DATA_FON |
DATA_TEC |
|---|---|---|
| Taille des données (DATA) | 2326140 KB | 2435598 KB |
| Taille des indexes (INDEX) | 509272 KB | 293770 KB |
| OAM page count | 23 | 21 |
| Data page count | 1163070 | 1217799 |
| Data row count | 26677334 | 26677334 |
| Data row size | 82 | 86 |
| INDX Leaf count | 251673 | 145778 |
| INDX Leaf row size | 19 | 11 |
| INDX Index height | 3 | 3 |
La taille des données est plus importante côté DATA_TEC (100 M) , car elle
contient une colonne en plus que la table originale. En revanche l’index est
plus léger avec la clé technique. (200 Mo de moins que la clé fonctionnelle,
soit 60% de gain en espace).
La hauteur de l’index est identique dans les deux 2 cas : 3.
La taille moyenne de chaque ligne (data row size) est plus grande pour
DATA_TEC que pour DATA_FON, ce qui implique un nombre plus important de pages
de données (1217799 contre 1163070).
Les écarts ne seront perceptibles qu’en sélectionnant des plages de données
assez larges, et que naturellement, les accès à la table DATA_TEC seront plus
nombreux, donc relativement plus lents.
L’idée reçue est donc infirmée dans le cas présent, les tests chiffrés suivants vont illustrer cette situation.
Les tests
Description
Les 2 tables vont être interrogées 6 fois chacune, partant d’un périmetre restreint vers un nombre de lignes plus important.
Pour ce faire on va utiliser la table KEY_REF précédemment créée, déterminer
un périmètre et réaliser une jointure soit par la clé technique, soit par la
clé fonctionnelle.
La mesure des performances est réalisée en comptant les I/Os
(Entrées/Sorties), ce qui donne pour l’interrogation de la table DATA_TEC:
set statistics io , time on
go
select d.ACCRINT_FLAG
into #tmp
from KEY_REF k
inner join DATA_TEC d on d.KEY_ID=k.KEY_ID
where k.PERIMETER=1et pour DATA_FON
set statistics io , time on
go
select d.ACCRINT_FLAG
into #tmp
from KEY_REF k
inner join DATA_FON d on
d.ACCRINT_PROVIDER=k.ACCRINT_PROVIDER
and d.ACCRINT_VALUE_DATE=k.ACCRINT_VALUE_DATE
where k.PERIMETER=1
goRésultats
| Test | Lignes | % Lignes | DATA_FON (I/O) |
DATA_KEY (I/O) |
|---|---|---|---|---|
| 1 jour | 24 680 | 0% | 3 605 | 3 625 |
| 1 semaine | 217 972 | 1% | 32 371 | 32 573 |
| 2 semaines | 435 644 | 2% | 64 537 | 64 869 |
| 1 mois | 940 032 | 4% | 140 228 | 141 036 |
| 2 mois | 1 930 810 | 7% | 291 777 | 293 539 |
| 3 mois | 2 851 684 | 11% | 430 072 | 432 658 |
Les résultats sont sans appel: quelle que soit la plage retournée, le nombre d’I/Os nécessaire à la réalisation de la requête est systématiquement inférieur en utilisant la clé fonctionnelle. Pour 1 mois par exemple, l’usage de la clé technique coûte 141 036 I/Os contre 140 228 pour la clé fonctionnelle.
Conclusion
L’usage d’une clé technique ajoutée an parallèle d’une clé fonctionnelle peut apparaître séduisante à première vue pour des raisons de performance.
Malgré l’espace supplémentaire occupé (minime *), la complexification des tâches d’alimentation (il faut maintenir la dite clé technique) et l’administration supplémentaire que cela implique, on peut imaginer adopter cette dénormalisation partielle avec une clé technique pour "améliorer" les performances de restitution.
Hélas, l’analyse des mesures de performance montre qu’une telle clé technique dégrade légèrement les performances d’interrogation.
* minime… ou pas, tout dépend du typage. Certains, sous SQL Server utilisent un GUID (16 octets) pour clé technique: sans commentaire…