Chargement d'un fichier logs dans une base SQL server trop long

Invité · 11/01/2008, 16h54

Bonjour,

Je suis actuellement en train d'effectuer un algorithme sous VB Express qui transfère un fichier de logs (une sorte de XML), pour y mettre les données dans la base SQL Server associée.

Mais le problème, c'est que le téléchargement actuel me semble beaucoup trop long. Bon, il faut déjà que je remplaces toutes mes boucles "Pour" par "Tant que" pour optimiser mon code (la machine restera moins de temps à faire une recherche qu'elle a déjà trouvé), mais à mon avis cela restera encore long.

Est-ce que quelqu'un s'y connaît un peu là-dedans ? Si des fois il y a moyen de trouver des API, ou trucs dans le genre, capables d'accélérer le traitement...

Harry Potter

**alain.couthures** · 12/01/2008, 10h32

Si le format est pratiquement du XML, le passer en XML pourrait permettre de le parser avec SAX ou STX pour en extraire bien les données : est-ce que vous voulez toutes les mettre en base ?

Quand vous parlez d'un temps de téléchargement trop long, de quelle étape s'agit-il ?

Invité · 14/01/2008, 11h01

Merci pour ta réponse Alain,

Voici un exemple d'une ligne du fichier des logs :

<log><date>2007-12-11 15:40:54</date><process name="NomSteServer Interface" version="2.3.2.14"/><priority>NOTICE</priority><activity>LOG_AUTH</activity><message>Login_User</message><origin><login org="nomste" domain="local" name="login" ip="192.168.0.127"/></origin><target><infoUser os="Windows Vista" browser="Firefox"/></target></log>

Je ne souhaite pas tout y mettre dans ma base. Par exemple, je n'extrais pas les données "Windows Vista" ou "Firefox".
Sinon SAX, STX... C'est quoi ça ? Par contre, en ce qui concerne l'extraction des données, j'y arrive déjà par le biais de mon code. Serait-ce alors plus rapide avec un parseur ?

Pour la dernière question, je sais pas si je vais bien y répondre, mais le temps de téléchargement trop long se trouve dans la phase du traitement du code.

Si ça peut être plus clair, je peux décrire mon code brièvement...

Après ouverture du fichier, traitement de chaque ligne du fichier :

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
While Not EOF(1)
'Récupération des données dans des variables
...
Site = PrendreEntre(source, "<site name=" & Chr(34), Chr(34) & "/></target>")'source = ligne en cours du fichier logs (entre<log> et </log>)
...
 
'Vérifier que les données n'ont pas déjà été stockées au préalable dans la table
...
SiteExiste = False
...
            '...table site
            indice = 0
            While (indice <= (condition)) And (SiteExiste = False)'Sortir de la boucle quand on a parcouru tout le fichier ou que Site de la ligne en cours a déjà été stocké au préalable dans la table
                If condition2 = Site Then'je ne rentre pas dans les détails de condition2
                    SiteExiste = True
                End If
 
                indice += 1
            End While
...
'On crée les requêtes SQL correspondantes en vue de l'insertion
...
            Dim strSQLSite As String = "INSERT into Site(NumSite, Site) VALUES(" & _
                CptCleSite & ",'" & Site & "');"
...
 
                'Mise en place et execution de la requête d'insertion dans SITE
                If SiteExiste = False And Site <> "0" Then
                    'Stockage dans un tableau du site pour éviter les doublons
                    SiteNom(CptCleSite - 1) = Site
 
                    cmSQLSite = New SqlCommand(strSQLSite, cnSQL)
                    cmSQLSite.ExecuteNonQuery()
                    CptCleSite += 1
                End If
end while

En fait, le temps de téléchargement est correct quand le fichier logs fait grosso-modo 10 000 lignes. Mais si par exemple, le fichier fait plus de 65 000 lignes, l'attente me semble vraiment beaucoup trop longue (cela reste entre 0 et 2 % au bout de, peut-être, 5 minutes, 1/4 d'heure, voire plus)

**alain.couthures** · 14/01/2008, 11h08

SAX et STX sont en Java et permettent de lire et transformer à la volée : ils pourraient servir, à confirmer, à générer un CSV directement intégrable en base.Il est tout à fait possible de "parser" soi même un document mais il faut faire très attention à l'utilisation des variables pour que le temps de traitement soit raisonnable. Un parseur est optimisé pour cela mais il est généraliste...

Invité · 14/01/2008, 11h16

