Discussion :

[Arnard]

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

[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 !

[elsuket]

Bonjour,

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)

@++

[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 :

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

[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

[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.

[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