temps insertion fichier texte trop long

**developpeur71** · 15/12/2009, 16h49

Bonjour,

Je développe une application VB avec visual studio qui me permet d'inserer des données brut provenant de fichiers texte dans une BDD sql Server 2005 version express.

Un répertoire (qui fait en moyenne 2.5 à 3 Mo) represente une semaine et contient à peu pres 80 fichiers textes et chaque fichier contient pas loin de 1000 lignes donc pour une semaine j'insere dans ma table ligneTransaction pas loin de 80 000 lignes.

Pour chaque ligne j'ai une routine qui la decoupe (avec un split) et je fais des test et des conversion avant d'appeler ma procedure stockée.

Dans ma procédure stockée je fais un test pour savoir si cette ligne existe, je reupere des clef etrangeres et ensuite je l'insere.

Quand j'utilise mon application et que je souhaite integrer une semaine le temps d'insertion augmente, pour la premiere semaine je met 6 minutes et quand j'arrive à la quatrième semaine je met pas loin de 41 minutes .

C'est beaucoup vous ne trouvez pas ? D'autant que dans une année il y a 53 semaine !!!

Je travaille en local sur un poste windows xp 3.48 Go de ram Pentium Dual Core 2.6 GHz avec visual studio 2008 et sql server 2005 express.

Est ce que le temps d'execution vous semble normal ?

Pouvez me donner un tuyau pour diminuer le temps de traitement c'est tres important et ca fait une semaine que je bloque !!

Merci d'avance !!!

**nasty** · 15/12/2009, 17h14

Bonjour
Euh non c'est pas long, c'est beaucoup trop long

, je sais que je met environ 30sec pour 35k lignes dans ma bdd sous sql server 2005.
Peut on voir le code qui permet l'insertion dans la bdd?
Tu fais ca dans un thread a part ou directement dans ton thread principal?
Cordialement
Nasty

**developpeur71** · 15/12/2009, 17h36

je pense que je fais ca dans mon thread principal je ne sais pas trop en fait !!

Voici mon code je ne sais pas si ca sera lisible c'est la premiere fois que post du code :

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
Public Function integrerLigne(ByVal maligne As String, ByVal pointVente As String)
 
        Dim continuLigne As Boolean = True
 
        Dim codeErreur As Int16
        Dim ElementBis() As String = maligne.Split(";")
        Dim v As Char = ","
        Dim p As Char = "."
        Dim sBis As String       
        Dim d1 As DateTime = New DateTime()
        Dim i As Int16
 
        Try
                If (ElementBis(3).Equals("V") = True) Then
 
                If (ElementBis(6).Contains(p)) Then
                    sBis = ElementBis(6).Replace(p, v)
                Else
                    sBis = ElementBis(6)
 
                End If
 
                i = CInt(ElementBis(0))
                Dim d As String = ElementBis(1)
                Dim h As String = ElementBis(2).Substring(4, 4).Insert(2, ":")
 
 
                Dim dh As String = d + " " + h
                d1 = DateTime.Parse(dh)
 
            bd.preparePs("dbo.test")
            bd.addParam("@IdMachine", ParameterDirection.Input, DbType.Int16)
            bd.fixerValeur("@IdMachine", i)
            bd.addParam("@codeUNI", ParameterDirection.Input, DbType.String)
            bd.fixerValeur("@codeUNI", pointVente)
            bd.addParam("@date", ParameterDirection.Input, DbType.DateTime)
            bd.fixerValeur("@date", d1)
            bd.addParam("@decision", ParameterDirection.Input, DbType.String)
            bd.fixerValeur("@decision", ElementBis(3))
            bd.addParam("@codeModePaiement",ParameterDirection.Input,         DbType.String)
            bd.fixerValeur("@codeModePaiement", ElementBis(4))
            bd.addParam("@nbTransaction", ParameterDirection.Input, DbType.Int16)
                bd.fixerValeur("@nbTransaction", Int16.Parse(ElementBis(5)))
                bd.addParam("@somme", ParameterDirection.Input, DbType.Decimal)
                bd.fixerValeur("@somme", Decimal.Parse(sBis))
 
                codeErreur = bd.executer()
 
                continuLigne = True
 
                If (codeErreur = 2) Then
                    continuLigne = False
                End If
 
 
            End If
 
            Return continuLigne
 
        Catch ex As Exception
            MsgBox("ligne non valide" + bd.getLib + "  " + pointVente + " " + i.ToString + " " + d1.ToString + " " + sBis)
        End Try
 
 
    End Function

