Extraction qui prend beaucoup de temps

**khadija30** · 02/05/2013, 13h49

Bonjour tout le monde, je dois optimiser les chargements ETL( SSIS); mon problème c'est que dans quelques packages l'extraction des données source depuis la base staging prend beaucoup de temps, la requête sql au niveau du OLE DB SOURCE contient beaucoup de jointures et selectionne beaucoup de champs en plus les données volumineuses. j'ai essayé de diviser la requête en deux et puis faire un merge join mais ça n'a pas abouti à de bon résultats. avez vous d'autres propositions
Merci d'avance

**Jinroh77** · 02/05/2013, 13h59

Je ne suis pas sûr que le travail soit au niveau de l'ETL, mais plutôt d'un point de vue fonctionnel.
Toutes les données sont-elles utiles ? N'est-il pas possible de calculer ou détecter un delta sur le volume de données ?

**khadija30** · 02/05/2013, 15h22

les champs sélectionnés par la requête sont tous utilisés sauf 2 ou 3 champs que je peux supprimés, les données à extraire sont moins d'un million de lignes.
je travaille sur un projet décisionnel qui existe déjà et qui répond à tous les besoins fonctionnels le problème réside au niveau des temps de réponse et je ne sais pas quoi faire j'ai déjà fait un diagnostic sur les composants du package qui prend du temps à s'executer (11 min, 30 min ...). les packages ne contiennent pas de composants bloquants ( juste le nécessaire) et on fait appel juste pour les colonnes dont on besoin au niveau du datamart ou datawarehouse, ce que j'ai remarqué c'est que au niveau de la requête qu'il y a probleme; quand j'execute seulement la requete via SQL SERVER Management Studio çà prend presque 2h sachant que le package qui contient la requête prend 11 min
Merciiiii

**arnaudvoisin** · 02/05/2013, 17h09

Bonjour khadija30,

quand j'execute seulement la requete via SQL SERVER Management Studio çà prend presque 2h sachant que le package qui contient la requête prend 11 min

Quand j'entends ça je me dis que tu as peut-être un soucis d'espace disque sur ton disque système.

Pour en avoir le coeur net, insert le résultat dans une table temporaire en utilisant le genre de code ci-dessous :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT
-- ta requête
 
INTO #results
 
FROM -- table
-- etc ...

Combien de temps d'exécution cela prend-il ?

**khadija30** · 03/05/2013, 10h58

Bonjour, merci pour vos réponses.
j'ai essayé votre proposition arnaudvoisin: ( select ... into .... from ....),la requête a pris 6:35:36 ( 6 h c'est énorme !!!) avec 717640 rows affectés.Pour être plus claire, la structure des packages est la suivante: j'ai un OLE DB SOURCE puis il y'a un traitement pour la gestion des doublons; ce traitement est assuré par Multicast, Aggregate, Sort et Merge join.et puis j'ai un Conditionnal Split et en fin OLE DB DESTINATION. OLE DB SOURCE extrait les données depuis ma base staging et ole db destination insert dans le DATAMART. les packages à optimiser ont les temps d'execution suivant: 5 min, 8 min, 11 min, 18 min,27 min. au debut j'ai cru que les composants bloquants aggregate, sort et merge join sont la cause mais quand j'ai exécuté la requête d'extraction, elle a pris du temps alors j'ai conclu que le problème réside tout d'abord au niveau du OLE DB SOURCE. la requête de chaque package s'étale sur 4 à 6 pages et contient plusieurs jointure et select plusieurs colonnes.
concernant la gestion des doublons, j'ai essayé de remplacer le traitement existant par un autre à l'aide du composant Lookup. Mais j'ai pas réussi puisque je veux supprimer les doublons à partir des données sélectionnées par OLE DB SOURCE et lookup me donne la possibilité de sélectionner à partir de la base ce qui m'oblige à recopier la requête du ole db source dans lookup; mais vu le probleme de la requête j'ai laissé tomber la gestion des doublons puisque c'est prioritaire.
Merci d'avance pour vos propositions et vos suggestions.

