Placement des index

**Monstros Velu** · 19/08/2009, 11h34

Bonjour,

j'ai des jointures qui reviennent souvent, et un lenteur excessive depuis la mise en place de ses jointures (mais on ne choisi pas les fichiers qu'on reçoit...)

Les 2 jointures en question sont les suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
from
	#tw_BSI_ToProcess tpr
		inner join #tw_duplicatedBSI dup
		on tpr.EMP_EAFBIRTHYEAR = dup.EMP_EAFBIRTHYEAR
			and tpr.EMP_LASTNAME = dup.EMP_LASTNAME
			and tpr.EMP_FIRSTNAME = dup.EMP_FIRSTNAME
			and tpr.EMP_COMMENT = dup.EMP_COMMENT
			and tpr.EMP_CUSTOMERCODE = dup.EMP_CUSTOMERCODE
			and tpr.CUS_CODECLIENT = dup.CUS_CODECLIENT

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
from
	#tw_BSI_ToProcess tpr
		inner join EMP_EMPLOYEE empParent
		on (tpr.CUS_IDPARENT = empParent.CUS_IDPARENT 
			and tpr.emp_customercode = empParent.emp_customercode)
 
			inner join EMP_EMPLOYEE empChild
			on (empChild.EMP_IDPARENT = empParent.emp_id
				and YEAR(empChild.EMP_BIRTHDATE)%100 = tpr.EMP_EAFBIRTHYEAR
				and empChild.EMP_LASTNAME = tpr.EMP_LASTNAME
				and empChild.EMP_FIRSTNAME = tpr.EMP_FIRSTNAME
				and empChild.EMP_COMMENT = 'customer code : ' + tpr.EMP_CUSTOMERCODE
				and empChild.EMP_CUSTOMERCODE = '')
			inner join CUS_CUSTOMER cusChild	
			on (empChild.CUS_IDPARENT = cusChild.CUS_ID
				and cusChild.CUS_CODECLIENT = tpr.CUS_CODECLIENT	)

La 2ème jointure est utilisée 4 fois, la 1ère 1 seul fois, dans la requête d'import (donc respectivement 800 et 200fois pour une fichier de 200 000lignes, puisqu'on traite des lots de 1000lignes... D'où mon besoin d'optimisation)

Je ne peux pas toucher aux tables EMP_EMPLOYEE et CUS_CUSTOMER. Par contre, les tables tpr et dup peuvent surement bénéficier d'index. Vaut-il mieux créer un index pour chaque colonne ? Un index avec tous les colonnes ? Un mix de l'ensemble ?
N'ayant pas d'expérience en ce domaine, je fais appel à la votre. Merci d'avance !

**Cybher** · 19/08/2009, 11h43

salut,

il n'y a pas déjà moyen de simplifier la 1ère requête?
il faut vraiment vérifier toutes ces conditions?
car l'idéal serait d'indexer toutes les colonnes utilisées pour les jointures mais cela va commencer à faire beaucoup

**Monstros Velu** · 19/08/2009, 11h46

Non, je dois vraiment vérifier toutes ces colonnes. Il n'y a pas de "clef" dans le fichier d'import, du coup, je dois retrouver les existants en appliquant cette règle...

**pacmann** · 19/08/2009, 12h09

Salut !

Je n'ai pas forcément tout compris...
Une des tables de ta requête correspond aux données à intégrer ?
Si ce sont les jointures qui sont longues et exécutées beaucoup de fois, peux-tu envisager de remplir une table temporaire avec ?

Et petite question bonus : ton SGBD ?

**CinePhil** · 19/08/2009, 14h06

Un coup d'oeil chez SQLPro pourrait sans doute t'aider à placer tes index.

Regarde notamment la notion d'index couvrant.

**Monstros Velu** · 19/08/2009, 14h58

J'avais regardé, mais ça me semblait énorme de "couvrir" autant de colonnes.

J'ai créé 'index suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
create index #tw_duplicatedBSI_global on #tw_duplicatedBSI (
				EMP_CUSTOMERCODE, 
				EMP_EAFBIRTHYEAR, 
				EMP_LASTNAME, 
				EMP_FIRSTNAME, 
				CUS_CODECLIENT)

