Index sur table d'historiques

Version imprimable

13/10/2009, 23h56
Arnard

Index sur table d'historiques

Bonjour,

je travaille actuellement sur un table d'historique, amenée à terme contenir plus centaines de milliers d'enregistrements. Je cherche à améliorer mon temps de consultation selon divers critères.

Ma table se définit de la manière suivante :
- Code : uniqueidentifier (PK)
- CodeEvt : uniqueidentifier
- Designation : varchar
- CodeFamille : uniqueidentifier
- CodeDomaine : uniqueidentifier
- Type : int
- Libelle : varchar
- Commentaire : varchar (NULLABLE)
- Batiment : varchar
- Date : datetime
- ... Divers autres champs secondaires pour la recherche

Je suis amené à faire une recherche incluant automatiquement sur les champs Date, Batiment (en général de la forme LIKE 'bat01%'), CodeFamille, CodeDomaine, Type. Les champs Libelle et commentaire peuvent être inclus, mais ce n'est pas la majorité des cas.

Vu le volume de données, une recherche efficace implique la mise en place d'index. Mais comment dois-je le définir sous SQL Server ? Quels éléments doivent constituer mon index ? Dois-je en faire un seul ou plusieurs index ? Quel type lui donner (CLUSTERED ? autre ?) ?

Merci pour votre aide :)
14/10/2009, 09h01
martinus45

Bonjour,

C'est très simple, il faut au moins que tu définisses un index secondaire composé des champs qui sont obligatoires dans la recherche.

Il est préférable de commencer par ceux qui ont le plus grand nombre d'occurences (si tu n'a que 5 batiments et n'importe quelle date, mets cette dernière en premier).

Avec une machine normalement puissante, cela devrait être suffisant. Le volume que tu indiques n'est pas énorme.

Si tu dois ou veux améliorer, tu peux ajouter les champs facultatifs les plus fréquemment fournis dans la recherche, voire faire plusieurs index avec les mêmes champs obligatoires pour commencer et différentes combinaisons de champs facultatifs. La multiplication des index peut toutefois ralentir la création ou la mise à jour.

Tu peux probablement utiliser des types d'index simples, pas besoin de te compliquer trop...

Bonne continuation !
14/10/2009, 09h29
elsuket

Bonjour,

Citation:

Quel type lui donner (CLUSTERED ? autre ?) ?

Vous avez déjà une clé primaire sur votre table, et la création d'une clé primaire entraîne la création d'un index cluster.
Tout autre index qui sera créé sur cette table sera donc de type non-cluster, et vous n'êtes pas obligé de le préciser dans l'instruction de création de l'index (mot clé NONCLUSTERED non obligatoire ;))

Il est dommage que vous ayez fait le choix de clés primaires de type UNIQUEIDENTIFIER dans plusieurs tables : en effet une valeur de ce type occupe 16 octets. Pour une centaine de milliers de lignes, une valeur de clé de type INT (4 octets) aurait amplement suffit, en configurant la colonne pour qu'elle ait la propriété de compteur. Cela fait que votre clé est large, donc que vous pouvez stocker moins de lignes dans les pages des niveaux intermédiaires de votre index ...

La pose des indexes passe par l'étude des plans de requête (CTRL+L sous SSMS) ;)

@++ ;)
14/10/2009, 10h01
Arnard

Quand je parlais de centaines de milliers, j'aurais en fiat du indiquer que je pourrais allègrement dépasser le million d'enregistrements. J'ai actuellement 3M de lignes, mais on ne peut pas considérer ça comme réaliste vu que c'est en phase de test. Mais je préfère envisager le cas de figure plutôt que ça pète à la tête...

Pour les bâtiments, j'en ai plusieurs centaines.

Donc je peux tout rassembler sur un seul index ? Même mes champs varchar que sont libelle et commentaire ?

je vais jeter un œil aux plans de requête.

======

Edit : après exécution du plan de requête, il ressort que le filtrage coûte 8% et le Clustered Index 92% dans le cas nominal (good ? bad ?).

Dans le cas d'un Union d'une même table mais de bases différentes, une table à faible volume représente 4% de temps Cluster Index Scan, la table à fort volume représente 87% de temps de scan.

======

Edit 2 : j'ai tenté de créer un index sur les champs suivants :
Date, CodeFamille, CodeDomaine, Batiment.

Or, le champ Batiment est un varchar de 1024, et j'ai le message suivant à la création :

Citation:

Avertissement ! La longueur de clé maximale est 900 octets. L'index 'IndexHistorique' a une longueur maximale de 1065 octets. Dans certains cas, la combinaison de valeurs élevées entraînera l'échec de l'opération d'insertion/mise à jour

Comment dois-je procéder ? Je peux demander à réduire la taille de ce champ, sinon un index ne portant pas sur ce champ (alors que ce champ est automatiquement dans la recherche) reste-t-il pertinent ?

Merci :)
14/10/2009, 13h40
martinus45

Il ne faut pas mettre des champs de libellés très longs dans un index, car, même si SQL-Server l'accepte, cela va être très "coûteux" et les performances ne seront pas vraiment meilleures avec un index que sans.

Si tu as vraiment des problèmes de performance, je crois qu'il va falloir ré-étudier la structure de la base qui ne me semble pas très logique. ESt-ce qu'il ne devrait pas Ce n'est pas logique de
14/10/2009, 13h55
martinus45

Il ne faut pas mettre un champ très long dans un index normal car même si SQL Server l'accepte, il sera gros et coûteux à mettre à jour et pas très efficace. Tu pourrais dans certains cas envisager un index FULLTEXT mais je ne crois pas que c'est opportun dans ton cas.