**d.joubert** · 03/05/2013, 11h35

Copie la requête ici, parce que le problème est à ce niveau-là

Une fois cette requête optimisée, il serait peut-être utile de gérer les doublons directement dedans, car l'utilisation des sort/aggregate dans SSIS est tout sauf performant

**khadija30** · 03/05/2013, 12h48

Pour des raisons de longueur de requête ( environ 6 pages de code) et de confidentialité j'ai essayé de donner la structure générale de la 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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
Select 
--11 fields from tbl1
Tbl1.field1 As field1,
….
.
.
Tbl1.field11 As field11,
 
CASE WHEN (dbo.tbl1.field11 = 'J') 
                      THEN N'Batch' ELSE dbo.tbl1.field11 END AS creation_oprid, CONVERT(date, tbl1.date1) 
                      AS creation_date, CONVERT(date, tbl1.date2) AS update_date, 
                      CASE WHEN (dbo.tbl1.field12 = 'J') 
                      THEN N'Batch' ELSE dbo.tbl1.field12 END AS update_oprid, 
tbl1.id, 
 
--3 fields from tbl2
 
Tbl2.field1 As field1,
….
.
.
Tbl2.field3 As field3,
 
Tbl3.field1 As field1,
Tbl4.field1 As field1,
Tbl3_2.field1 As field1,
 
--30 fields from tbl5
Tbl5.field1 As field1,
….
.
.
Tbl5.field30 As field30,
 
--7 fields from tbl6
 
Tbl6.field1 As field1,
….
.
.
Tbl6.field7 As field7,
 
--5 fields from tbl7
 
Tbl7.field1 As field1,
….
.
.
Tbl8.field5 As field5,
 
 
--7 fields from tbl8
 
Tbl8.field1 As field1,
….
.
.
Tbl8.field7 As field7,
 
--
 
---6 conditions from the same table and the same field and 6 conditions with AND
 
  CASE WHEN tbl9.field1 = N'Value1' THEN tbl9.field2 END AS XXX1,
  CASE WHEN tbl9.field1 = N'Value1' THEN tbl9.field2 AND
  Tbl9.field3= 'SAMEValue' THEN N'Batch' ELSE Tbl9.field3 END AS YYY1,
 
.
.
  CASE WHEN tbl9.field1 = N'Value6' THEN tbl9.field2 END AS XXX6,
  CASE WHEN tbl9.field1 = N'Value6' THEN tbl9.field2 AND
  Tbl9.field3= 'SAMEValue' THEN N'Batch' ELSE Tbl9.field3 END AS YYY6,
 
Tbl3_4.field1 As field1,
Dbo.tbl10.field1 As field1,
Dbo.tbl10_1.field1 As field1,
 
 
CASE WHEN dbo.tbl8.TYPE_ID = 9 THEN N'Y' WHEN dbo. dbo.tbl8.TYPE_ID = 10 THEN N'N' END AS
                       flag, 
CASE WHEN dbo.tbl11.field_ID IS NOT NULL 
                      THEN ('O_' + dbo.tbl11.field_ID) END AS field_ID, 
CASE WHEN dbo.tbl12.field_ID IS NOT NULL THEN ('L_' + dbo.tbl12.field_ID) END AS field_ID
                    ,ROW_NUMBER() OVER (ORDER BY id,dbo.tbl8.FIELDID,dbo.tbl8.Field2ID) AS RowNumber
 
 
 
FROM         
dbo.tbl1 
INNER JOIN dbo.tbl8 
	ON tbl1.field_ID = Tbl8.contact_field_ID 
LEFT OUTER JOIN dbo.tbl12 
	ON dbo.TBL1.PERSON_ID = dbo.tbl12.FIELD_ID_CONTACT 
LEFT OUTER JOIN dbo.PS_RSF_OPP_CONTACT 
	ON dbo.TBL1.PERSON_ID = dbo.tbl11.FIELD_ID_CONTACT 
