Discussion :

[joKED]

Bonjour à tous,

Je viens solliciter vos lumières pour un choix particulièrement cornélien (dû effectivement à mon manque de connaissances en la matière, je l'assume).

Je dois créer un table qui doit stocker des valeurs temporelles et entières pour des capteurs.
La majorité des requêtes qui seront effectuées devront permettre d'effectuer des aggrégats de valeurs (SUM, AVG, ...) sur des périodes de temps définies pour 1 ou N capteur (exemple : somme de toutes les valeurs du capteur 12 entre date A et date B, ou somme de toutes les valeurs entre date A et date B pour les capteurs 10 à 50, groupé par identifiant de capteur). Bref, rien de fantastique.
Ces valeurs de capteurs seront insérées à rythme soutenu (plusieurs centaines par minute), et je prévois environ 500 millions d'insertions sur l'année (à la louche, fourchette haute).
Je n'aurais pas besoin de référencer les données de cette table dans une autre table.
Je sais que chaque couple DeviceId/ValueDate sera absolument unique.

J'ai donc 2 approches pour le choix de la clé primaire.
Soit je fais simplement une clé composite avec DeviceId/ValueDate en tant que clé primaire tel que suit :

Code :

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

1
2
3
4
5
6
7
CREATE TABLE T_Test (
    DeviceId BIGINT,
    ValueDate DATETIME,
    Value INT,
    ValueType TINYINT,
    PRIMARY KEY (DeviceId, ValueDate)
);

En principe, je suis supposé y gagner en terme d'indexation. Mais je risque d'y perdre en performance d'insertion (il faut bien que le SGBD vérifie l'unicité de la paire).

Soit je me fie à mes habitudes, et j'utilise une clé primaire auto incrémentée tel que suit (ce qui m'oblige à créer ensuite un index sur DeviceId/ValueDate) :

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE T_Test (
    Id BIGINT IDENTITY PRIMARY KEY,
    DeviceId BIGINT,
    ValueDate DATETIME,
    Value INT,
    ValueType TINYINT,
    INDEX IX_DeviceId_ValueDate (DeviceId, ValueDate)
);

Mais je risque d'y perdre en performance de lecture (quoique, avec l'index sur la paire, ça devrait pas être pire non ?), et en gestion d'espace (il va bien falloir stocker une colonne bigint en plus, qui a aucun intérêt métier pour moi).

