Ecriture de données en masse

Version imprimable

Bonjour,

Je ne sais pas bien si c'est ici que je dois poster ma question mais on déplacera si nécessaires.

Je précise que je continue mes recherches en parallèles de ce poste bien sur !

Voici mon problème,

J'ai un programme qui va lire des informations dans une base Oracle sur un site distant et qui va ensuite écrire toutes les lignes trouvées dans la base Mysql de mon serveur Local.

Le soucis est que j'ai par exemple 37.000 lignes à écrire ... et que ça prend un temps certains ...

Pour écrire mes informations j'utilise une programme que j'aie développé en VB.net et dans lequel je charge un DataReader avec les informations trouvées et lance une boucle Do While pour ecrire les informations.

Existe t-il une solution plus rapide ou moins usine à gaz pour rapatrier les informations ???

Voici le code en question.

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
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
 Dim CmdOracle As New OracleCommand(TextSql, DBOracle1)
                DrORacle = CmdOracle.ExecuteReader
 
                If DrORacle.HasRows Then
 
                    Do While DrORacle.Read
 
                        Enseigne = DrORacle.GetString(0).ToString
                        Saison = DrORacle.GetString(1).ToString
                        Nom = DrORacle.GetString(2).ToString
                        NumCommande = DrORacle.GetString(3).ToString
                        Buyer = DrORacle.GetString(4).ToString
                        Famille = DrORacle.GetString(5).ToString
                        Coloris = DrORacle.GetString(6).ToString
 
                        If Not DrORacle.IsDBNull(7) Then
                            LibColoris = Strings.Replace(DrORacle.GetString(7).ToString, "'", "''")
                        Else
                            LibColoris = "N/A"
                        End If
 
                        If Not DrORacle.IsDBNull(8) Then
                            CodeGrilleTaille = DrORacle.GetInt32(8)
                        Else
                            CodeGrilleTaille = 0
                        End If
 
                        If Not DrORacle.IsDBNull(9) Then
                            LibGrilleTaille = DrORacle.GetString(9).ToString
                        Else
                            LibGrilleTaille = "N/A"
                        End If
 
                        Fournisseur = Strings.Replace(DrORacle.GetString(10).ToString, "'", "''")
                        OTB = Strings.Replace(DrORacle.GetString(11).ToString, "'", "''")
                        Themes = Strings.Replace(DrORacle.GetString(12).ToString, "'", "''")
 
                        If Not DrORacle.IsDBNull(13) Then
                            DistriProd = Strings.Replace(DrORacle.GetString(13).ToString, "'", "''")
                        Else
                            DistriProd = "N/A"
                        End If
 
                        PlieSup = DrORacle.GetString(14).ToString
                        MEPREA = DrORacle.GetString(15).ToString
 
                        Paht = DrORacle.GetDecimal(16)
                        QttCommande = DrORacle.GetInt32(17)
                        QttLivre = DrORacle.GetInt32(18)
                        QttResteALivre = DrORacle.GetInt32(19)
 
                        Dim Jour, Mois, Annee As String
 
                        Jour = "00" & DrORacle.GetOracleDate(20).Day.ToString
                        Mois = "00" & DrORacle.GetOracleDate(20).Month.ToString
                        Annee = DrORacle.GetOracleDate(20).Year.ToString
 
                        DateLivraison = Annee & "-" & Strings.Right(Mois, 2) & "-" & Strings.Right(Jour, 2)
 
                        If Not DrORacle.IsDBNull(21) Then
                            Jour = "00" & DrORacle.GetOracleDate(21).Day.ToString
                            Mois = "00" & DrORacle.GetOracleDate(21).Month.ToString
                            Annee = DrORacle.GetOracleDate(21).Year.ToString
                            DDM = Annee & "-" & Strings.Right(Mois, 2) & "-" & Strings.Right(Jour, 2)
                        Else
                            DDM = "1900-01-01"
                        End If
 
                        If Not DrORacle.IsDBNull(22) Then
                            Jour = "00" & DrORacle.GetOracleDate(22).Day.ToString
                            Mois = "00" & DrORacle.GetOracleDate(22).Month.ToString
                            Annee = DrORacle.GetOracleDate(22).Year.ToString
                            DateDIM = Annee & "-" & Strings.Right(Mois, 2) & "-" & Strings.Right(Jour, 2)
                        Else
                            DateDIM = "2050-12-31"
                        End If
 
                        If Not DrORacle.IsDBNull(23) Then
                            RefFourn = Strings.Replace(DrORacle.GetString(23).ToString, "'", "''")
                        Else
                            RefFourn = "N/A"
                        End If
 
                        If Not DrORacle.IsDBNull(24) Then
                            Stagged = Strings.Replace(DrORacle.GetString(24).ToString, "'", "''")
                        Else
                            Stagged = "N/A"
                        End If
 
                        If Not DrORacle.IsDBNull(25) Then
                            Ironing = Strings.Replace(DrORacle.GetString(25).ToString, "'", "''")
                        Else
                            Ironing = "N/A"
                        End If
 
                        If Not DrORacle.IsDBNull(26) Then
                            OnHanger = Strings.Replace(DrORacle.GetString(26).ToString, "'", "''")
                        Else
                            OnHanger = "N/A"
                        End If
 
                        TextSql = "insert into gat (enseigne, saison, nom, commande, buyer, famille, coloris, libcoloris, codegrilletaille, libgrilletaille, fournisseur, otb, themes, paht, qttcommande, qttlivre, qtereste, datelivraison, ddm, dim, pliesup, meprea, distriprod, reffourn, stagged, ironing, onhanger ) values ('" & Enseigne & "', '" & Saison & "', '" & Nom & "', " & NumCommande & ", '" & Buyer & "', '" & Famille & "', '" & Coloris & "', '" & LibColoris & "', " & CodeGrilleTaille & ", '" & LibGrilleTaille & "', '" & Fournisseur & "', '" & OTB & "', '" & Themes & "', '" & Paht & "', " & QttCommande & ", " & QttLivre & ", " & QttResteALivre & ", '" & DateLivraison & "', '" & DDM & "', '" & DateDIM & "', '" & PlieSup & "', '" & MEPREA & "', '" & DistriProd & "', '" & RefFourn & "', '" & Stagged & "', '" & Ironing & "', '" & OnHanger & "')"
 
                        Dim CmdTraitement As New MySqlCommand(TextSql, DBMysql5)
                        CmdTraitement.ExecuteNonQuery()
 
                    Loop
 
                End If
 
                DrORacle.Close()

