Discussion :

[vinch999]

Salut,

Mon problème : Je dois stocker un gros volume de data dans une table (plusieurs milliards de lignes), l'unicité doit se faire sur une combinaison de 3 colonnes (un datetime A, un char(12) B,un int C) afin d'éviter les doublons potentiels.

Est- il mieux de :
-créer une PRIMARY KEY , combinaison des 3 champs ABC et donc un index clustered associé, donc 1 index
-Créer une PRIMARY KEY , avec un champ bigint auto-incrémenté avec index clustered associé + une contrainte d'unicité sur la combinaison des 3 champs ABC avec index non cluster associé, donc 2 indexes

Quel est le meilleur choix , et pourquoi?

Merci d'avance pour votre éclairage !

[SQLpro]

La 2eme solution sera incontestablement meilleure.

En effet lors de jointures, l'estimation de cardinalité sera plus affinée. En effet en cas d'index ayant une pluralité de colonne, seule la première colonne fait l'objet de statistiques.

A +

[vinch999]

Ah , bien entendu j'avais appliqué la 1ere solution .

Merci pour l'info

[vinch999]

Je ne fais par contre aucune jointure vers d'autres tables,
Mon besoin premier est destiné aux insertions (l'unicité permettant d’empêcher l'insert de doublon).
Le second aux lectures.

[SQLpro]

Là aussi plus la clef est simple, et monotone et plus rapide sera l'insertion et moins fragmentée sera la table.

A +

[vinch999]

ok merci pour l'info

[aieeeuuuuu]

Bonjour,

Ah , bien entendu j'avais appliqué la 1ere solution .

Elle n'est pas forcément mauvaise pour autant !
Mais cela dépend du contexte. Il nous faudrait en savoir plus.

Par exemple : vous avez une colonne date. Si vous insérez dans cette table des données de façon ordonnées sur cette colonne (par exemple table recevant des logs de je ne sais quelle application), le problème de fragmentation évoqué par SQLPro ne devrait pas se produire (mais cela dépend aussi de la façon dont les données sont insérées)

Cela dépend aussi des autres besoins d'indexation sur cette table.
Lorsque vous créerez des index secondaires, la clef de la table sera stockée. si la clef est constituée de vos trois colonnes plutôt que qu'un simple BIGINT, cela fera 16 octets de plus par ligne dans l'index. Sur un milliard de ligne, ça fait donc un index qui pèse environ 16Go de plus et ce n'est pas négligeable...

Donc, comme je le disais, tout dépend du contexte. En en sachant plus, on pourra mieux vous aider mais dans tous les cas, vous devrez impérativement effectuer des tests de performances (prototypage). Comparez donc les deux solutions en faisant des tests avec vos vraies données, dans les conditions les plus proches possible de celles de production. Dans votre cas et avec une telle volumétrie, ce sera la seule façon d'obtenir des réponses fiables, car toutes les réponses théoriques auront leurs limites...

Une fois que ce sera fait, revenez poster vos conclusions

[vinch999]

Merci pour les précisions.
Je peux effectivement assimiler ces données à des logs, j'ai un champ date (type int pointant vers une dimension date), un champ time (type time(0)) , + autre(s) champ(s) (soit real,soit char(12),soit les 2). C'est cette combinaison qui doit être unique.
J'ai testé les 2 méthodes.
La solution avec primary key autoincrémentée + unicité sur combinaison des champs ==> l'index non cluster (Unicité) fait que le volume global est beaucoup plus important qu'avec l'autre solution. LA différence n'est pas négligeable, +-35% Soit +-3To...

Donc à première vue je pense que la solution de la PK combinaison de champs me va mieux.
Sachant que je n'utiliserai pas la PK comme FK dans d'autres tables.

Le soucis pourrait par contre se situer du côté de l'insertion moins performante.

Je compte passer par une table de staging intermédiaire , et ensuit faire un MERGE avec la table finale.
Ce qui fait que j'ai un index peu fragmenté sur la staging, et je pourrais envisager faire un refresh de l'index de la table de destination après le MERGE.

Vous en pensez quoi?

[SQLpro]

Un test de volume est une chose et n'a que peu d'intérêt en soi. En effet vous ne dissociez pas dans votre volumétrie table et index. Vous verrez qu'avec une clef autoincrémenté, vous serez déjà gagnant sur le long terme sur le volume de la table (sans les autres index)...
Le problème viendra des performances et pire encore des blocages :
Pour les écritures la différence sera peu sensible
Pour les lectures, plus il y a d'index, plus rapide seront les accès
Pour la concurrence : avoir 1 table et 1 index permet une meilleure simultanéité d'accès !

Mais vous n'êtes pas obligez de me croire...

A +

[vinch999]

Salut,
Je ne comprends votre commentaire.
Je dois tenir compte du volume global de la table, indexes compris.
Et le volume des indexes est tout sauf faible, j'ai une différence de plusieurs To. (J'ai calculé en extrapolant le volume d'une journée de données fois n jours, je ne sais pas si le rapport est constant).
Je n'ai, bien entendu, pas un espace disponible illimité.

Le soucis de volume d'index vient du fait que l'unicité se fait sur une combinaison de nombreux champs de la table.