Discussion :

[Monstros Velu]

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 :

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
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 ?

[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

@++

[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

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

[Monstros Velu]

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 :

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

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.

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