LEFT OUTER JOIN dbo.tbl3 AS tbl3_4 
	ON TBL1.COMM_METHOD = tbl3_4.FIELDVALUE AND (FIELDNAME = N'VALUE') AND (EFF_STATUS = N'A') 
LEFT OUTER JOIN dbo.tbl9 
	ON TBL1.FIELD_ID = tbl9.FIELD_ID 
LEFT OUTER JOIN dbo.tbl5 
	ON TBL1.FIELD_ID = tbl5.FIELD_ID 
LEFT OUTER JOIN dbo.tbl7 
	ON TBL1.FIELD_ID = tbl7.FIELD_ID 
LEFT OUTER JOIN tbl6
	ON TBL1.FIELD_ID = tbl6.FIELD_ID 
LEFT OUTER JOIN dbo.tbl10 AS tbl10_1 
RIGHT OUTER JOIN dbo.tbl13 
	ON tbl10_1.CM_PURPOSE_TYPE_ID = dbo.tbl13.CM_PURPOSE_TYPE_ID 
	ON TBL1.FIELD_ID = tbl13.FIELD_ID 
LEFT OUTER JOIN dbo.tbl10 
RIGHT OUTER JOIN dbo.tbl14 
	ON dbo.tbl10.CM_PURPOSE_TYPE_ID = dbo.tbl14.CM_PURPOSE_TYPE_ID 
	ON TBL1.FIELD_ID = tbl14.FIELD_ID 
LEFT OUTER JOIN dbo.tbl3 AS tbl3_2 
	ON TBL1.PRSN_TYPE = tbl3_2.FIELDVALUE AND (tbl3_2.FIELDNAME = N'PRSN_TYPE') 
	AND (tbl3_2.EFF_STATUS = N'A') 
LEFT OUTER JOIN dbo.tbl4 
	ON TBL1.FIELD_CD = TBL4.FIELD_CD 
LEFT OUTER JOIN dbo.tbl3 AS tbl3 
	ON TBL1.Field_CD = tbl3.FIELDVALUE AND (tbl3.FIELDNAME = N' LANG') 
	AND (tbl3.EFF_STATUS = N'A') 
LEFT OUTER JOIN dbo.tbl2 AS tbl2 
	ON TBL1.FIELD_ID = tbl2.FIELD_ID AND (tbl2.PRIMARY_IND = 'Y')
ORDER BY dbo.TBL1.PERSON_ID

Mercii

**d.joubert** · 03/05/2013, 14h11

Elle s'exécute rapidement si tu supprimes le ORDER BY à la fin et le champ en ROW_NUMBER() ?

**khadija30** · 06/05/2013, 11h30

Bonjour Jaubert,
j'ai supprimer le ORDER BY et le ROW_Number; la requête a pris 2:28 min.
le Row_Number() est utilisé dans toutes les requêtes pour des fin de dédoublonnages. y a-t-il une méthode de dédoublonnage autre que celle ci ( row-number et les composant: multicast, aggregate, sort et merge join) et autre que le Sort avec l'option supprimer les doublons ?

**arnaudvoisin** · 07/05/2013, 10h54

Bonjour khadija,

Tu veux dire que tu utilises un row_number dans le but de dédoublonner ?

Code :

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

ROW_NUMBER() OVER (ORDER BY id,dbo.tbl8.FIELDID,dbo.tbl8.Field2ID) AS RowNumber

Je ne suis pas persuadé que 3 colonnes soient nécessaires dans le ORDER BY pour la séquence de numérotation.

A moins que tu souhaitais dédoublonner sur une partition composé des 3 ID ?

Dans ce cas tu devrais plutôt écrire :

Code :

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

ROW_NUMBER() OVER (PARTITION BY id,dbo.tbl8.FIELDID,dbo.tbl8.Field2ID ORDER BY id) AS RowNumber