J'ai du enlever EMP_COMMENT, car la colonne est un varchar(4000), et l'index est limité à 900 caratères. J'ai mis EMP_CUSTOMER en 1er, car il est utilisé sur d'autres requêtes : autant avoir a possibilité d'utiliser l'index pour celles-ci.

Je suis en train de tester pour vérifier que l'index fonctionne... J'en saurai plus demain, après avoir passé 2 ou 3 fichiers ^^ Si quelqu'un a d'autres suggestions, je suis tout ouïe ^^

**Yanika_bzh** · 20/08/2009, 09h41

D'un premier coup d'oeil, je pense que vous pouvez indexer
EMP_LASTNAME
car cette condition de jointure semble assez discriminante pour vos resultats.
CUS_CODECLIENT puisqu'elle semble etre la condition de jointure de vos tables

Un index sur empChild.EMP_BIRTHDATE ne doit pas servir a grand chose car sa condition de jointure est appliquée avec un double operation (conversion et modulo) donc interet limité.

Faites plusieurs indexes sur une colonne plutot qu'un index sur plusieurs colonnes.

Voila, vite fait ce que je peux dire.

Bon courage

**Monstros Velu** · 20/08/2009, 10h09

Pour EMP_BIRTHDATE, j'ai fait le calcul dans ma table temporaire, du coup, j'indexe sur EMP_EAFBIRTHYEAR.

Pourquoi devoir créer plusieurs index plutôt qu'un index sur plusieurs colonnes ? Sql Server ne pourrait-il pas utiliser ça comme une optimisation ?

**CinePhil** · 20/08/2009, 10h26

Si je comprends bien ce qu'explique SQLPro dans son tutoriel déjà cité, un index multi colonne est plus performant qu'un index par colonne si les colonnes qui le composent sont appelées ensemble dans les conditions de jointure ou les condition de restriction (clause WHERE).

Exemples...

1) Si vous cherchez vos clients systématiquement par leur nom + prénom, faites un index (nom, prenom).

2) Si votre système réagit à la saisie du nom et vous propose la liste de tous les clients portant ce nom puis qu'ensuite la recherche peut être affinée par le prénom, l'index multi-colonnes ne sera peut-être pas utile. Ca dépend sans doute aussi de la fréquence d'utilisation car il ne faut pas oublier qu'un index est aussi coûteux en espace et en temps de mise à jour des données.

Testez aussi plusieurs configurations des index multi-colonnes. Il y aura a priori plus de noms que de prénoms dans la table donc il faut mettre le nom avant le prénom.
Quant à la date de naissance, il y a 365 ou 366 jours par an, multipliés par l'âge de vos clients, potentiellement entre 20 et 60 ans a priori donc ça peut aussi être très discriminant par rapport au prénom.

Par contre, si vous souhaitez la fête de vos clients, n'oubliez pas de créer quand même un index sur le prénom seul.

C'est grosso modo l'idée de ces histoires d'index multi-colonnes et couvrant.

**pacmann** · 20/08/2009, 11h01

Que représente la table #tw_BSI_ToProcess ?
Que représente la table #tw_duplicatedBSI ?

Quelles sont les volumétries des tables ?

**Monstros Velu** · 20/08/2009, 11h07

#tw_BSI_ToProcess contient 1000 lignes. Les imports sont placés dans une table BSI_TOIMPORT lors de la phase d'insertion. Dans la phase de validation, on prends 1000 lignes de BSI_TOIMPORT pour les mettre dans #tw_BSI_ToProcess, travailler dessus, puis updater BSI_TOIMPORT

#tw_duplicatedBSI contient les doublons de #tw_BSI_ToProcess trouvés dans BSI_TOIMPORT, donc entre 0 et 1000 lignes

EMP_EMPLOYEE, qui est la table de destination finale des imports, contient un peu plus de 9 millions de lignes, et CUS_CUSTOMER environ 4000

**pacmann** · 20/08/2009, 11h44

1) Si j'ai bien compris, les deux tables # sont vidées et remplies à chaque itération. Les requêtes (jointures) dont tu parles traitent ces tables dans leur intégralité. Je ne suis pas certain qu'un index apporte énormément...

