Clé technique ou clé fonctionnelle ?

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_FON
 Column 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
go

La 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
go

Les 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 10000

Reste à 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,1
 index_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=1

et 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
go

Résultats

# Test Lignes % Lignes DATA_FON (I/O) DATA_KEY (I/O)
1 1 jour 24 680 0% 3 605 3 625
2 1 semaine 217 972 1% 32 371 32 573
3 2 semaines 435 644 2% 64 537 64 869
4 1 mois 940 032 4% 140 228 141 036
5 2 mois 1 930 810 7% 291 777 293 539
6 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...