Après fonctionnellement si tu as des doublons alors que ton champ Id est la clé primaire de la table dbo.tbl1 je pense que tu devrais vérifier tes jointures, il doit y en avoir une (ou plusieurs) qui dupliquent tes données.

Maintenant si tu souhaites réellement faire le déboulonnage tu pourrais imaginer de passer par une CTE et de faire les jointures sur le résultat de la CTE :

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
;with ma_cte as (
	SELECT
 
	 La_liste_de_mes_champs....
 
	 ,ROW_NUMBER() OVER (PARTITION BY id,dbo.tbl8.FIELDID,dbo.tbl8.Field2ID ORDER BY id) AS RowVersion
 
	FROM         
	dbo.tbl1 
	INNER JOIN dbo.tbl8 
		ON tbl1.field_ID = Tbl8.contact_field_ID 
)
 
SELECT
	La_liste_de_mes_champs....
FROM ma_cte
 
 LEFT JOIN ma_table1
 ON etc ....
 
 WHERE RowVersion=1

Il est difficile de t'aider sans en connaitre plus sur le domaine fonctionnel. On peut juste t'aider à aller au bout de tes choix d'implémentation.

Ce qu'il faut que tu gardes en tête c'est que pour réaliser une extraction sur moins d'un million de lignes sans avoir à réaliser de traitements (calculs ou aggrégations) complexes cela doit prendre à peine plus d'une poignée de secondes.

**khadija30** · 07/05/2013, 16h24

Merci Arnaud pour ton aide.
ma tâche est d'améliorer les temps d'exécution des packages sans toucher au fonctionnel; je travaille sur un projet décisionnel qui existe déjà et j'essaie de comprendre la fonction de chaque package à partir des traitements faits au sein du package.
Par exemple le package que j'essaie d'optimiser maintenant extrait les données depuis plusieurs tables de la base de staging, élimine les doublons et puis alimente une table du datamart. au début j'ai un ole db source qui fait l'extraction à l'aide d'une requête sql;le RowNumber est définie dans cette requête de la manière suivante:

Code :

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

 ROW_NUMBER() OVER (ORDER BY tb1.colonne1, tb2.colonne2,tb2.colonne3) AS RowNumber

et puis j'ai un multicast qui me donne en sortie deux flux identiques , on laisse un flux intact et on fait un traitement pour dédoublonner sur l'autre: à l'aide du composant AGGREGATE on fait group by --> colonne1, group by --> colonne2, group by --> colonne3 et Minimum --> RowNumber
après on fait un SORT et à la fin MERGE JOIN ( inner join) pour lier ce flux avec l'autre qui est intact et comme çà on aura plus de doublons. Après on a un ole db destination.

**arnaudvoisin** · 07/05/2013, 18h41

Je ne pense pas que tu doives faire une telle machine à gaz dans un dataflow SSIS pour dédoublonner. Les Sort et Aggregate sont moins performant dans le pipeline de SSIS que dans la tempdb du moteur.

Un conseil, si tu dois gérer le dédoublonnage d'un dataset, essaie de le traiter dès l'extraction en SQL et ce sans passer par un dataflow complexe, difficile à maintenir et peu performant. Dans le message précédent je t'ai parlé de l'utilisation d'une CTE pour numéroter tes lignes par partition afin de les versionner et de ne prendre qu'une occurrence d'entre elles.

Tu peux aussi utiliser d'autres méthodes que tu trouveras bien expliqué par Fredéric Brouard, ici. Donc pour résumer, je serais toi je reconsidérerais totalement cette tâche et ne me contenterais pas simplement d'un petit tuning.

**khadija30** · 14/05/2013, 11h51

Bonjour Arnaud.
Merci bcp pour ton aide et tes propositions, j'ai utilisé une CTE et éffectivement cela m'a permis de gérer le dédoublonnage au niveau de la requête, de ce fait j'ai éliminé les composants bloquants de mon package( sort, aggregate, merge join) et la chose la plus importante c que la nouvelle requête a pris moins de temps

Merciiiii