2) Sur la deuxième jointure, le plus important est d'avoir les indexes sur EMP_EMPLOYEE ! (même si j'ai cru comprendre que tu ne peux pas la modifier)
Cette table fait 9 millions de lignes : c'est elle qui doit être accédée par index !
=> Ce ne sont pas les indexes sur les petites tables # qui te permettront d'éviter le scan entier de cette table.

De plus, si tu es obligé de lire la table EMP en entier, les index sur # ne serviront à rien puisqu'il sera probablement bien plus rentable de faire la lecture entière de ces tables et de hasher la clef de jointure.

3) Comme le dit Cinephile, l'index couvrant sert à ne pas avoir à accéder à la table, mais uniquement à l'index.
Donc si au choix :
- tu ne peux mettre toute les colonnes de jointure parce que l'index est limité à 900
- tu as besoin d'autres colonnes dans le select
Dans ces cas il ne te sert à rien d'avoir un index couvrant.

Après cela, quand il ne s'agit plus d'économiser les accès table, l'important dans l'index est sa "sélectivité", soit sa capacité à cibler un nombre restreint de lignes. Et là, je suis d'accord avec Yanika sur le fait que le birth_date ne sert pas énormément... puisque NOM + PRENOM devrait cibler déjà très fort.

4) Voici mon interprétation qu'il faudra confirmer / infirmer pour la deuxième jointure :
- Elle te renvoie assez peu de lignes (genre 1000 * nombre d'EMP enfants par parent)
- Le résultat est utilisé plusieurs fois dans l'itération
- C'est surtout la jointure qui est lourde, vu que derrière il y a la table 9 millions

=> Ne serait-il pas rentable de stocker le résultat (ces quelques milliers de lignes) dans une table temporaire et l'utiliser sur le reste du traitement ?
Question subsidiaire :
As-tu des index sur EMP_EMPLOYEE.cus_idparent ?
(je suppose que sur emp_id oui, vu que ça semble être la pk)

En gros, le plan d'exécution qu'on s'imagine bien est :
Lire #tw_BSI_ToProcess en entier (1000 lignes)
Pour chaque ligne, lire par index EMP_EMPLOYEE (il faut voir à quel point CUS_IDPARENT et / ou emp_customercode filtre bien EMP_EMPLOYEE)
Pour chaque ligne, retrouver le customer associé. Comme cus_id_parent = cus_parent et cus_id a l'air d'être l'id de customer, il semble mieux de faire la jointure sur customer directemetn sur empParent avec de récupérer les enfants
Pour chaque ligne, lire EMP_EMPLOYEE qui sont les enfants (avec un index sur EMP_IDPARENT, et en partant du principe qu'un parent a un nombre assez limité d'enfants)

Encore une remarque : dans le cas où vous n'avez pas les bons index sur EMP_EMPLOYEE, et que vous ne pouvez en créer de nouveaux, il serait peut être plus judicieux de ne pas découper en lots de 1000. Car tant qu'à devoir se taper les 9 millions de lignes, il vaudrait mieux ne le faire qu'une fois (et non 200) afin de faire une seule fois la groooosse jointure.

**Monstros Velu** · 20/08/2009, 12h13

Merci beaucoup à tous pour toutes ces réponses depuis le début du mail ^^

1) effectivement, la table temporaire est supprimée et recréée

C'est rassurant : il y a déjà plein d'index sur EMP_EMPLOYEE : voici leur liste :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
nonclustered located on PRIMARY	CUS_IDCLIENT
nonclustered located on PRIMARY	CUS_IDPARENT
nonclustered located on PRIMARY	EMP_CUSTOMERCODE, CUS_IDCLIENT
nonclustered located on PRIMARY	EMP_CUSTOMERCODE
nonclustered located on PRIMARY	EMP_IDPARENT
nonclustered located on PRIMARY	ibp_code     -- c'est le code du fichier d'import
nonclustered located on PRIMARY	EMP_LASTNAME
nonclustered located on PRIMARY	EMP_LOGIN
nonclustered located on PRIMARY	EMP_CODESUBSIDIARY
nonclustered, unique, primary key located on PRIMARY	EMP_ID

Je pense qu'on trouve tout notre bonheur là-dedans ^^

Il y a 1,5 million de EMP_IDPARENT différents et 6 millions de EMP_CUSTOMERCODE différents.

