Positionnement d'index pour optimiser une requête

Version imprimable

Bonjour,

Dans le cadre d'un ETL, je remplis des tables temporaires qui servent à mettre à jour la table sur laquelle se base le cube. Pour la table bénéficiaire, la requête prends 9h, et c'est un peu lent...

je souhaite optimiser une requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
		update
			dbo.BEN_BENEFICIARY
		set
			TO_UPDATE = 1,
			FIRST_NAME = isnull(tmp.FIRST_NAME, '?'), 
			LAST_NAME = isnull(tmp.LAST_NAME, '?'), 
			ZIP_CODE = isnull(tmp.ZIP_CODE, dbo.Without()), 
			NEWSLETTER = dbo.BoolToString(isnull(tmp.NEWSLETTER, 0)), 
			ISVIP = dbo.BoolToString(isnull(tmp.ISVIP, 0)), 
			ACTIVE = dbo.BoolToString(isnull(tmp.ACTIVE, 0)), 
			BIRTH_DATE = dbo.ShortDate(tmp.BIRTH_DATE), 
			CIVILITY = isnull(tmp.CIVILITY, '?'),
			BO_CREATION_DATE = dbo.ShortDate(tmp.BO_CREATION_DATE), 
			BO_UPDATE_DATE = dbo.ShortDate(tmp.BO_UPDATE_DATE),
			CONTRACT_STARTING_DATE =  dbo.ShortDate(tmp.CONTRACT_STARTING_DATE),
			CONTRACT_END_DATE =  dbo.ShortDate(tmp.CONTRACT_END_DATE),
			PRO_EXTERNAL_ID = isnull(tmp.PRO_EXTERNAL_ID, dbo.DefaultProfileExtId()), 
			CUS_EXTERNAL_ID = isnull(tmp.CUS_EXTERNAL_ID, dbo.DefaultCustomerExtId()),
			DEPARTEMENT = dbo.Departement(tmp.ZIP_CODE),
			REGION = dbo.Region(tmp.ZIP_CODE),
			UPDATE_DATE = @currentDate
		from
			dbo.BEN_BENEFICIARY img
				inner join dbo.BEN_BENEFICIARY_TEMP tmp
				on (img.EXTERNAL_ID = tmp.EXTERNAL_ID)
		where 
			img.FIRST_NAME <> isnull(tmp.FIRST_NAME, '?') 
			or img.LAST_NAME <> isnull(tmp.LAST_NAME, '?') 
			or img.ZIP_CODE <> isnull(tmp.ZIP_CODE, dbo.Without()) 
			or img.NEWSLETTER <> dbo.BoolToString(isnull(tmp.NEWSLETTER, 0)) 
			or img.ISVIP <> dbo.BoolToString(isnull(tmp.ISVIP, 0))
			or img.ACTIVE <> dbo.BoolToString(isnull(tmp.ACTIVE, 0)) 
			or isnull(img.BIRTH_DATE, @currentDate)  <> isnull(dbo.ShortDate(tmp.BIRTH_DATE), @currentDate)
			or img.CIVILITY <> isnull(tmp.CIVILITY, '?') 
			or img.BO_CREATION_DATE <> dbo.ShortDate(tmp.BO_CREATION_DATE) 
			or img.BO_UPDATE_DATE <> dbo.ShortDate(tmp.BO_UPDATE_DATE) 
			or isnull(img.CONTRACT_STARTING_DATE, @currentDate)  <> isnull(dbo.ShortDate(tmp.CONTRACT_STARTING_DATE), @currentDate)
			or isnull(img.CONTRACT_END_DATE, @currentDate)  <> isnull(dbo.ShortDate(tmp.CONTRACT_END_DATE), @currentDate)
			or img.PRO_EXTERNAL_ID <> isnull(tmp.PRO_EXTERNAL_ID, dbo.DefaultProfileExtId()) 
			or img.CUS_EXTERNAL_ID <> isnull(tmp.CUS_EXTERNAL_ID, dbo.DefaultCustomerExtId())

Je pense qu'un index sur EXTERNAL_ID dans les 2 tables pourrait être utile...
Devrai-je rajouter un index sur ma table BEN_BENEFICIARY couvrant toutes les colonnes utilisées dans le where ?
Avez-vous des idées ?

10/02/2010, 13h45
elsuket

Bonjour,

Effectivement, il faut toujours que les colonnes qui servent aux jointures soient indexées, sans cela les jointures ne peuvent pas se faire rapidement.
En conséquence, les colonnes directement candidates à une indexation sont les colonnes de clé étrangère.

Mais votre requête est surtout lente du fait de votre prédicat de recherche qui n'est pas SARGable.

Dès lors, le moteur de base de données n'a pas d'autre choix que de parcourir toute la table (par table scan ou clustered index scan), ce qui bien sûr peut s'avérer très long et très contre-performant si votre table possède un nombre de lignes important.

