[R] Lenteur script d'import csv to sqlite

[dreamdoors]

Bonjour,

je suis en cour de réalisation d'un petit prog d'import en masse d'utilisateurs dans active directory à partir de fichier csv avec stockage dans sqllite pour pouvoir réutilisé les info dans divers fonction de mon prog.

pour l'instant je suis en cour de test de la fonction csv to sql qui fonctionne mais j'ai un prob de lenteur dès que je dépasse plusieurs dizaines de comptes. (+/- 15 minutes pour l'import de 420 comptes), alors que pour 10 comptes sa dure 2 ou 3 secondes

Les puristes trouverons surement que c'est codé avec les pieds et c'est surement pour ça que c'est lent, mais je suis bidouilleurs et pas programmeur, j'apprend donc au fur et à mesure. (et c'est pour ça que j'ai besoin de vos conseille

les scripts de test sont

- Creation de la Table Utilisateur - pour crée la base, la table et les champs
- Csv to Sqlite - qui importe d'un fichier csv dans la base sqlite
- Lecture Table Utilisateur - pour afficher le contenu de la base

il est bien entendu que c'est Csv to Sqlite qui me pose problème

je joint un petit .zip qui contient l'ensemble des scripts,

csv to sqlite.zip

(16.26 Kio) Téléchargé 191 fois

un csv de 400 comptes et la base vide (meme si elle peut etre crée avec le script Creation de la Table Utilisateur)

- Creation de la Table Utilisateur -

► Afficher le texte

Code : Tout sélectionner

#include <SQLite.au3>
#include <SQLite.dll.au3>

_SQLite_Startup ()
If @error > 0 Then Exit MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
$bdd=_SQLite_Open ("AD.bdd")
If @error > 0 Then Exit MsgBox(16, "SQLite Error", "Can't Load Database!")

 _SQLite_Exec (-1, "CREATE TABLE utilisateurs (Identifiant, Nom, Prénom, Groupe);")

_SQLite_Close ()
_SQLite_Shutdown ()
 

- Csv to Sqlite -

► Afficher le texte

Code : Tout sélectionner

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include"_CSVLib_V1.0.au3"
#include <Array.au3>
#include <windowsconstants.au3>

;# Ouverture Fichier CSV
$File = FileOpenDialog('Open csv', @ScriptDir & '\', 'Csv files (*.csv)', 1, 'base.csv')

;# Lecture Data
$Records = _CSVReadRecords ($File)

;# Initialiser la variable $NbEnregCSV pour la boucle d'insertion sql
$NbEnregCSV = _CSVGetField ($Records, 0, 0)

;# Création Affichage compteur import
GUICreate("" , 150 , 50,100,-1,$WS_POPUP)
GUISetState()
$d=GUICtrlCreateLabel("" , 15 , 15 , 100 , 100)

;# Ouverture de la connexion Sqlite
_SQLite_Startup ()
If @error > 0 Then Exit MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
$bdd=_SQLite_Open ("AD.bdd")
If @error > 0 Then Exit MsgBox(16, "SQLite Error", "Can't Load Database!")

;# debut Boucle d'insertion
For $i = 2 To $NbEnregCSV Step 1

;# Initialisation des variables avec les colonnes CSV provenant de la variable de boucle $i
$IdentifiantCSV = _CSVGetField ($Records, 1, $i)
$NomCSV = _CSVGetField ($Records, 2, $i)
$PrenomCSV = _CSVGetField ($Records, 3, $i)
$GroupCSV = _CSVGetField ($Records, 4, $i)

;# insertion sqlite
 _SQLite_Exec (-1, "INSERT INTO utilisateurs VALUES ('" & $IdentifiantCSV & "','" & $NomCSV & "','" & $PrenomCSV & "','" & $GroupCSV & "');")

;# Mise à jour de la Affichage compteur import
GUICtrlSetData($d , $i&" utilisateurs importer")

;# Fin de boucle
Next

;# Fermeture de la connexion Sqlite
_SQLite_Close ()
_SQLite_Shutdown ()

- Lecture Table Utilisateur -

► Afficher le texte

Code : Tout sélectionner

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Res_Language=1036
#AutoIt3Wrapper_Add_Constants=n
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3>

Local $aResult, $iRows, $iColumns, $iRval

_SQLite_Startup ()
If @error > 0 Then Exit MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
$bdd=_SQLite_Open ("AD.bdd")
If @error > 0 Then Exit MsgBox(16, "SQLite Error", "Can't Load Database!")

; Query
$iRval = _SQLite_GetTable2d (-1, "SELECT * FROM utilisateurs;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then

    _ArrayDisplay($aResult, "Query Result")
Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ())
EndIf
_SQLite_Close ()
_SQLite_Shutdown ()

Merci d'avance pour vos conseils et commentaires

[jchd]

Dès que l'on insère plus de quelques enregistrements, il faut quasi-impérativement "envelopper" les insertions dans une transaction.

Code : Tout sélectionner

_SQLite_Exec (-1, "begin;")
for $i = ...
_SQLite_Exec (-1, "insert into ...")
next
_SQLite_Exec (-1, "end;")
 

Les temps vont descendre à la cave !

[dreamdoors]

merci du conseille

j'ai appliquer mais sa ne change pas la vitesse d'execusion hélas.

[Yogui]

Pouvez vous tester sans la mise à jour de l'Affichage compteur import...

Souvent les lenteurs sont liés à l'affichage et à la mise à jour de la GUI voir pour l'utilisation de la fonction :

AdlibRegister

[dreamdoors]

c'est équivalent, mais j'ai mis le compteur après avoir constatée la lenteur pour mieux m'en rendre compte

par contre je crois que c'est plutôt au niveau de la parti csv ou tableau, parce que si je test sur un csv de 20 lignes l'import est quasi immédiat.

par contre si je met 400 lignes le compteur défile de l'ordre de 1 compte toute les 3 à 4 secondes et ceux dès le 1er enregistrement importer et la vitesse ne varie pas du début à la fin

en gros plus le csv est gros plus la vitesse d'insertion est lente entre chaque ligne

[dreamdoors]

j'ai tester la génération de 5000 ligne dans la table, en remplaçant l'import CSV par une boucle incrémental et il faut moins de 5 sec pour la finir.

Code : Tout sélectionner

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3>
#include <windowsconstants.au3>

;# Création Affichage compteur import
GUICreate("" , 150 , 50,100,-1,$WS_POPUP)
GUISetState()
$d=GUICtrlCreateLabel("" , 15 , 15 , 100 , 100)


;# Ouverture de la connexion Sqlite
_SQLite_Startup ()
If @error > 0 Then Exit MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
$bdd=_SQLite_Open ("AD.bdd")
If @error > 0 Then Exit MsgBox(16, "SQLite Error", "Can't Load Database!")

;# Début de Transaction
_SQLite_Exec (-1, "begin;")

;# debut Boucle d'insertion
For $i = 1 To 5000 Step 1

;# Initialisation des variables avec les colonnes CSV provenant de la variable de boucle $i
$IdentifiantCSV =  $i
$NomCSV =  $i
$PrenomCSV =  $i
$GroupCSV =  $i


;# Insertion sqlite
_SQLite_Exec (-1, "INSERT INTO utilisateurs VALUES ('" & $IdentifiantCSV & "','" & $NomCSV & "','" & $PrenomCSV & "','" & $GroupCSV & "');")


;# Mise à jour de la Affichage compteur import
GUICtrlSetData($d , $i&" utilisateurs importer")

;# Fin de boucle
Next

;# Fin de Transaction
_SQLite_Exec (-1, "end;")

;# Fermeture de la connexion Sqlite
_SQLite_Close ()
_SQLite_Shutdown ()
 

[Yogui]

Code : Tout sélectionner

$Records = _CSVReadRecords ($File)

le problème doit venir de la fonction _CSVReadRecords (maison)

ou utilisez vous : http://www.autoitscript.fr/forum/viewto ... =21&t=2741 ?

[dreamdoors]

oui je crois que vous avez raison, mais c'est pas une fonction maison c'est un udf qui vient de http://www.autoitscript.com/forum/index ... opic=38397

je pensai me simplifier la vie en évitant de réinventé la roue (je part du principe que si la fonction existe autant l'utilisé)

la fonction _ArrayFileToArray me parrait tres bien, je vais essayer de l'utilisé, sinon je crois que je vais être obligé de me faire ma propre fonction de lecture csv à l'aide de stringslipt

[Yogui]

Franchement la fonction : _ArrayFileToArray est vraiment rapide après il ne faut pas oublier que remplir la mémoire d'un poste ça prend du temps ...

[jchd]

Voyons ceci:

► Afficher le texte

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3>
#include <windowsconstants.au3>

;# Ouverture Fichier CSV
Local $File = FileOpenDialog('Open csv', @ScriptDir & '\', 'Csv files (*.csv)', 1, 'base.csv')
Local $hFile = FileOpen($File)

;# Création Affichage compteur import
GUICreate("" , 150 , 50,100,-1,$WS_POPUP)
GUISetState()
$d=GUICtrlCreateLabel("" , 15 , 15 , 100 , 100)

;# Ouverture de la connexion Sqlite
_SQLite_Startup ()
If @error > 0 Then Exit MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
$bdd=_SQLite_Open ("AD.bdd")
If @error > 0 Then Exit MsgBox(16, "SQLite Error", "Can't Load Database!")

_SQLite_Exec (-1, "CREATE TABLE if not exists utilisateurs (Identifiant, Nom, Prénom, Groupe);")

Local $ligne, $data, $compte
$ligne = FileReadLine($hFile) ; lecture des en-tête (ignorés)

_SQLite_Exec (-1, "begin;")

;# debut Boucle d'insertion
While True

$ligne = FileReadLine($hFile)
If @error Then ExitLoop
$data = StringSplit($ligne, ',')
If $data[0] <> 4 Then
MsgBox(0, "Ligne mal formée", $ligne)
ContinueLoop
EndIf

;# insertion sqlite
; attention, les littéraux en SQL doivent être entre apostrophes '' (simple quote) et non ""
; d'autre part, si un champ contient une simple quote, il convient de la doubler
;~ _SQLite_Exec (-1, "INSERT INTO utilisateurs VALUES ('" & $IdentifiantCSV & "','" & $NomCSV & "','" & $PrenomCSV & "','" & $GroupCSV & "');")
_SQLite_Exec (-1, "INSERT INTO utilisateurs VALUES (" & _
_SQLite_Escape($data[1]) & "," & _
_SQLite_Escape($data[2]) & "," & _
_SQLite_Escape($data[3]) & "," & _
_SQLite_Escape($data[4]) & _
");")

$compte += 1
;# Mise à jour de la Affichage compteur import
GUICtrlSetData($d , $compte & " utilisateurs importés")

;# Fin de boucle
WEnd

_SQLite_Exec (-1, "end;")
FileClose($hFile)

;# Fermeture de la connexion Sqlite
_SQLite_Close ()
_SQLite_Shutdown ()

Ce n'est pas vraiment la peine de se faire suer avec des usines à gaz pareilles pour traiter les .CSV car il n'y en a pas deux qui respectent les mêmes conventions. De plus, comme ce sont en général des sources externes sur lesquelles l'on a peu ou pas du tout de contrôle, le format employé est bien (trop) souvent susceptible de changer. J'ai même une source qui me renvoie pas moins de trois formats distinct (et incompatibles) suivant le serveur qui me répond ! Ou bien je redemande le fichier en boucle jusqu'à "tomber" sur un serveur qui me fournit le format X attendu, ou bien je "depiaute" le premier fichier reçu pour déterminer dans quelle moulinette il faut le mixer...
Je traite une vingtaine de sources de .CSV et j'ai actuellement 17 routines distinctes. C'est du sur-mesure.
Ceci dit, cette UDF CSV est un modèle d'inefficacité, tes temps de traitement le montrent !

Attention à la quotation (escape) des littéraux SQL. La prochaine version de SQLite risque de ne plus faire l'interprétation elle-même, ce qui n'est pas plus mal. Donc 'abc' est un littéral chaîne, "abc" est un nom de table ou un alias, un nom d'index, un nom de base, ...

Pour info, vide ta base et refais les timmings avec et sans transaction

EDIT 2 : regarde aussi l'UDF AD dans le forum d'outre-étang (aka US), il pourrait t'être utile.

[dreamdoors]

YESSSSSSSSSSSSSS....!!!!!!!!! MERCI

sa fonctionne impéccable moin de 5 secondes pour l'import, cette solution en plus à l'air bien plus simple.

pour l'udf AD c'est effectivement celui que je j'utilise.

à votre avis quelle avantage ou inconveniant d'activé (ou pas) les transaction ??

grand merci,

[jchd]

SQLite fonctionne par défaut en mode autocommit, ce qui veut dire qu'il considère chaque opération comme une seule et unique transaction. Donc, il pose des verrous (même pour une lecture) au début de l'opération, journalise la chose, récrit ce qu'il faut, force l'écriture sur disque, détruit le journal et seulement lorsque tout ceci s'est déroulé sans erreur, considère que la transaction est terminée.

Lorsqu'on a des update ou insertions à effectuer en masse, l'articulation de toutes ces opérations consomme inutilement un temps considérable. Il est alors plus judicieux de grouper les update et autre insert en lot de 1000 à 10000 au sein d'une transaction. Donc verrous, journalisation, écriture se passent essentiellement en cache, ainsi que les opérations internes (rééquilibrage d'index etc). La différence en temps passé est souvent énorme.

Si la base est utilisée par un ou plusieurs autres processus, il faut définir un timeout raisonable (j'utilise 60000 soit 60s sans honte) et employer une transaction immediate "begin immediate;" ce qui permet d'éviter tout possibilité de deadlock.

Employer aussi BEGIN IMMEDIATE .. END pour une transaction du type read/modify/write pour s'affranchir de tout deadlock et garantir qu'aucun autre processus ne vient affecter le jeu de données qu'on a lu et qu'on se propose de modifier, sinon, c'est la cata (le I de ACID n'est plus respecté [Isolation]) !

Voilà.