Probléme d'index avec des tables de référence

Bonjour,

J'ai du reprendre un projet contenant une base de données SQL Server 2012.

Mon probleme porte sur une table qui contient environ 40 millions de ligne.
Chaque est constitué d'une vingtaine de calcul (real) et d'un champs date, et de 5 champs varchar d'une longueur de 30 environ.

Pour alléger le poids de la table j'ai créer 5 tables de références contenant les chaines de caractères et j'ai créer un ID (smallint) permettant de faire le lien.

Mon problème se porte sur les index.

1 - Lorsque je réalise ma requête en filtrant avec les noms littéraux les table de références sont utilisé et les temps de traitement sont infiniment long.
2 - Lorsque je fait la même requête en utilisant directement des ID correspondant la requête est quasi instantanée.

Il y a un index couvrant sur la grosse table avec la date et les 5 colonnes en tant que clefs.

la requete est un group by portant sur une ou plusieurs des clefs de l'index. La clause where porte sur les 5 colonnes.

Merci de votre aide

Ce serait plus simple d'avoir la requête mais...
Les colonnes de libellé dans les tables de référence sont-elles indexées ? Si elles sont utilisées dans le filtre, ce serait une piste à explorer.

Il suffit de faire la requête à l'envers :

• préjointure des tables de référence pour filtrage
• GROUP BY sur les id et SELECT avec id uniquement
• Jointure finale des id pour avoir les libellés es tables de ref.

A +

Je ne comprends pas trop la solution.

Voici comme suggéré un exemple proche du problème:

3 tables :

table GROSSE_TABLE_AVEC_25MILLIONS_LIGNES_20_COLONNES_CALCUL
int: ID, ID_PERSONNE,ID_OBJET
varchar: CAT
real: CALCUL_01 ... CALCUL_20


PETITE_TABLE_NOM_OBJET_100_LIGNES
int: ID
varchar: NOM, TYPE, DESCRIPTION

PETITE_TABLE_NOM_PERSONNE_100_LIGNES
int: ID
varchar: NOM, TYPE, CHAMP A
int: CHAMP B, CHAMP C

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

select CAT, NOM_OBJET, NOM_PERSONNE, avg(CALCUL_01), ..., avg(CALCUL_02) from (     ----SOUS REQUETE   select b.NOM_OBJET, c.NOM_PERSONNES, a.CAT, a.CALCUL_01, a.CALCUL_01, ..., a.CALCUL_20 from GROSSE_TABLE_AVEC_25MILLIONS_LIGNES_20_COLONNES_CALCUL a left join PETITE_TABLE_NOM_OBJET_100_LIGNES b on a.ID_OBJET = b.ID left join PETITE_TABLE_NOM_PERSONNE_100_LIGNES c on a.ID_PERSONNE = c.ID where b.TYPE_OBJET='test' and c.TYPE_PERSONNE='test' and a.TYPE_CALCUL='test' and a.CALCUL_06 > 15.6   ----------------   )a group by CAT, NOM_OBJET, NOM_PERSONNE

---

Pour la sous requete:

la requête filtre sur les la grosse table principale et sur les 2 tables de références contenant les caractéristiques des OBJETS et PERSONNES
1 - Si je met tout dans une grosse table la requête prend 10 secondes, mais c'est a l'encontre de l'idée de normalisation
2 - Si je recherche tous les ID Objet et Personne puis filtre a la main en utilisant les ID (sans jointure donc), la requête prend 2 secondes
3 - Si j'utilise la requête si dessus il faut environ 40 secondes

Pour la requête globale, on peut avoir a grouper par CAT, CAT/NOM_OBJET, CAT/NOM_PERSONNE, CAT/NOM_OBJET/NOM_PERSONNE.

Comment disposeriez vous les indices, refactoriserai les tables ou ferais la requête ?

Merci :D

Bonjour

Comme indiqué par al1_24, il serait utile d'indexer les clefs étrangères.

Pour votre requête, l'index suivant serait un bon début.

Code:

---

1 2	(ID_OBJET , TYPE_PERSONNE, TYPE_CALCUL) INCLUDE (CALCUL_06)

---

Par ailleurs, pourquoi faire des jointures externes ? comme vous filtrez ensuite, cela revient à faire une jointure interne.
Vous pourriez aussi éviter la sous requete. Elle ne devrait pas avoir d'impact sur les perfs, mais rend la requete moins lisible et surtout, si vous l'enleviez (et que vous ne faites que des jointures internes), vous pourriez créer une vue indexée à partir de cette requete. Si votre requête est appelée souvent, ça peut être la meilleure solution.