**bobo10** · 15/12/2009, 17h45

Bonjour,

Selon moi c'est pas le traitement dans VB pour le split des lignes qui pose problème, ce doit être au niveau SQL que tu as le ralentissement.

Je pense qu'une procédure stocké a beaucoup d'avantage dans certains cas mais selon mois dans ton cas tu devrait plutôt faire tes inserts avec un Query en direct dans la base. Actuellement la procédure tourne pour chaque ligne.
Essai d'envoyer plusieurs enregistrement dans la même requete:

Code :

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

INSERT INTO Table (champ1,champ2,etc...) VALUES (Champ1,champ2,etc)(champ1,champ2,etc)

Le temps de traitement SQL sera beaucoup plus rapide

**nasty** · 15/12/2009, 17h52

C'est une possibilité.
Pour faire mes insertions massives j'utilise

Code :

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

1
2
3
4
5
6
7
8
9
bulk insert elect_test_yln.dbo.FICHE
from 'C:\Documents and Settings\YLN\fiche.txt'
with 
(
datafiletype = 'widechar',
fieldterminator = ';',
rowterminator = '\n',
firstrow = 1
);

tu a juste a faire le traitement dans chacun de tes fichiers pour mettre les "," et autres séparateurs, ce qui doit être gérable niveau code (j'ai pas encore essayé mais ca doit être trouvable)
Mais je ne promet rien

Cordialement
Nasty

**Pol63** · 15/12/2009, 18h13

executer une requete par ligne c'est ca qui prend du temps

utilise le bulk insert pour qu'sql server aille directement piocher les données dans les fichiers et ca devrait etre relativement plus instantanné

**developpeur71** · 15/12/2009, 18h16

Pour bobo :
Actuellement la procédure tourne pour chaque ligne.
80000 lignes = 80000 appel de procedure sotckees !!

Tu me conseille donc d'utiliser un query c ca et non passer par une procedure sotckee ?

Pour Nasty :

apres bulk insert tu met quoi le nom de la table ?
Est ce que je peux utiliser le multithreading si oui tu peux m'en dire plus ?

Merci beaucoup pour votre aide !!

**nasty** · 15/12/2009, 18h17

Merci Pol63 de confirmer
Mais ca ne sera toujours pas "instantané", il y aura toujours le temps de traitement pour mettre les fichiers textes en forme et ca peut être assez long des fois! Mais ca sera toujours moins que tes 40minutes

Cordialement
Nasty

**bobo10** · 15/12/2009, 18h25

bulk je ne connais pas, je peux pas me prononcer.
Cependant j'avais fait des tests en executant un executenonquery avec mes requetes en "string" dans une boucle ou je splittait environ par 100 insertions par requete et les temps d'execution se sont environ divisé par 10

**developpeur71** · 15/12/2009, 18h26

Je n'ai jamais utilise bulk !!
C'est une requete sql ? ou une fonction VB ?

Le probleme c'est qu'il faut renseigner le chemin du fichier et que dans mon cas le fichier est un parametre car j'en ai plusieurs dans plusieurs repertoire !!

Merci d'avance !!

**nasty** · 15/12/2009, 18h29

bulk insert c'est du sql.
il faut passer le chemin du fichier a traiter.
Si tu veux te faciliter le travail, il est envisageable de regrouper tous les fichiers de ton dossier dans un seul fichier texte (et faire la mise en forme par la même occasion), de lui attribuer un nom précis et tu passe le chemin de ce fichier en tant que paramètre.
Je ne l'ai pas fait via vb, mais ca doit être faisable!

p.s et edit : pour le multithread je ne suis pas un expert mais je vais essayer de m'expliquer
tu as un thread A qui va faire le traitement des fichiers du dossier 1, quand c'est fini l'insertion se fait dans un thread B et pendant l'insertion ton thread A va faire le traitement des fichiers du dossier 2 et ainsi de suite.
Je suis sur que tu peux gagner de precieuses minutes (voir heure vue la durée de tes traitements

) en la jouant comme ca. Mais je peux pas t'expliquer plus j'ai encore un peu de mal avec le multithread

Cordialement
Nasty

**Pol63** · 15/12/2009, 18h32

tu peux aussi tenter autre chose
si tous tes fichiers vont dans la meme table
tu ouvres tous les fichiers
tu utilises un system.text.stringbuilder pour concaténer toutes tes requetes
et dans le commandtext tu mets le contenu du stringbuilder
il n'y aura donc qu'une requete qui va passer

il parrait meme que plutot que
insert into
insert into
...
il serait plus rapide de faire
insert into
select ..
union select ..
union select ..

**developpeur71** · 15/12/2009, 18h43

Merci pour toutes ces reponses je vais tenter le bulk !

j'ai donc crée une procedure stockee et je passe le chemin du fichier en parametre mais ca ne marche pas :

Code :

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

1
2
3
4
5
6
7
8
9
create proc insererFichier  @cheminfichier varchar(300)
 
as
begin
		BULK INSERT ligne
		FROM @cheminfichier 
		WITH (FIELDTERMINATOR = '\n', ROWTERMINATOR = ';')
 
end

**nasty** · 16/12/2009, 09h45

Bonjour
Pourquoi passer par une procédure stockée?
Si tu travaille en VB tu peux tres bien faire la requête depuis ton code!
Et pour l'instant tu as quoi comme erreur lors de l'execution?
Cordialement
Nasty

**developpeur71** · 16/12/2009, 10h59

Bonjour,

Voici les erreurs :

Msg*102, Niveau*15, État*1, Procédure*insererFichier, Ligne*7
Syntaxe incorrecte vers '@cheminfichier'.

Msg*319, Niveau*15, État*1, Procédure*insererFichier, Ligne*8
Syntaxe incorrecte près du mot clé «*with*». Si l'instruction est une expression de table commune ou une clause xmlnamespaces, l'instruction précédente doit se terminer par un point-virgule.

Merci

**nasty** · 16/12/2009, 11h09

Re bonjour
Euh pour la première erreur, as tu pensé a mettre les " ' " autour de ton chemin?
Et pour la deuxième j'ai pas encore d'idée.
Cordialement
Nasty

**Pol63** · 16/12/2009, 11h12

à mon avis c'est plutot que bulk insert veut le chemin directement, pas dans une variable
donc là aussi executer le bulk insert dans le commandtext plutot que de passer par une procédure stockée serait plus conseillé
ca permet de concaténer le chemin dans la requete

**nasty** · 16/12/2009, 11h14

Je ne savais pas que lorsque l'on donnait un chemin via une variable il n'etait pas nécessaire d'ajouter les '!
Cordialement
Nasty

**Pol63** · 16/12/2009, 11h28

un string en vb.net que tu mets dans une variable tu fais monstring = "valeur"
mais il contient valeur seulement
en sql quand on appelle une procédure stockée qui demande un varchar soit on fait une variable

Code :

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

1
2
3
declare @str varchar(50)
set @str = 'test' -- qui ne contient donc pas de '
exec sp @str

ou alors directement

Code :

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

exec sp 'test'

le ' n'est qu'un délimiteur

**nasty** · 16/12/2009, 11h34

Oui c'est logique, je te remercie pour ce micro cours!
Cordialement
Nasty

temps insertion fichier texte trop long

Windows Forms

Discussions similaires

Partager

Partager