Selon vous, quelle est l'approche la plus optimisée selon mon usage, tant en terme de performances (surtout à la lecture) et d'espace (l'espace disque supplémentaire, le service achat trouve que ça coûte cher).

Je vous remercie d'avance de vos réponses éclairées (et vous présente mes excuses pour mon ignorance).

[SQLpro]

Une clé composite n'est jamais bonne sauf à de très rares exceptions...

Plus une clé est "lourde" (nombre de colonnes, octets...) moins elle sera efficace dans tout un tas de problématique... Performances d'accès, mise à jour (INSERT, UPDATE, DELETE), maintenance, qualité des plans d'exécution des requêtes....

De plus, concernant des capteurs et l'utilisation que vous voulez en faire (agrégats) il faut rendre synchrones vos mesures....

Étant donné que j'ai modélisé la base de données de Vigicrues en 2011 en remplacement d'une base de données PostGreSQL qui ne donnait pas satisfaction... je vais vous donner quelques conseils et vous montrer comment on fait cela...

Je suppose que vos mesures viennent de capteurs il faut donc savoir de quelques capteurs chaque mesure provient. Il faut donc une entité "source" avec les informations des capteurs (dans Vigicrues nous avons des stations qui ont jusqu'à 3 capteurs : limnimétrie, débit, pluviométrie).

Pour pouvoir agréger vos données, il faut savoir à quelle moment elle sont captées et surtout synchroniser vos données (quelle soient relevées exactement au même moment...). Or cela est impossible si vous vous contentez de l'information d'horodatage de vos capteurs.... Chacun donnera une valeur différentes à quelques secondes... Ce qui vous fera perdre 50% de vos données aux borne de l'intervalle d'agrégation... De plus les requêtes seront complexes à écrire et peu performantes... Pour éradiquer ce problème à la source, il faut recaler les données avec une table de chronodatation.

Supposons que vos données viennent toutes les minutes... Dans ce cas il faut créer une table à double entrée : dateheure à la minute et ID.

Enfin, vos mesures doivent être de type réel et non en entier sinon vos agrégats vont être moins rapide à calculer et approximatif, car les opérations se feront en entier...

Ceci conduit au modèle conceptuel suivant (élaboré avec Power Designer) :



Qui, décliné pour MS SQL Server via Power Designer, donne le modèle physique suivant :



Qui traduit en SQL par Power Designer donne le script suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
/*==============================================================*/
/* Nom de SGBD :  Microsoft SQL Server 2012                     */
/* Date de création :  10/08/2024 18:12:45                      */
/*==============================================================*/
 
 
/*==============================================================*/
/* Schéma SQL : S_PROD                                          */
/*==============================================================*/
CREATE SCHEMA S_PROD
go
 
/*==============================================================*/
/* Schéma SQL : S_REF                                           */
/*==============================================================*/
CREATE SCHEMA S_REF
go
 
/*==============================================================*/
/* Table : T_CHRONODATATION_CDT                                 */
/*==============================================================*/
create table S_REF.T_CHRONODATATION_CDT (
   CDT_ID               INT                  identity,
   CDT_DATE_HEURE       datetime2            not null,
   constraint PK_CDT primary key nonclustered (CDT_ID),
   constraint UK_CDT_DATEHEURE unique (CDT_DATE_HEURE)
)
go
 
/*==============================================================*/
/* Table : T_MESURE_MSR                                         */
/*==============================================================*/
create table S_PROD.T_MESURE_MSR (
   MSR_ID               bigint               identity,
   SRC_ID               INT                  null,
   MST_ID               smallint             not null,
   CDT_ID               INT                  not null,
   MSR_VALEUR           float                not null,
   constraint PK_MSR primary key nonclustered (MSR_ID)
)
go
 
/*==============================================================*/
/* Index : DATEE_FK                                             */
/*==============================================================*/
create index DATEE_FK on S_PROD.T_MESURE_MSR (
CDT_ID ASC
)
go
 
/*==============================================================*/
/* Index : TYPEE_FK                                             */
/*==============================================================*/
create index TYPEE_FK on S_PROD.T_MESURE_MSR (
MST_ID ASC
)
go
 
/*==============================================================*/
/* Index : MESUREE_FK                                           */
/*==============================================================*/
create index MESUREE_FK on S_PROD.T_MESURE_MSR (
SRC_ID ASC
)
go
 
/*==============================================================*/
/* Table : T_MESURE_TYPE_MST                                    */
/*==============================================================*/
create table S_REF.T_MESURE_TYPE_MST (
   MST_ID               smallint             identity,
   MST_CODE             char(8)              not null,
   MST_LIBELLE          varchar(256)         not null,
   constraint PK_MST primary key nonclustered (MST_ID),
   constraint UK_MST_CODE unique (MST_CODE)
)
go
 
/*==============================================================*/
/* Table : T_SOURCE_SRC                                         */
/*==============================================================*/
create table S_REF.T_SOURCE_SRC (
   SRC_ID               INT                  identity,
   SRC_CODE             char(16)             not null,
   SRC_LIBELLE          varchar(256)         not null,
   constraint PK_SRC primary key nonclustered (SRC_ID),
   constraint UK_SRC_CODE unique (SRC_CODE)
)
go
 
alter table S_PROD.T_MESURE_MSR
   add constraint FK_MSR_CDT foreign key (CDT_ID)
      references S_REF.T_CHRONODATATION_CDT (CDT_ID)
go
 
alter table S_PROD.T_MESURE_MSR
   add constraint FK_MSR_SRC foreign key (SRC_ID)
      references S_REF.T_SOURCE_SRC (SRC_ID)
go
 
alter table S_PROD.T_MESURE_MSR
   add constraint FK_MSR_MST foreign key (MST_ID)
      references S_REF.T_MESURE_TYPE_MST (MST_ID)
go

Pour remplir la table de chronodatation, il suffit de faire :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SET NOCOUNT ON;
 
DECLARE @I INT = 0;
SET IDENTITY_INSERT S_REF.T_CHRONODATATION_CDT ON;
 
-- insertion des 2300 premières valeurs depuis le 1/1/2024
WHILE @I < 2300
BEGIN
   INSERT INTO S_REF.T_CHRONODATATION_CDT (CDT_ID, CDT_DATE_HEURE)
   VALUES (@I, DATEADD(minute, @I, '2024-01-01'));
   SET @I = @I + 1
END; 
 
-- insertion de 2300² valeurs suivantes, soit jusqu'au 2034-02-01 14h30
--> un peu plus de 10 ans / 5 289 991 lignes en tout
INSERT INTO S_REF.T_CHRONODATATION_CDT (CDT_ID, CDT_DATE_HEURE) 
SELECT 2300 + ROW_NUMBER() OVER(ORDER BY T1.CDT_ID + 2300 * T2.CDT_ID), 
       DATEADD(minute, 1 + T1.CDT_ID + 2300 * T2.CDT_ID, '2024-01-02 14:19')
FROM   S_REF.T_CHRONODATATION_CDT AS T1
        CROSS JOIN  S_REF.T_CHRONODATATION_CDT AS T2;
 
SET IDENTITY_INSERT S_REF.T_CHRONODATATION_CDT OFF;

A +

[joKED]

Bonjour,

J'ai simplifié la description de ma table lorsque j'ai posé la question, j'ai bien entendu une table qui référence le capteur, et une table qui référence le type de donnée reçu.

Concrètement, chaque capteur peut envoyer n types de donnée, mais jamais plusieurs données au même horodatage, elle ne sont jamais synchrones. Dans la réalité du terrain, elles devraient l'être, mais dans la réalité de la source de données, elles ont systématiquement un horodatage différent, probablement car l'horodatage de la source provient d'une date de traitement ou d'insertion en DB au lieu d'une date de "captage" sur le terrain. C'est moche, mais c'est le cas malheureusement.
Exemple, le capteur C mesure la pression à l'instant T, ensuite la température à T+5 secondes, ensuite l'humidité à T+13 secondes, etc...
Le capteur D mesure la pression à l'instant T+1 seconde, ensuite l'humidité à T+7 secondes, ensuite la pression à T+9 secondes, etc...

Dès lors, j'ai bien besoin d'un horodatage pour chaque mesure, il ne me paraît du coup pas pertinent d'avoir une table d'horodatage. Cette table aurait du sens si je venait à "tronquer" à la minute les dates reçues, mais le client veut absolument avoir une granularité à la seconde près.

En tout cas, un grand merci pour cette réponse détaillée, ça me permet de remettre en question une belle partie du projet dont j'ai hérité.
Je vais pouvoir essayer de "tordre" le fournisseur de la source afin que les données soient synchrones, et auquel cas, je pourrais appliquer la structure suggérée.

Pour revenir au sujet initial de la question, je note qu'il est donc plus efficace de "toujours" (il y a surement des exceptions) passer par une clé unique auto incrémentée, et de poser les bons index sur les données à requêter, que de créer de la clé composite lourde en tant que clé primaire.

[SQLpro]

...
Concrètement, chaque capteur peut envoyer n types de donnée, mais jamais plusieurs données au même horodatage, elle ne sont jamais synchrones. Dans la réalité du terrain, elles devraient l'être, mais dans la réalité de la source de données, elles ont systématiquement un horodatage différent, probablement car l'horodatage de la source provient d'une date de traitement ou d'insertion en DB au lieu d'une date de "captage" sur le terrain. C'est moche, mais c'est le cas malheureusement.

Et oui, c'est normal...

...Dès lors, j'ai bien besoin d'un horodatage pour chaque mesure, il ne me paraît du coup pas pertinent d'avoir une table d'horodatage. Cette table aurait du sens si je venait à "tronquer" à la minute les dates reçues, mais le client veut absolument avoir une granularité à la seconde près.

Il faudra dons faire une table à la seconde, et c'est TRES pertinent..... Ce sera une table de 315 millions de ligne, chaque ligne occupant 8 octets, soit environ 315 570 pages, soit 2,5 Go.... C'est peu !

...
En tout cas, un grand merci pour cette réponse détaillée, ça me permet de remettre en question une belle partie du projet dont j'ai hérité.
Je vais pouvoir essayer de "tordre" le fournisseur de la source afin que les données soient synchrones, et auquel cas, je pourrais appliquer la structure suggérée.

Ce ne sera jamais possible.... Il faut faire un recalage au plus juste à la seconde.

...Pour revenir au sujet initial de la question, je note qu'il est donc plus efficace de "toujours" (il y a surement des exceptions) passer par une clé unique auto incrémentée, et de poser les bons index sur les données à requêter, que de créer de la clé composite lourde en tant que clé primaire.

Et oui, et c'est pas nouveau !

A +