D'accord, merci.

Mais y'a des trucs, c'est du chinois dans ce que tu me dis pour moi.

Quand tu dis "parser" ???

Sinon, existe-t'il un parseur pour VB Express ?

**alain.couthures** · 14/01/2008, 11h18

Parser, c'est reconnaître des éléments dans du texte.

VB Express, c'est VStudio gratuit ? Alors oui, .Net intègre un parseur XML !

Invité · 14/01/2008, 11h32

VB Express est gratuit. Après, il me semble bien que c'est Visual Studio aussi.

Donc, je peux utiliser un parseur... Manque plus qu'à savoir comment là, car je ne sais pas du tout comment faire...

Mais est-ce que ce sera vraiment beaucoup plus rapide qu'avec mon code, avec mes boucles while dans un while, ... ?

**alain.couthures** · 14/01/2008, 12h30

Tout dépend de l'efficacité de ton code mais si ton XML est aussi régulier et simple qu'il y parait ton programme peut être très efficace à condition qu'il ne manipule pas, par exemple, des chaines de caractères trop longues.

C'est plutôt en terme d'évolutivité que le parseur est intéressant.

Invité · 14/01/2008, 14h40

Arf, il risque d'y avoir quelques difficultés alors.

Parce que justement, j'ai remarqué que le fichier n'était pas, ou ne semblait pas, si régulier que cela.

Je prends cet exemple (dans la balise <origin>) :

<origin><login org="nomste" domain="local" name="login" ip="192.168.0.127"/></origin>

Il ne fait pas de séparation pour chaque sous-partie, comme par exemple <org>...</org>, ..., <ip>...</ip>, ...

Et comme c'est exactement les parties que je recherche, je me demande si un parseur ne va pas être difficile dans mon cas. Parce que, si j'ai bien compris, un parseur ne peut récupérer que les valeurs entre chaque borne, sans distinction.

**alain.couthures** · 14/01/2008, 14h48

org est simplement un attribut et un parseur XML sait très bien gérer cela tandis qu'un programme perso devra apprendre à gérer aussi les attributs.

Ce que je trouve un peu coûteux, c'est l'apprentissage du XML, de XPath,de XSLT ou STX... mais ça en vaut la peine !

En terme de volumes et de traitements, le chargement en mémoire d'un document de plusieurs Mo ne prend que quelques secondes et la récupération de données sans traitements et recherches croisées est, d'habitude, du même ordre de grandeur.

Invité · 14/01/2008, 15h14

D'accord, hé bien merci beaucoup Alain pour toutes ces infos et ces conseils.

J'en ai déjà pas mal appris.

Mais si c'est effectivement lourd d'apprendre le XML, XPath, XSLT ou STX, ça va pas m'être facile de trouver quelque chose d'assez clair pour avancer mon problème. Enfin, je peux toujours essayer de chercher des docs sur ce sujet.

Sinon, une autre question... Si jamais je trouve quelque chose qui va vraiment me faire accélérer le transfert des données, penses-tu que tout le code que j'ai déjà fait deviendra inutile ? (si le parseur, XPath, ou autres s'occupera déjà du transfert en mémoire ainsi que de l'écriture dans la base... ce qui marche déjà avec mon code, mais un peu beaucoup lent)

**alain.couthures** · 14/01/2008, 15h19

Je n'ai pas encore bien perçu ce qui est lent dans le programme actuel...

Peut-être est-ce l'écriture en base ? Il est toujours mieux de faire un import massif (on peut même désactiver les index pour aller plus vite) plutôt que des inserts ligne par ligne.

La chaine globale (récupération-transformation-chargement) n'existe pas toute faite mais des produits (ETL) y aident. A moins d'utiliser un de ces produits, le code déjà écrit sera réutilisé partiellement.

Invité · 14/01/2008, 17h09

En fait, sur un fichier de plus de 65 000 lignes, faire des insertions ligne par ligne rend le processus vraiment vraiment très lent. Parce que je n'ai pas dit le nombre de table de ma base de données : au moins une dizaine.

Avec des contrôles (par des boucles) pour chercher si une insertion équivalente à celle en cours a déjà été faîte (pour éviter les doublons, et dans ce cas l'insertion n'a pas lieu) avant chaque insertion, pour chaque ligne, ça doit bien prendre du temps cela je pense.

Sinon, tu ne saurais pas par hasard comment désactiver ces index dont tu parles ?