Merci d'avance à tous pour vos idées d'amélioration !

28/02/2015, 12h01
ed73170

Bonjour,

Si c'est le temps d'insertion qui est long, tu peux peut-être essayer d'écrire tes lignes dans un fichier texte sur ton serveur puis de le charger dans MySQL par LOAD DATA INFILE.

Voir ici : http://dev.mysql.com/doc/refman/4.1/en/load-data.html
28/02/2015, 14h34
DotNetMatt

37000 lignes c'est pas non plus énorme. Pour accélérer les insertions en masse, on peut :
- Supprimer les index avant l'insertion, puis les recréer une fois que c'est terminé.
- Gérer toutes les instructions insert au sein d'une seule et unique transaction.

Donc tu peux tester ces 2 points et voir si cela améliore les choses ou non...
05/03/2015, 17h40
0n31l

Bonjour DotNetMatt,

Je suis d'accord 37.000 lignes ce n'est pas grand choses mais bon 9 minutes c'est long ...
Je vais réécrire ces informations mise à jours toute les nuit avec un automate que j'ai programmé.

Qu'est-ce que tu entend par gérer toutes les instructions insert au sein d'une seule et unique transaction ?

et supprimer les index, je fait un truncate de la table au début de chaque nouvelle écriture.

Citation:

