Discussion :

[Kropernic]

Hello tout le monde.

Petite question qui, je pense, trouvera une réponse rapidement...

Voici le contexte : Il s'agit, pour la problématique de la question, de gérer l'historique du niveau de batterie de différents capteurs.

Voici le DDL (simplifié) des tables concernées :

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
 
CREATE TABLE TAGS (
  ID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),
  IDENTIFIER NVARCHAR(50) NOT NULL,
  COMPANY_ID UNIQUEIDENTIFIER NOT NULL,
  CONSTRAINT PK_TAGS PRIMARY KEY CLUSTERED (ID)
)
 
CREATE TABLE BATTERY_HISTORY(
  ID INT NOT NULL IDENTITY (-2147483648, 1),
  DATE DATETIMEOFFSET(7) NOT NULL DEFAULT SYSDATETIMEOFFSET(),
  LEVEL INT NOT NULL,
  TAG_ID UNIQUEIDENTIFIER NOT NULL,
  CONSTRAINT PK_BATTERY_HISTORY PRIMARY KEY CLUSTERED(
    ID
  ) WITH (FILLFACTOR = 80),
  CONSTRAINT FK_BATTERY_HISTORY_TO_TAG FOREIGN KEY (TAG_ID) REFERENCES DBO.TAGS(ID),
  CONSTRAINT CK_LEVEL CHECK(LEVEL BETWEEN 0 AND 100)
)

Ma question porte donc plus particulièrement sur la table BATTERY_HISTORY. Etant donné qu'on aura de multiple lignes pour chaque capteur, j'ai donc ajouté un id propre. Mais dans le monde l'IOT, le nombre de lignes risque fort de grimper très très vite. Du coup, j'ai un peu peur des perfs lorsqu'on voudra récupérer la dernière valeur pour un capteur histoire d'avoir son niveau "actuel" de batterie. Alors oui bien sûr, je pourrais ajouter un index sur les colonnes (TAG_ID, DATE) mais c'est toujours moins performant qu'un index cluster.

Du coup, je me demandais s'il ne serait pas judicieux de retirer la colonne ID et de passer l'index cluster sur les colonnes (TAG_ID, DATE).

Je suis certain que cette "configuration" améliorera grandement le temps d'exécuter de la requête de sélection pour avoir la dernière valeur d'un capteur mais ce sont les insertions qui me font peur alors.

Donc je viens demander l'avis des experts que vous êtes.

Merci d'avance.

EDIT : J'ai oublié de préciser que cela se passe sur une Azure Sql Database.

[escartefigue]

Bonjour,

Si on utilise fréquemment les éléments de l'historique d'un capteur, alors, je préconiserai a priori l'identification de la mesure relativement au capteur
Ce qui donne une PK composée de la PK du capteur + le chrono de la mesure.
Ceci permet, quand la PK est bien organisée, d'avoir tous les éléments rangés dans un espace physique contigu au bénéfice des perfs.
Il faut bien sur conserver une colonne d'horodatage de la mesure pour ne pas commettre la faute de débutant qui consiste à utiliser le chrono technique comme une séquence d'arrivée

[Kropernic]

Alors oui effectivement, l'historique va être fréquemment utilisé (ne fut-ce que pour avoir l'état "actuel" de la batterie).

Don oui, la PK sur l'id du capteur + date va accélérer les SELECT. Ca c'est certain vu qu'on aura au niveau des pages de l'index, toutes les lignes pour le capteur B, puis toutes les lignes pour le capteur C, puis toutes celles pour D, etc.

Mais quand on va vouloir ajouter une nouvelle ligne pour le capteur B, s'il n'y a plus de place pour l'insertion, vu que ça se faire en plein milieu de l'index, ça va recréer toute la table non ? Et donc bloquer les lectures du coup.

C'est cela qui m'inquiète...

[aieeeuuuuu]

Bonjour

Mais quand on va vouloir ajouter une nouvelle ligne pour le capteur B, s'il n'y a plus de place pour l'insertion, vu que ça se faire en plein milieu de l'index, ça va recréer toute la table non ?

Non, ça provoquera juste un split de page, ce qui est certes plus long qu'une insertion dans une page existante, mais pas vraiment problématique en soit.
à terme, ça pourrait aussi augmenter la fragmentation de l'index, mais comme la deuxième clef est strictement croissante, la fragmentation globale devrait être assez limitée (à surveiller tout de même)

[aieeeuuuuu]

Alors oui bien sûr, je pourrais ajouter un index sur les colonnes (TAG_ID, DATE) mais c'est toujours moins performant qu'un index cluster.

En incluant la colonne LEVEL, ça reviendrait au même qu'un index cluster (mais aussi avec les mêmes problémeatique pour la mise à jour, voire plsu car deux indexes à mettre à jour), dans ce cas, autant se passer effectivement de la colonne ID, et de l'index secondaire...
Mais cela n'est plus vrai s'il y a d'autres colonnes dans la table... est-ce le cas ?

[Kropernic]

Bonjour



Non, ça provoquera juste un split de page, ce qui est certes plus long qu'une insertion dans une page existante, mais pas vraiment problématique en soit.
à terme, ça pourrait aussi augmenter la fragmentation de l'index, mais comme la deuxième clef est strictement croissante, la fragmentation globale devrait être assez limitée (à surveiller tout de même)

Ah bin oui ! C'est juste la page qui se split... Du coup, avec une pk de type UNIQUEIDENTIFIER, ce n'est pas si dramatique que ça non plus si, par mégarde, un id n'est pas créé via la fonction NEWSEQUENTIALID(). Cela provoquera aussi un split mais pas la reconstruction de la page... Je m'étais stupidement laissé influencé par un soi-disant gourou qui affirmait que la table entière allait être recréée si un GUID était inséré en plein milieu...

Pour en revenir à notre clef composée, est-ce un problème si je mets la deuxième colonne (la date) en ordre descendant ? Je me dis que pour la recherche, ça ira plus vite si la valeur qu'on cherche est au début.

Mais en écrivant, je me dis que pour le stockage (et donc les pages), c'est débile... Car ça va devoir faire de la place en début de page à chaque fois. Donc vaut mieux laisser en croissant. Correct ?