Discussion :

[olivtone]

Hello a Tous

je me prends la tete pour baisser les perfomances sur un traitement SSIS qui prend 18 minutes pour 13 millions de lignes

J'ai de mon coté :

- Désactiver l'index Clustered sur la table (meme en le supprimant complement c'est le meme temps)
- Paritionner la table meme ca donne le meme temps voir pire (je perds 1 minute)
- Activer les options SSIS (lot par ligne en essayant 100 000 ou plus, et TABLELOCK ) meme temps...


Le SSIS fait un INSERT BULK en masse sur une table qui fait 138 colonnes...

Si vous avez des avis je suis preneur

merci a vous

[escartefigue]

je me prends la tete pour baisser les perfomances sur un traitement SSIS qui prend 18 minutes pour 13 millions de lignes

Vous voulez augmenter les perfs et donc abaisser les temps CPU et elapsed où alors vous êtes maso

J'ai de mon coté :
- Désactiver l'index Clustered sur la table (meme en le supprimant complement c'est le meme temps)
- Paritionner la table meme ca donne le meme temps voir pire (je perds 1 minute)
- Activer les options SSIS (lot par ligne en essayant 100 000 ou plus, et TABLELOCK ) meme temps...

Si vous virez l'index vos dégradez les perfs pour de l'insertion, et la reconstruction de l'index post chargement prendra du temps aussi.
Or c'est bien le temps total de l'opération qu'il faut mesurer
Si vous chargez une table vide, conservez l'index cluster, triez vos données à charger selon cet index et éclatez le par critère de partitionnement, ainsi vous pourrez paralléliser le chargement des différentes partitions. Autre avantage, le tri / éclatement se fait sans perturber la dispo de la table, et post opération, votre table est réorganisée.

[olivtone]

J'arrive pas trop a piger desole...

si je laisse l'index clustered je passe a 28 minutes en insertion....

Comment je peux faire un TRI d'insertion par bu BULK INSERT en masse?

si tu as un exemple je suis preneur

merci de ton retour

[olivtone]

Issu du livre blanc Microsoft

https://technet.microsoft.com/en-us/...=sql.100).aspx

Before you load data into an empty table, it is always a good idea to drop or disable all indexes on the table. After the load is done, re-create or re-enable the indexes.

[escartefigue]

Issu du livre blanc Microsoft

https://technet.microsoft.com/en-us/...=sql.100).aspx

Before you load data into an empty table, it is always a good idea to drop or disable all indexes on the table. After the load is done, re-create or re-enable the indexes.

mais aussi : https://technet.microsoft.com/fr-fr/...ql.105%29.aspx

[bvesan]

Bonjour,
Histoire de connaître le temps minimal d'insertion, il serait intéressant d'insérer ces données dans une table de même structure (mêmes colonnes) mais sans indexe ou contrainte et vide, avec la base en mode de recouvrement SIMPLE ou BULK-LOGED pour profiter du FAST BULK INSERT, et avec l'option TABLOCKX qui va bien (comme spécifié dans les deux liens des messages précédents).

Si cette durée minimale est déjà considérée comme trop grande, c'est uniquement sur la durée du transfert réseau (si l'import n'est pas local) et/ou de l'écriture des données (et journaux transactionnels) qu'il faut jouer.
Si elle est acceptable, alors il faut vérifier que l'insertion sur la "vraie" table profite bien du Fast Bulk Insert, et comprendre pourquoi si ça n'est pas le cas.
Si l'option du partitionnement est envisageable, il y a peut-être moyen d'importer les données dans une table vide, d'y ajouter les contraintes et indexes de la "vraie" table, puis d'utiliser le mécanisme d'échange des partitions pour que les données se retrouvent dans la bonne table plus rapidement

Vous parlez de 13 milions de lignes, mais en terme de volumétrie, on parle de quelques Mo, quelques Go, To ?

[olivtone]

ok je vais essayer BVESAN

Question : Dans SSIS peut on mettre l'option WITH ORDER pour faire l'insertion selon l'index clustered sur la table cible?

Car apparement on peut le faire qu'a partir d'un fichier plat...et non d'une autre BDD

[SQLpro]

Hello a Tous

je me prends la tete pour baisser les perfomances sur un traitement SSIS qui prend 18 minutes pour 13 millions de lignes

J'ai de mon coté :

- Désactiver l'index Clustered sur la table (meme en le supprimant complement c'est le meme temps)
- Paritionner la table meme ca donne le meme temps voir pire (je perds 1 minute)
- Activer les options SSIS (lot par ligne en essayant 100 000 ou plus, et TABLELOCK ) meme temps...

Le SSIS fait un INSERT BULK en masse sur une table qui fait 138 colonnes...

Si vous avez des avis je suis preneur

merci a vous

Décrivez sous forme DDL la table.

Supprimez un index clustered prends généralement plus de temps que de le laisser. Encore faut-il saoir comment il est construit... d'ou question 1