Cela m'étonnerait que tes utilisateurs puissent avoir besoin de 1024 caractères (1/2 page) pour identifier un de leurs bâtiments. S'il te disent que oui, demande leur un exemple, ça va les calmer :-)

Il me semble que si tu as des problèmes de performance et que tu n'as que quelques centaines de bâtiments différents, il faut mieux faire générer une table de ces différents bâtiments et ne garder dans la table historique qu'une référence vers eux, pas 1024 caractères. Cela sera certainement le plus efficace et le plus économique.

Cela vaut peut-être pour d'autres champs de ta table.
14/10/2009, 21h26
Arnard

Les tables d'hsitoriques se veulent indépendantes du reste du système, tout doit y être consolidé !

J'ai discuté de revoir la taille du champ, mais sans résultat pour l'instant.
1024 caractères représentent une demi-page ? Ca, ça peut être un argument utile :) (Pour info cela peut représenter une arborescence genre batA etage5 piece 3)

Merci pour ces infos.

Actuellement je suis parti sur une base d'un index par élément de recherche (un index sur le domaine, un sur la famille, un sur la date et un sur le type, mon approche est-elle juste ?

Merci :)
16/10/2009, 10h26
elsuket
Citation:

1024 caractères représentent une demi-page ?

Cela dépend de la résolution des écrans, dont on ne peut évidemment pas se servir ...
Pour avoir une idée, exécutez :
Code:

1 2 SELECT MAX(LEN(maColonneVARCHAR1024)) FROM maTable WITH (NOLOCK)
Si vous êtes amené à faire des recherches avec un LIKE comme vous l'avez écrit dans votre premier post, il est évident que l'indexation de texte intégral, comme vous le propose martinus45 vous sera d'un grand secours, que ce soit en maintenance, en performances, ou en implémentation.

En revanche comme on vous l'a précisé avant, n'indexez pas cette colonne qui peut atteindre 1024 caractères : outre le fait que cela vous coûtera très cher en espace disque et en performances, vous n'en tirerez parti que lorsque vous effectuerez des recherches du type LIKE 'toto%' (jamais pour les cas LIKE '%toto' ou LIKE '%toto%').
Supposons que votre table contienne 3 millions de ligne, et que la moyenne de la longueur des valeurs de la colonne de description des bâtiments est de 256. Une page peut stocker 8060 o, sa taille est de 8192 o.

Voyons comment va être organisé un index qui aurait pour clé seulement cette colonne :

- 1er niveau de l'index : 3 000 000 * 256 o = 768 000 000 / 8060 = 95286 pages
- 2e niveau de l'index : 95286 * 256 o = 24393216 / 8060 = 3027 pages
- 3e niveau de l'index : 3027 * 256 o = 774912 / 8060 = 97 pages
- 4e niveau de l'index : 97 * 256 o = 24832 / 8060 = 4 pages
- 5e niveau de l'index : 1 page.

cela fait donc 98 415 pages <=> 806 215 680 o ~ 768 Mo pour un index qui ne sera que très faiblement utilisé, et qui se fragmentera très vite si vous ajoutez, modifiez ou supprimez des lignes.
Cela vous oblige en plus à lire au moins 6 pages (5 d'index, une de la table) pour obtenir une ligne de données : 6 * 8192 ~ 48 Mo ... pas terrible comme rendement :mouarf:

Citation:

Actuellement je suis parti sur une base d'un index par élément de recherche (un index sur le domaine, un sur la famille, un sur la date et un sur le type, mon approche est-elle juste ?

domaine et famille sont de type UNIQUEIDENTIFIER, et cela va vous coûter cher à indexer et à maintenir, puisque les valeurs de ces colonnes ne ne suivent pas, et que comparer des valeurs de type UNIQUEIDENTIFIER coûte beaucoup plus cher que de comparer des entiers.

Une valeur de type UNIQUEIDENTIFIER coûte 16 octets.
Je doute que vous ayez plus de 256 domaines => type TINYINT : 1 octet. Si c'est le cas : type SMALLINT : 2 octets.
Idem pour les familles, et si vous dépassez : type INT : 4 octets.
Refaites les calculs comme je vous l'ai montré pour votre colonne de type VARCHAR(1024), en comparant ce que vous obtenez pour UNIQUEIDENTIFIER et pour des types entiers : y'a pas photo.

D'autre part ajouter un index sur chaque colonne n'est probablement pas la meilleure solution si les clauses WHERE de vos requêtes filtrent sur plusieurs colonnes (ce qui à mon avis sera le cas ...) : vous allez au mieux devoir utiliser plusieurs indexes au lieu d'un seul, multi-colonnes.
Mais ce n'est pas une raison pour mettre plusieurs colonnes comme clé de votre indexe : comme je vous l'ai montré, plus la clé de votre indexe est petite, plus vous pouvez stocker de valeurs de clé dans les niveaux intermédiaires de votre index, moins vous aurez de niveaux à traverser, plus votre index sera efficace.
La pose d'indexes sur une table ne peut se faire que par l'étude des plans des requêtes qui utiliseront cette table.

@++ ;)
17/10/2009, 19h01
SQLpro

Une clef déclarée en UNIQUEIDENTIFIER est la pire des stupidité, et malheureusement on la trouve souvent car elle vient de gens qui ont une pensée objet (OID = UNIQUEIDENTIFIER) et pas une vision BD !
A lire sur les horreurs de l'UNIQUEIDENTIFIER :
http://blog.developpez.com/sqlpro/p7...nt-le-verdict/

A +