Eventuellement, je peux ajouter des index sur EMP_EMPLOYEE. Je suis d'ailleurs étonné que le EMP_FIRSTNAME ne soit pas indexé, alors qu'il y a des recherches effectuées dessus.

**pacmann** · 20/08/2009, 13h37

Ben dis-moi, c'est pas mal tout ça !

La première jointure sur CUS_IDPARENT et emp_customercode
Quelle est la cardinalité de cette relation entre #tw_BSI_ToProcess et EMP_EMPLOYEE ?
Proche du 1 - 1 ?
Tu as un index sur CUS_IDPARENT : combien d'employés sont renvoyés pour un CUS_IDPARENT donné ?
=> Si il y en a trop et donc que emp_customercode est très discriminant, il faut envisager de d'ajouter la colonne emp_customercode à l'index CUS_IDPARENT.

Si on joint le résultat avec CUS_CUSTOMER, je pense que ça ne pose aucun problème (que soit en utilisant l'index primaire de cus_customer ou non !)

En joignant une deuxième fois EMPLOYEE sur emp_idparent, on lit 4000 lignes par index, donc tout va bien encore une fois...

Du coup, j'ai envie de te demander :
Vous avez des problèmes de perfs ?
Si oui, avez-vous localisé quelle partie du traitement est lente ?
Si encore une fois oui, est-ce que ta jointure prend bien les index de manière satifsfaisante ?
Et est-tu bien d'accord avec moi qu'à priori des index les tables # n'apportenteraient probablement pas grand chose de plus ?

**Yanika_bzh** · 20/08/2009, 14h38

postez vos plans d'execution afin d'avoir une meilleure vision de vos requetes

Bon courage

**Monstros Velu** · 20/08/2009, 14h44

Envoyé par pacmann

Ben dis-moi, c'est pas mal tout ça !

La première jointure sur CUS_IDPARENT et emp_customercode
Quelle est la cardinalité de cette relation entre #tw_BSI_ToProcess et EMP_EMPLOYEE ?
Proche du 1 - 1 ?

C'est un import : Il y a des EMP qui existent déjà et des EMP qui n'existent pas pour un BSI. Et à l'inverse, pour 1 EMP, il peut y avoir plusieurs BSI. Mais ils sont rapidement tagués comme doublon et exclus dans les tests suivants.

Tu as un index sur CUS_IDPARENT : combien d'employés sont renvoyés pour un CUS_IDPARENT donné ?
=> Si il y en a trop et donc que emp_customercode est très discriminant, il faut envisager de d'ajouter la colonne emp_customercode à l'index CUS_IDPARENT.

Pour CUS_IDPARENT, j'ai entre 1 et 1 millions d'EMP, avec une moyenne à 5000.
Pour 1 emp_customercode, j'ai entre 1 et 3 millions d'EMP, avec une moyenne de 1 : les 3 millions correspondent au code vide '', et il y a en fait quasiment 1 code pour chaque EMP. Par contre, dans le cas de cet importe, il y en a forcément plusieurs, c'est à dire en général entre 2 et 5.
Emp_customercode est quant à lui "presque unique".

Si on joint le résultat avec CUS_CUSTOMER, je pense que ça ne pose aucun problème (que soit en utilisant l'index primaire de cus_customer ou non !)

En joignant une deuxième fois EMPLOYEE sur emp_idparent, on lit 4000 lignes par index, donc tout va bien encore une fois...

Du coup, j'ai envie de te demander :
Vous avez des problèmes de perfs ?

oui ! C'est lancé le soir, il faut que ce soit fini le matin... ^^

Si oui, avez-vous localisé quelle partie du traitement est lente ?

Non. C'est une procédure stockée qui fait beaucoup de choses. Et en particulier, le temps d'exécution dépends énormément des données à l'intérieur. Bien que traitant par bloc de 1000, les 1000 peuvent passer en 10s ou en 5 minutes. Je n'ai pas encore réussi à trouver où est le problème, j'espérais que les index le résoudrai.

Si encore une fois oui, est-ce que ta jointure prend bien les index de manière satifsfaisante ?

Je vais vérifier ça.

Et est-tu bien d'accord avec moi qu'à priori des index les tables # n'apportenteraient probablement pas grand chose de plus ?

J'ai testé, comme je l'avais dit, et ça n'apporte effectivement rien de plus.

**pacmann** · 20/08/2009, 15h25

Ok !

Est-ce que le emp_customercode de la table # peut valoir '' ?
Si oui, c'est un gros problème...
Si no, construire un index sur emp_customercode dans EMP est une très bonne idée !
(l'index sur ID_CUSCLIENT est quasiment inutile ici, vu qu'il ne permet pas de filtrer assez la relation # <=> EMP parent)

Je n'ai pas encore réussi à trouver où est le problème, j'espérais que les index le résoudrai.

C'est notre grand problème ici... depuis le début, on balance des solutions au pif alors qu'on n'a pas la moindre idée de l'origine du problème

Est-ce que SQL Server propose des outils pour diagnostiquer l'exécution ?
(Sous Oracle, la trace SQL aurait à peu près réglé le problème...)

Déjà, ça serait bien de savoir si ce sont vraiment les phases de validation qui moulinent (enfin celles qui utilisent les jointures qu'on étudie ici)...

J'ai testé, comme je l'avais dit, et ça n'apporte effectivement rien de plus.

On peut donc abandonner les idées d'index couvrants ou non sur les tables # ! (enfin pour le moment du moins...)

**Monstros Velu** · 20/08/2009, 15h49

Envoyé par pacmann

Ok !

Est-ce que le emp_customercode de la table # peut valoir '' ?
Si oui, c'est un gros problème...

Non, jamais ! C'est la 1ère vérification effectuée ;o)

Si no, construire un index sur emp_customercode dans EMP est une très bonne idée !
(l'index sur ID_CUSCLIENT est quasiment inutile ici, vu qu'il ne permet pas de filtrer assez la relation # <=> EMP parent)

Il y a déjà un index sur emp_customercode dans EMP

C'est notre grand problème ici... depuis le début, on balance des solutions au pif alors qu'on n'a pas la moindre idée de l'origine du problème

Au départ, j'étais convaincu que c'étai tun problème d'index, donc j'ai cherché à le smettre en place. C'est autre chose, alors je donne les pièces que je trouve ;o)

Est-ce que SQL Server propose des outils pour diagnostiquer l'exécution ?
(Sous Oracle, la trace SQL aurait à peu près réglé le problème...)

Oui, il y a ça, je suis en train de "nettoyer" la procédure stockée pour ne garder que le type d'import en question. Le soucis, c'est que sans les données qui vont avec, c'est pas significatif... Je vais devoir simuler l'import... donc pas avant demain, le temps de tout faire tourner...

Déjà, ça serait bien de savoir si ce sont vraiment les phases de validation qui moulinent (enfin celles qui utilisent les jointures qu'on étudie ici)...

On peut donc abandonner les idées d'index couvrants ou non sur les tables # ! (enfin pour le moment du moins...)

**Monstros Velu** · 21/08/2009, 12h25

Une première analyse révèle que la lecture d'un clustered index prend 31% du temps de ma requête... La table fait à peine plus de 2000lignes. Cet index est lu 3 fois, dans les diverses requêtes, ce qui signifie que le reste ne prends quasiment pas de temps. Ca me semble assez irréel. Est-il possible que le plan de requête donné par le SQL Management Studio soit faux ?

**pacmann** · 21/08/2009, 13h41

Je ne connais pas bien SQL Server, mais je pense qu'il vaut mieux partir du principe qu'il ne se plante pas.
En lecture de l'index, le fait qu'il soit clustered ne change rien vu que ça impacte juste l'ordre physique des lignes dans la table.

Par contre, le fait qu'une lecture d'index puisse prendre 31% du temps n'est pas choquant en soi.
Si tu fais :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
SELECT a.*
FROM a JOIN b ON a.col = b.LaColIndexee

Et que b est la table détail de a par exemple, il se peut que tu passes plus de temps à lire l'index de b que la table a !

Maintenant, pour interprèter le résultat, c'était quel index ?
Et quand tu as fait cette mesure, ça prenait 10 secondes ou 5 minutes ?
Le but étant de trouver un exemple qui est particulièrement long, puis de comprendre pourquoi...

Placement des index

Développement SQL Server

Discussions similaires

Partager

Partager