Envoyé par 0n31l

Je suis d'accord 37.000 lignes ce n'est pas grand choses mais bon 9 minutes c'est long ...

Ah oui, 9 minutes pour 37000 lignes c'est même très très long :aie:

Citation:

Envoyé par 0n31l

Qu'est-ce que tu entend par gérer toutes les instructions insert au sein d'une seule et unique transaction ?

Dans le code que tu as posté plus haut, on voit qu'à chaque fois que tu lis une ligne, tu génères une commande INSERT puis tu l'envoie au serveur (ExecuteNonQuery).

C'est probablement cela qui cause autant de latence. Je pense que tu devrais "bufferiser" tes instructions INSERT, puis toutes les soumettre en même temps au serveur, ce serait beaucoup plus efficace. Voici un exemple de code en me basant sur le tiens :

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
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
 Dim CmdOracle As New OracleCommand(TextSql, DBOracle1)
 Dim inserts As New StringBuilder()
 
DrORacle = CmdOracle.ExecuteReader
 
If DrORacle.HasRows Then
 
	Do While DrORacle.Read
 
		Enseigne = DrORacle.GetString(0).ToString
		Saison = DrORacle.GetString(1).ToString
		Nom = DrORacle.GetString(2).ToString
		NumCommande = DrORacle.GetString(3).ToString
		Buyer = DrORacle.GetString(4).ToString
		Famille = DrORacle.GetString(5).ToString
		Coloris = DrORacle.GetString(6).ToString
 
		If Not DrORacle.IsDBNull(7) Then
			LibColoris = Strings.Replace(DrORacle.GetString(7).ToString, "'", "''")
		Else
			LibColoris = "N/A"
		End If
 
		If Not DrORacle.IsDBNull(8) Then
			CodeGrilleTaille = DrORacle.GetInt32(8)
		Else
			CodeGrilleTaille = 0
		End If
 
		If Not DrORacle.IsDBNull(9) Then
			LibGrilleTaille = DrORacle.GetString(9).ToString
		Else
			LibGrilleTaille = "N/A"
		End If
 
		Fournisseur = Strings.Replace(DrORacle.GetString(10).ToString, "'", "''")
		OTB = Strings.Replace(DrORacle.GetString(11).ToString, "'", "''")
		Themes = Strings.Replace(DrORacle.GetString(12).ToString, "'", "''")
 
		If Not DrORacle.IsDBNull(13) Then
			DistriProd = Strings.Replace(DrORacle.GetString(13).ToString, "'", "''")
		Else
			DistriProd = "N/A"
		End If
 
		PlieSup = DrORacle.GetString(14).ToString
		MEPREA = DrORacle.GetString(15).ToString
 
		Paht = DrORacle.GetDecimal(16)
		QttCommande = DrORacle.GetInt32(17)
		QttLivre = DrORacle.GetInt32(18)
		QttResteALivre = DrORacle.GetInt32(19)
 
		Dim Jour, Mois, Annee As String
 
		Jour = "00" & DrORacle.GetOracleDate(20).Day.ToString
		Mois = "00" & DrORacle.GetOracleDate(20).Month.ToString
		Annee = DrORacle.GetOracleDate(20).Year.ToString
 
		DateLivraison = Annee & "-" & Strings.Right(Mois, 2) & "-" & Strings.Right(Jour, 2)
 
		If Not DrORacle.IsDBNull(21) Then
			Jour = "00" & DrORacle.GetOracleDate(21).Day.ToString
			Mois = "00" & DrORacle.GetOracleDate(21).Month.ToString
			Annee = DrORacle.GetOracleDate(21).Year.ToString
			DDM = Annee & "-" & Strings.Right(Mois, 2) & "-" & Strings.Right(Jour, 2)
		Else
			DDM = "1900-01-01"
		End If
 
		If Not DrORacle.IsDBNull(22) Then
			Jour = "00" & DrORacle.GetOracleDate(22).Day.ToString
			Mois = "00" & DrORacle.GetOracleDate(22).Month.ToString
			Annee = DrORacle.GetOracleDate(22).Year.ToString
			DateDIM = Annee & "-" & Strings.Right(Mois, 2) & "-" & Strings.Right(Jour, 2)
		Else
			DateDIM = "2050-12-31"
		End If
 
		If Not DrORacle.IsDBNull(23) Then
			RefFourn = Strings.Replace(DrORacle.GetString(23).ToString, "'", "''")
		Else
			RefFourn = "N/A"
		End If
 
		If Not DrORacle.IsDBNull(24) Then
			Stagged = Strings.Replace(DrORacle.GetString(24).ToString, "'", "''")
		Else
			Stagged = "N/A"
		End If
 
		If Not DrORacle.IsDBNull(25) Then
			Ironing = Strings.Replace(DrORacle.GetString(25).ToString, "'", "''")
		Else
			Ironing = "N/A"
		End If
 
		If Not DrORacle.IsDBNull(26) Then
			OnHanger = Strings.Replace(DrORacle.GetString(26).ToString, "'", "''")
		Else
			OnHanger = "N/A"
		End If
 
		inserts.Append("insert into gat (enseigne, saison, nom, commande, buyer, famille, coloris, libcoloris, codegrilletaille, libgrilletaille, fournisseur, otb, themes, paht, qttcommande, qttlivre, qtereste, datelivraison, ddm, dim, pliesup, meprea, distriprod, reffourn, stagged, ironing, onhanger ) values ('" & Enseigne & "', '" & Saison & "', '" & Nom & "', " & NumCommande & ", '" & Buyer & "', '" & Famille & "', '" & Coloris & "', '" & LibColoris & "', " & CodeGrilleTaille & ", '" & LibGrilleTaille & "', '" & Fournisseur & "', '" & OTB & "', '" & Themes & "', '" & Paht & "', " & QttCommande & ", " & QttLivre & ", " & QttResteALivre & ", '" & DateLivraison & "', '" & DDM & "', '" & DateDIM & "', '" & PlieSup & "', '" & MEPREA & "', '" & DistriProd & "', '" & RefFourn & "', '" & Stagged & "', '" & Ironing & "', '" & OnHanger & "');" & VbCrLf) ' Note: j'ai ajouté le point virgule à la fin pour séparer les instructions
 
	Loop
 
	TextSql = inserts.ToString()
 
	Dim CmdTraitement As New MySqlCommand(TextSql, DBMysql5)
	CmdTraitement.ExecuteNonQuery()
 
