Discussion :

[Roubachof]

Chers développeurs,

J'ai une table de positions gps de 9Go qui possède donc un grand nombre de lignes.
Cette table a comme index clusterisé

GpsVehicleID bigint,
GpsTime dateTime

Il y a beaucoup d'insertions (plus de 100 par secondes). En règle générale, les lignes insérées se suivent plus ou moins selon la colonne GpsTime.
Le nombre total de véhicules est lui peut élevé (à peu près 5000).

Au départ mon cluster était inversé, le temps étant en premier.
Mais nous nous sommes alors aperçu que toutes les requêtes faites étaient d'abord filtrées par le GpsVehicleID puis par le temps.
Nous avons donc décidé d'inverser le cluster afin d'économiser la maintenance d'un index non clusterisé sur une table aussi obèse.

Cependant, aujourd'hui j'ai un doute. Est-ce que cela ne créerait pas plus rapidement de la fragmentation dans le cluster de ne pas mettre en premier la colonne sensée augmenter de manière monotone ?

[SQLpro]

évidemment oui !

Mais pour la meilleure configuration il nous faudrait connaître le SGBDR. Par exemple pour SQL Server je vous aurait conseillé de rajouter une clef auto incrémentée de type BIGINT et de créer l'index cluster dessus, puis de créer une contrainte d'unicité sur vos colonnes avec un FILL factor de l'ordre de 90%

A +

[Luc Orient]

... pour SQL Server je vous aurait conseillé de rajouter une clef auto incrémentée de type BIGINT et de créer l'index cluster dessus, puis de créer une contrainte d'unicité sur vos colonnes avec un FILL factor de l'ordre de 90% ...

Donc deux index au lieu d'un ... Si c'est le cas, l'optimisation proposée m'échappe quelque peu ...

[Roubachof]

Je ne comprends pas bien l'intérêt de créer un index cluster sur une colonne que je devrai rajouter, et qui ne me sera d'aucune utilité...
Et comme l'a souligné Luc, cela me rajoute un index en plus.

Ma question portait plus sur l'intérêt du cluster regroupé par VehiculeID et non par le temps gps. Je me demande si ça vaut le coup d'économiser un index non cluster grâce à ce cluster (rajouter ce type d'index fait grossir la table de 25%, non négligeable, avec tous les traitements à rajouter au niveau de la mise à jour de l'index), sachant qu'apparemment selon vous et comme je le soupçonnais cela va créer de la fragmentation plus rapidement qu'un cluster sur le temps gps.

Donc ma question est plutôt qu'est-ce qui a le plus d'impact sur les performances entre ces deux possibilité ?

Merci.

[SQLpro]

Je n'ai pas le temps de faire une explication de 3 heures sur le sujet.

Commencez par lire ceci : http://sqlpro.developpez.com/optimis...ntenanceIndex/
Il vous faut comprendre comment sont structurées les pages de données et d'index dans un SGBDR

Ensuite comprenez que :
sans index cluster, la ligne d'une table est repéré à l'aide d'une clef physique comportant 3 éléments :

• le n° de fichier
• le N° de page dans le fichier
• et enfin le N° de slot de ligne dans une page

Tous les index créés doivent référencer la ligne de la table pour qu'après recherche dans l'index on puisse revenir sur le table.
Si vous utilisez un index cluster, alors la ligne de la table n'est plus repérée par cet identifiant physique, mais par l'indentifiant logique que constitue la valeur de la clef cluster. Tous les index secondaire utiliseront donc cet info pour retrouver la ligne

Si votre clef de cluster est longue ou composé de plusieurs parties, alors elle devient moins intéressante que de ne pas clusteriser.
C'est pourquoi l'index cluster doit être le plus court possible.
Or à l'évidence DATETIME + BIGINT = 2 colonnes et 16 octets.
Alors que BIGINT d'auto incrément = 1 colonne et 8 octets.
Qu'est ce qui sera plus optimisé selon vous : que tous vos index secondaires ait à faire référence à la ligne par 16 octets en 2 colonnes ou 8 octets en 1 colonne ?

A +

[Roubachof]

Tout d'abord merci pour le lien!
Celui-ci a répondu de manière concrète à ma question principale.
En effet, avec un GpsVehiculeID en première colonne de cluster, cela va créer un nombre important de split de pages, et donc de fragmentation... Dans mon index cluster.

Par contre, et malgré la lecture de votre article, je ne vois toujours pas l'avantage du cluster sur clé auto-incrémentée.
Avec cette solution, je dois créer un index non cluster en plus, donc je me retrouve à gérer l'index non-cluster sur (GpsVehiculeID, GpsTime) ET l'index cluster sur la clé auto-incrémentée.

Lorsqu'une recherche sera faite dans cet index, cela me coutera le coût du parcours de l'index + le lien vers l'index cluster.
Sa taille sera aussi grossie par ce nouvel index cluster.

Alors que si je crée un seul index, cluster, sur (GpsVehiculeID, GpsTime), j'accède directement à mes lignes, sans indirection.
Je paye certes la taille du cluster sur deux colonnes, mais j'économise quand même par rapport à un cluster + un index non cluster sur ces mêmes deux colonnes.

Maintenant je dis ça bien humblement, c'est juste qu'il y a quelque chose qui doit m'échapper. Si vous pouviez m'éclairer cela me permettrait de progresser dans ces méandres...