Invité · 22/01/2008, 09h39

Quand je parlais de plus de 65.000 lignes, c'est nettement plus en fait. J'ai vérifié par le code, le programme est passé ligne par ligne sur un fichier qui comporte plus de 680.000 lignes !!! D'où la lenteur...

Cependant, je viens de remarquer un truc qui m'a peut-être fait comprendre pourquoi il pouvait être si lent dans la boucle qui insère les données dans ma base. Explication...

Il y a dans un premier temps une première boucle :

Code :

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

1
2
3
4
        While Not EOF(1)'1, c'est le fichier logs, tant qu'on n'est pas à la fin du fichier
            LineInput(1)'On lit une ligne
            intCompteurLigne += 1'On compte une ligne de plus au compteur
        End While

Comme vous pouvez le remarquer, c'est pour compter le nombre du fichier logs. En récupérant le nombre de lignes total, cela permet de faire des calculs de pourcentage sur la deuxième boucle, pour la barre de progression...

Ensuite, mise en place de ma deuxième boucle qui là fait les insertions dans ma base de données (je simplifie beaucoup et je ne mets qu'une table pour essayer de rendre le code plus visible) :

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
 'on lance une boucle End Of Line qui va lire chaque ligne une a une jusqu'a la fin du fichier
        While Not EOF(1)
            'on commence a calculer le pourcentage pour le compteur
            If (intCompteurLigne \ 100) <> 0 Then formTrois.lblPourcentage.Text = ((CptI) \ (intCompteurLigne / 100)) & " %"
            'on lit une ligne
            strLine = LineInput(1)
            'on effectue les calculs sur chaque ligne pour trouver les champs qui nous interessent dans le xml
            strDomaine = PrendreEntre(strLine, "domain=""", """ name=")
 
            'Vérifier que les données n'ont pas déjà été stockées au préalable dans la table
            DomaineExiste = False
            '...table domaine
            indice = 0
            While (indice <= (CptDomaine - 2)) And (DomaineExiste = False) And (strDomaine <> "0")'Deux dernières conditions sont mises pour ne pas rentrer dans la boucle quand ce n'est pas nécessaire, la première indique juste de passer sur chaque ligne du fichier antérieure à celle en cours
                If Domaine(indice) = strDomaine Then'si le domaine de la ligne en cours a déjà été stocké dans le fichier
                    DomaineExiste = True
                End If
 
                indice += 1
            End while
 
                '... Je passe d'autres boucles qui n'ont pas d'intérêt ici...
 
                'Déclaration de la requête SQL d'insertion
            Dim strSQLDomaine As String = "INSERT into Domaine(NumDomaine, NomDomaine) VALUES(" _
                & CptDomaine & ",'" & strDomaine & "');"
 
            'Mise en place et execution de la requête d'insertion dans Domaine
            If DomaineExiste = False And strDomaine <> "0" Then
                'Stockage dans un tableau de Domaine pour éviter les doublons
                Domaine(CptDomaine - 1) = strDomaine
 
                cmSQLDomaine = New SqlCommand(strSQLDomaine, cnSQL)
                cmSQLDomaine.ExecuteNonQuery()
                CptDomaine += 1
            End If
 
            'On incremente les 2 compteurs, celui de l'index de ligne et celui du pourcentage de la barre
            i += 1
            CptI += 1
 
            'on ajoute le nouveau login dans cbxLogin
            '(Comme j'ai  repris un ancien code déjà existant, je pense que cette partie n'est pas nécessaire, avec ma base de données)... Je suis en train de me demander si cela aussi peut ralentir le traitement
            If strLogin <> "0" Then
                If strTypeAction = "Login_User" Then If Not cbxLogin.Items.Contains(strLogin) Then cbxLogin.Items.Add(strLogin)
            End If
 
            'C'est cette partie, d'après ce que j'ai pu remarquer, qui opère un gros ralentissement au programme, d'après ce que j'ai pu constater
            'refresh du pourcentage et avancement de la barre de progression
            formTrois.lblPourcentage.Refresh()
            formTrois.mybarre.PerformStep()
            formTrois.mybarre.Refresh()
 
            'sortie de la boucle
            End While

Voilà, désolé pour le pâté de code que j'ai mis (même si j'ai essayé de le simplifier, c'est pas forcément facile). Je pense que cette dernière partie n'y est pas pour rien dans la lenteur du transfert. Ce doit sûrement être ce Refresh...
Car dans la première boucle, il n'y a pas de refresh et il arrive à passer toutes les lignes en quoi, 10 - 15 secondes. Je sais qu'il n'y a que deux lignes code et aucune boucle (à côté de toutes celles de la deuxième boucle while), mais quand même, ça m'a l'air de faire une nette différence !! (sans Refresh...)

De plus, je testes de rajouter un refresh dans la première boucle (on voit le numéro de la ligne en cours dans un Label) :

Code :

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

1
2
3
4
5
6
7
        While Not EOF(1)
            LineInput(1)
            intCompteurLigne += 1
 
            formTrois.test.Text = intCompteurLigne
            formTrois.test.Refresh()
        End While

Et avec l'affichage de la ligne dans le label, on voit les numéros défiler et donc la vitesse qu'il met à passer dans la boucle. Il est soudainement devenu beaucoup beaucoup plus lent !

Que faire ? Enlever le Refresh de la deuxième boucle ? Quand je le fais, il a toujours l'air d'être lent, mais comme il n'y a plus de barre de progression, difficile de voir où il en est. Faut peut-être que je revois ça plus en profondeur...

Harry Potter

P.S. : Si une barre de progression est uniquement efficace pour les fichiers de quoi, 2 Mo, je ne vois pas trop l'intérêt. Car justement, si c'est rapide, on a pas trop à attendre et si c'est plus gros et donc plus lent, c'est là que ça devient intéressant de voir à combien on est.

**alain.couthures** · 22/01/2008, 09h42

Pourquoi ne pas couper la poire en "deux" et ne faire le Refresh qu'une fois sur dix ou cent (un test avec un modulo c'est génial pour ça!)

Invité · 22/01/2008, 09h49

Ah oui pas bête ça, je n'y avais pas pensé.

Faut juste que je revois la vitesse où il va d'abord sans aucun Refresh (en me créant une table de test et en insérant toutes les lignes dedans, pour quand je l'arrêtes, voir combien de lignes il a fait).

Invité · 23/01/2008, 12h21

Même si autre chose est lent, c'est à mon avis le Refresh qui ralentit le plus, et de loin. Car je viens de tester la vitesse avec aucun Refresh (en ne remplissant qu'une seule table, un champ et sans aucune condition et boucle), et la vitesse est, d'après mes calculs, 3 fois plus rapide qu'avec !!

En tout cas, merci encore une fois Alain pour ta proposition de "couper la poire en deux"... Elle est vraiment très bonne. Je vais y réfléchir...

Invité · 23/01/2008, 15h54

Par contre, je crois que sans les Refresh, ce n'est pas encore tout à fait ça...

Dès que je l'applique sur mon fichier de plus de 600.000 lignes, il ne se trouve à la 17.704 ème ligne qu'au bout de 35 minutes !! Un calcul vite fait, et s'il continue jusqu'au bout comme ça, il aura fini au bout de... 35 heures !!!

Il veut ptet faire 35 heures par semaine lui aussi, mais là, j'aimerai bien qu'il fixe ses horaires en minutes, voire en secondes...

Invité · 29/01/2008, 16h19

Voilà le message qu'il me met maintenant, aléatoirement, en cours d'exécution du transfert :

"Context DeadLock détecté ...
Le CLR n'a pas pu effectuer de transition du contexte COM 0x554540 au contexte COM 0x5546b0 pendant 60 secondes. Le thread qui possède le contexte/cloisonnement de destination est probablement en train d'attendre ou de traiter une opération très longue sans pompage des messages Windows. Cette situation a généralement des effets négatifs sur les performances et peut parfois entraîner le blocage de l'application ou une utilisation ininterrompue de la mémoire. Pour éviter ce problème, tous les threads STA (Single Threaded Apartment) doivent utiliser des primitives d'attente de pompage (par exemple CoWaitForMultipleHandles) et pomper de manière régulière les messages lors des opérations qui sont longues à s'exécuter."

Invité · 01/02/2008, 11h54

En faisant, Ctrl + Alt + E, j'ai essayé en décochant l'option "levé" à ContextDeadLock, mais ça a l'air de toujours aller à la même lenteur.

En tout cas, avec ce message d'erreur, je crois que c'est une exception ou un truc comme cela sous VB qui ralentit tous les transferts.

Mais qu'est ce qui faut faire pour le rendre plus rapide...

Chargement d'un fichier logs dans une base SQL server trop long

VB.NET

Discussions similaires

Partager

Partager