Vous pouvez voir cela en surlignant votre requête si elle n'est pas seule dans le batch et CTRL+L sous SSMS pour afficher le plan de requête (qui se lit de droite à gauche).
En dessous de chaque opérateur se trouve le pourcentage que chacun deux coûte relativement à la requête.
Je ne serai pas étonné de trouver un pourcentage élevé pour votre requête sur un table scan ou clustered index scan ;)

@++ ;)
10/02/2010, 14h40
Monstros Velu

Il y a presque 10 millions de lignes dans cette table...

J'ai 25% sur un Clustered Index Update
12% sur Table Spool
16% sur Distinct Sort
13% sur indexseek
9% sur Key Lookup
24% sur sort et 1% sur clustered Index Scan

Le Inner join utilise 0%

J'avais repéré que la requête n'était pas sargable, mais je ne vois pas trop comment l'améliorer. J'enlève les isnull(...) en mettant des requêtes préalable sur la table BEN_BENEFICIARY_TEMP
10/02/2010, 15h13
SQLpro

Ce n'est pas du tout de cette façon qu'il faut travailler l'alimentation d'un datawarehouse, parce que en effet le coût du <> est gigantesque et inoptimisable.

Ce qu'il faut faire c'est implémenter un outil spécifique pour traquer le changement d'état des données et n'envoyer que le delta au DW.

Pour cela vous pouvez utiliser des déclencheurs ou mieux un système comme change data capture, particulièrement dédié à ce type de job (tracking de changement asynchrone)

A +

Mais nous n'envoyons que le delta. Chaque jour, un fichier est généré sur la base de production avec les données modifiées ou inserrées dans la dernière période de temps (5fois par semaine), et c'est ce fichier qui est mis dans les table _TEMP.

Ensuite, les données sont retraités (transformation des guid en int pour rendre le cube plus rapide, etc...) et intégrée dans une table "finale" contenant toutes les données.

J'ai créé un index
Code:

1 2 3 4 5 6 7 8 9 CREATE UNIQUE NONCLUSTERED INDEX [IX_BEN_EXTERNAL_ID] ON [dbo].[BEN_BENEFICIARY] ( [EXTERNAL_ID] ASC ) INCLUDE(FIRST_NAME, LAST_NAME, ZIP_CODE, NEWSLETTER, ISVIP, ACTIVE, CIVILITY, BO_UPDATE_DATE, BO_CREATION_DATE, PRO_EXTERNAL_ID, CUS_EXTERNAL_ID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
J'espère que ça améliorera les choses.

10/02/2010, 23h57
Monstros Velu

Est-ce que supprimer les conditions et mettre à jour que les données ait changé ou pas, améliorerait les choses ?

Est-ce que passer sur SQL 2008 et utiliser "merge" sur la colonne EXTERNAL_ID améliorerait les choses ?
11/02/2010, 12h46
Waldar

Combien avez-vous de lignes dans la table dbo.BEN_BENEFICIARY ?
11/02/2010, 12h51
Monstros Velu

Presque 10 millions...
11/02/2010, 13h26
Waldar

Et dans la TMP ?
11/02/2010, 14h24
Monstros Velu

Ça dépends des jours : Entre 10 000 et 400 000
11/02/2010, 15h55
Waldar

Vous pouvez à ce moment là faire les suppressions dans dbo.BEN_BENEFICIARY, puis insérer tout le contenu des tables TMP.

Analysez bien contraintes de votre table, il ne faudrait pas dégrader la qualité de votre base.

Si vous avez beaucoup d'index (hors clef primaire), ça peut valloir le coup de les dropper / recréer.
17/02/2010, 16h28
Sergejack

Citation:

Envoyé par Monstros Velu

Est-ce que supprimer les conditions et mettre à jour que les données ait changé ou pas, améliorerait les choses ?

Très probablement.
À essayer.

Citation:

Envoyé par Monstros Velu

Est-ce que passer sur SQL 2008 et utiliser "merge" sur la colonne EXTERNAL_ID améliorerait les choses ?

J'en doute.
Merge te serait bénéfique si tu fesais des update ET des insert.
17/02/2010, 16h41
Monstros Velu

En réalité, il y a une deuxième partie dans le transfert de données qui fait des inserts...
17/02/2010, 16h48
Sergejack

Citation:

Envoyé par Monstros Velu

En réalité, il y a une deuxième partie dans le transfert de données qui fait des inserts...

Alors, (si tu envisages vraiment de passer en 2008) emploie un merge qui soit l'union (UNION ALL pour être plus rapide) de tes sources actuelles pour l'insert et pour l'update.
17/02/2010, 16h53
Monstros Velu

Le passage en 2008 est un des objectifs de l'année pour les admins servers, donc ça se fera.

Et pour le merge : Toutes les données à mettre à jour ou insérer sont dans la même table : En enlevant les vérifications et en mettant à jour même ce qui n'a pas changé, je n'aurai plus qu'à vérifier l'EXTERNAL_ID.

Je testerai...