End If
 
DrORacle.Close()

Teste avec ca et dis-nous ce que ca donne (peut-être quelques ajustements de syntaxe à faire car ca fait longtemps que je n'ai pas codé en VB.NET :aie:)

Autre point important, utilise des requêtes paramétrées : .Net, SQL Server, et les requêtes paramétrées (VB.Net) (sous Oracle le principe reste le même, seule la syntaxe change : il ne faut pas utiliser "@" devant un paramètre mais ":").

10/03/2015, 11h55
worm83

Bonjour,

Pour une importante insertion en BDD (plusieurs centaines de méga voir Giga), je ne peux te recommander que de regarder le "BulkInsert".
Il est vrai que 37K enregistrements n'est pas énorme, tu pourrais au lieu d’insérer ligne par ligne, de créer une requête qui concatène l'ensemble des données d'un seul coup comme te le conseil DotNetMatt.
11/03/2015, 10h18
François M.

Bonjour

Deux choses :

- sans modification majeures, ça irait beaucoup plus vite si les inserts étaient fait dans une transaction unique. Ici on utilise une transaction implicite et c'est la seule et unique raison de ce temps de traitement absurde.

- je ne comprends pas trop l’intérêt de faire un programme applicatif pour faire du DB => DB. il y a des outils pour cela (ici SSIS ferait très bien le taf).