Pour accélérer votre processus, vous devez n'avoir AUCUNE opération de croissance des fichiers de la base pendant l'insertion. C'est ce qui prend le plus de temps
Pour ce faire, dimensionnez correctement les fichiers de données et journaux... Est-te vous en mode de journalisation simple ?

Sans toutes les informations de contexte, difficile de vous répondre....

Votre table n'est visiblement pas normalisée... 138 colonnes c'est une table obèse... À lire : http://blog.developpez.com/sqlpro/p1...mances_petites

A +

[olivtone]

Merci frederic de ton retour

la BDD est déjà en mode simple jai meme activé pour Test le TRACEFLAG 610 mais cela n'a rien changé

je suis d'accord que la table est plus qu'obese...

La table a la base n'avait pas d'index du tout a la base, je l'avais ajouté pour test.

Voici le script de la table ci dessous

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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
 
 
 
CREATE TABLE [dbo].[toto](
	[E_EXERCICE] [char](3) NULL,
	[E_JOURNAL] [char](3) NULL,
	[E_DATECOMPTABLE] [datetime] NULL,
	[E_NUMEROPIECE] [int] NULL,
	[E_NUMLIGNE] [int] NULL,
	[E_GENERAL] [varchar](17) NULL,
	[E_AUXILIAIRE] [varchar](17) NULL,
	[E_DEBIT] [numeric](19, 4) NULL,
	[E_CREDIT] [numeric](19, 4) NULL,
	[E_REFINTERNE] [varchar](35) NULL,
	[E_LIBELLE] [varchar](35) NULL,
	[E_NATUREPIECE] [char](3) NULL,
	[E_QUALIFPIECE] [char](3) NULL,
	[E_TYPEMVT] [char](3) NULL,
	[E_VALIDE] [char](1) NULL,
	[E_ETAT] [varchar](10) NULL,
	[E_REFEXTERNE] [varchar](35) NULL,
	[E_DATEREFEXTERNE] [datetime] NULL,
	[E_UTILISATEUR] [char](3) NULL,
	[E_CONTROLEUR] [char](3) NULL,
	[E_DATECREATION] [datetime] NULL,
	[E_DATEMODIF] [datetime] NULL,
	[E_SOCIETE] [char](3) NULL,
	[E_ETABLISSEMENT] [char](3) NULL,
	[E_VISION] [char](3) NULL,
	[E_REFLIBRE] [varchar](35) NULL,
	[E_AFFAIRE] [varchar](17) NULL,
	[E_TVAENCAISSEMENT] [char](1) NULL,
	[E_REGIMETVA] [char](3) NULL,
	[E_TVA] [char](3) NULL,
	[E_TPF] [char](3) NULL,
	[E_NUMEROIMMO] [int] NULL,
	[E_BUDGET] [varchar](17) NULL,
	[E_CONTREPARTIEGEN] [varchar](17) NULL,
	[E_CONTREPARTIEAUX] [varchar](17) NULL,
	[E_COUVERTURE] [numeric](19, 4) NULL,
	[E_LETTRAGE] [varchar](5) NULL,
	[E_LETTRAGEDEV] [char](1) NULL,
	[E_DATEPAQUETMAX] [datetime] NULL,
	[E_DATEPAQUETMIN] [datetime] NULL,
	[E_REFPOINTAGE] [varchar](17) NULL,
	[E_DATEPOINTAGE] [datetime] NULL,
	[E_SUIVDEC] [char](3) NULL,
	[E_MODEPAIE] [char](3) NULL,
	[E_DATEECHEANCE] [datetime] NULL,
	[E_DATERELANCE] [datetime] NULL,
	[E_NIVEAURELANCE] [int] NULL,
	[E_TIERSPAYEUR] [varchar](17) NULL,
	[E_DEVISE] [char](3) NULL,
	[E_DEBITDEV] [numeric](19, 4) NULL,
	[E_CREDITDEV] [numeric](19, 4) NULL,
	[E_TAUXDEV] [numeric](15, 9) NULL,
	[E_DATETAUXDEV] [datetime] NULL,
	[E_CONTROLE] [char](1) NULL,
	[E_MULTIPAIEMENT] [varchar](35) NULL,
	[E_QTE1] [numeric](19, 4) NULL,
	[E_QTE2] [numeric](19, 4) NULL,
	[E_QUALIFQTE1] [char](3) NULL,
	[E_QUALIFQTE2] [char](3) NULL,
	[E_ECRANOUVEAU] [char](3) NULL,
	[E_ECHEENC1] [numeric](19, 4) NULL,
	[E_ECHEENC2] [numeric](19, 4) NULL,
	[E_DATEVALEUR] [datetime] NULL,
	[E_RIB] [varchar](70) NULL,
	[E_REFRELEVE] [varchar](35) NULL,
	[E_COUVERTUREDEV] [numeric](19, 4) NULL,
	[E_ETATLETTRAGE] [char](3) NULL,
	[E_NUMPIECEINTERNE] [varchar](35) NULL,
	[E_NOMLOT] [varchar](17) NULL,
	[E_ENCAISSEMENT] [char](3) NULL,
	[E_TRACE] [varchar](70) NULL,
	[E_TYPEANOUVEAU] [char](3) NULL,
	[E_NUMECHE] [int] NULL,
	[E_ANA] [char](1) NULL,
	[E_ECHE] [char](1) NULL,
	[E_EMETTEURTVA] [char](1) NULL,
	[E_ORIGINEPAIEMENT] [datetime] NULL,
	[E_FLAGECR] [char](3) NULL,
	[E_CONFIDENTIEL] [char](1) NULL,
	[E_CONTROLETVA] [char](3) NULL,
	[E_CREERPAR] [char](3) NULL,
	[E_EXPORTE] [char](3) NULL,
	[E_TRESOLETTRE] [char](1) NULL,
	[E_LIBRETEXTE0] [varchar](35) NULL,
	[E_LIBRETEXTE1] [varchar](35) NULL,
	[E_LIBRETEXTE2] [varchar](35) NULL,
	[E_LIBRETEXTE3] [varchar](35) NULL,
	[E_LIBRETEXTE4] [varchar](35) NULL,
	[E_LIBRETEXTE5] [varchar](35) NULL,
	[E_LIBRETEXTE6] [varchar](35) NULL,
	[E_LIBRETEXTE7] [varchar](35) NULL,
	[E_LIBRETEXTE8] [varchar](35) NULL,
	[E_LIBRETEXTE9] [varchar](35) NULL,
	[E_TABLE0] [varchar](17) NULL,
	[E_TABLE1] [varchar](17) NULL,
	[E_LIBREDATE] [datetime] NULL,
	[E_LIBREBOOL0] [char](1) NULL,
	[E_LIBREBOOL1] [char](1) NULL,
	[E_CONSO] [char](3) NULL,
	[E_LIBREMONTANT0] [numeric](19, 4) NULL,
	[E_LIBREMONTANT1] [numeric](19, 4) NULL,
	[E_LIBREMONTANT2] [numeric](19, 4) NULL,
	[E_LIBREMONTANT3] [numeric](19, 4) NULL,
	[E_REFLETTRAGE] [varchar](35) NULL,
	[E_TABLE2] [varchar](17) NULL,
	[E_TABLE3] [varchar](17) NULL,
	[E_BANQUEPREVI] [varchar](17) NULL,
	[E_NATURETRESO] [varchar](17) NULL,
	[E_QUALIFORIGINE] [char](3) NULL,
	[E_CFONBOK] [char](1) NULL,
	[E_ECHEENC3] [numeric](19, 4) NULL,
	[E_ECHEENC4] [numeric](19, 4) NULL,
	[E_ECHEDEBIT] [numeric](19, 4) NULL,
	[E_EDITEETATTVA] [char](1) NULL,
	[E_COTATION] [numeric](15, 9) NULL,
	[E_NUMORDRE] [int] NULL,
	[E_MODESAISIE] [char](3) NULL,
	[E_EQUILIBRE] [char](1) NULL,
	[E_AVOIRRBT] [char](1) NULL,
	[E_PIECETP] [varchar](35) NULL,
	[E_CODEACCEPT] [char](3) NULL,
	[E_PERIODE] [int] NULL,
	[E_SEMAINE] [int] NULL,
	[E_ETATREVISION] [char](3) NULL,
	[E_IO] [char](3) NULL,
	[E_PAQUETREVISION] [int] NULL,
	[E_REFREVISION] [int] NULL,
	[E_NUMGROUPEECR] [int] NULL,
	[E_IMMO] [varchar](17) NULL,
	[E_NUMTRAITECHQ] [varchar](17) NULL,
	[E_NUMENCADECA] [varchar](17) NULL,
	[E_NUMCFONB] [varchar](17) NULL,
	[E_SAISIMP] [numeric](19, 4) NULL,
	[E_REFGESCOM] [varchar](35) NULL,
	[E_REFPAIE] [varchar](35) NULL,
	[E_BLOCNOTE] [text] NULL,
	[E_DOCID] [int] NULL,
	[E_TRESOSYNCHRO] [char](3) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO

[SQLpro]

Dimensionne large ton JT

Remplace tout tes VARCHAR par du CHAR

Remplace le text par du VARCHAR(max)

Met la table en "large value type out of row"
=> sp_tableoption

Regarde si ça arrange les choses

Enfin, dans l'insert agit par bloc de 64 Ko
BULK INSERT ... KILOBYTES_PER_BATCH = 64

Tu es sur quelle version de SQL Server ?

A +

[olivtone]

ok merci je vais essayer ca et je te fais un retour des demain

merci encore

[olivtone]

meme temps apres toutes ces modifications...

Le serveur est un SQL 2008 R2

Si je mets en place le partionnement cela ne devrait pas t il ameliorer les perfomances concernant l'insertion ?

merci