Performance base de données SQLite

[Cls]

Hello à tous,

revenu depuis un jour seulement et déjà une question !

J'ai une base de donnée SQLite (excellent les commande en natif ! ) et un fichier d'environ 30000 lignes contenant des INSERT. Après avoir créé ma base et la table qui va bien, je parcours toutes les lignes du fichier et procède à l'insertion via la commande DatabaseUpdate. Mon souci vient du fait que ce traitement prend énormément de temps : 30 minutes environ pour mes 30k de lignes !

Avez - vous des infos sur la performance de SQLite, notamment en insertion ?

(Je poste un peu de code et le timing exact dès que je rentre chez moi, je suis toujours au boulot )

Cordialement,
Cls

[Cls]

Comme promis, un peu de code pour tenter l'expérience :

Le code :

Code : Tout sélectionner

Global databaseFile.s = "database.db"

SetCurrentDirectory("C:\")

Procedure CreateDatabase()
  If FileSize(databaseFile) = -1
    ; Création du fichier
    CreateFile(0, databaseFile)
    
    CloseFile(0)
    
    ; Création de la table principal
    OpenDatabase(0, databaseFile, "", "", #PB_Database_SQLite)
    
    DatabaseUpdate(0, "CREATE DATABASE test_database")
    
    
    req.s = "CREATE TABLE test (id Int(9), x int(3), y int(3))"

    DatabaseUpdate(0, req)
    
    CloseDatabase(0)
    
    
  EndIf  
EndProcedure

Procedure RemplirDatabase(file.s)
  
  If ReadFile(1, file)
    
    OpenDatabase(0, databaseFile, "", "", #PB_Database_SQLite)
    
    Debug_A = ElapsedMilliseconds()
    
    tmp.s
    While Eof(1) = 0           
      
      Debug_C = ElapsedMilliseconds()
      line.s = ReadString(1)      
      
      line = RemoveString(line, Chr(96)) ; Supprime le caratere `
      
      DatabaseUpdate(0, line)
      Debug_D = ElapsedMilliseconds()
      
      Debug "Temps d'éxécution insert : " + Str(Debug_D - Debug_C) + " ms "
      
    Wend
    
    Debug_B = ElapsedMilliseconds()
    
    Debug "Temps d'éxécution total : " + Str(Debug_B - Debug_A) + " ms "
    
    CloseDatabase(0)
    
    CloseFile(1)
    
  Else
    MessageRequester("Erreur lors de l'import", "Impossible de lire le fichier à importer : " + #CRLF$ + file)
  EndIf
  
EndProcedure


;- Librairies
UseSQLiteDatabase()

CreateDatabase()

RemplirDatabase("C:\test.sql")

Le fichier test.sql :

Code : Tout sélectionner

INSERT INTO test VALUES (409,8,400);
INSERT INTO test VALUES (710,309,400);
INSERT INTO test VALUES (711,310,111);
INSERT INTO test VALUES (712,311,500);
INSERT INTO test VALUES (713,312,400);
INSERT INTO test VALUES (401,-8,400);
INSERT INTO test VALUES (209,10,00);
INSERT INTO test VALUES (309,-8,-7);

Je trouve que les résultats renvoyés sont assez énormes ! 100ms pour un INSERT...

[Cls]

Bon après quelques tests et quelques recherches, voilà les résultats :



Résultats que je trouve aberrant !

Et pourtant, j'ai trouvé des vieux tests SQLite ici :
http://www.sqlite.org/speed.html

Je ne sais donc pas trop quoi penser. J'ai modifié la structure de ma table, utilisé des transactions, vérifié mes requêtes : sans résultats !

Trois solutions :
- utiliser une librairie externe (je crois avoir vu un sqlite3.dll)
- changer de base (pourtant SQLite me plait bien sur le principe)
- je suis une patate et j'ai fait une erreur gros comme mon pif (c'est fort probable) : il suffit donc de modifier un truc dans mon traitement.

Si quelqu'un a une idée sur le sujet, ça m'avancerait bien !

Cordialement,
Cls

[Thyphoon]

J'ai un peu le même problème que toi. Mai déjà désactive le debugger complètement lorsque tu compile l'exécutable...ça devrait un peu améliorer les choses... Il faudrait peut être poser la question aussi le sur le forum anglais.
de mon côté je vais aussi faire des tests aujourd'hui donc je te tient au courant...

Edit: regarde ce que j'ai trouvé sur le forum anglais : http://www.purebasic.fr/english/viewtop ... qlite+slow

[Thyphoon]

Bon moi mon exécutable et ma base de donnée sont sur une clef USB donc ça doit jouer un peu ...

voilà les requetes que je fais (ce sont des updates pas des inserts aussi..)

Code : Tout sélectionner

UPDATE [Entry] SET Title='coucou',Notes='' WHERE Id='2'
UPDATE [Field] SET FieldValue='login1' WHERE Id='3'
UPDATE [Field] SET FieldValue='Password' WHERE Id='4'

sur clef usb Debugger désactivé ça me donne :
sans Transaction : 2172ms
avec Transaction : 765ms

pour faire une transaction
je commence part faire un

Code : Tout sélectionner

DatabaseUpdate(#Db,"BEGIN" )

ensuite toute mes requêtes, et je finis part

Code : Tout sélectionner

DatabaseUpdate(#Db,"COMMIT" )

[Thyphoon]

autre chose a étudier en fonction de l'utilisation de la base de donnée... c'est

Code : Tout sélectionner

hDB = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite) 

si j'ai bien compris la base de donnée s'ouvre en mémoire...
j'ai pas tout compris et faut tester :
http://www.purebasic.fr/english/viewtopic.php?t=32639

[Cls]

Merci Typhoon, je vais tester ça dès que je peux.

Concernant l'utilisation des transactions, elles ne semblent pas avoir améliorées les performances lors de mes tests ; j'ai dû zapper un truc.

Edit : Effectivement voici un document qui traite des transactions et de leurs performances : http://www.sqlite.org/cvstrac/wiki?p=Pe ... iderations

[Thyphoon]

Tient moi au courant. Si jamais je trouve mieux je te fais signe.

[Cls]

Bon je fais des tests avec ce code :

Code : Tout sélectionner

Global databaseFile.s = "database.db" 

; Création du fichier de test
Procedure CreateTestFile(file.s)
  If CreateFile(0, file)
    
    i.l
    While i <= 30000
      req.s = "INSERT INTO test (id, x, y) VALUES (" + Str(i) + ", 'atr', 100)"
      WriteStringN(0, req)
      i + 1
    Wend
    CloseFile(0)
  
  EndIf

EndProcedure

Procedure CreateDatabase()
  If FileSize(databaseFile) = -1
    ; Création du fichier
    CreateFile(0, databaseFile)
   
    CloseFile(0)
   
    ; Création de la table principal
    OpenDatabase(0, databaseFile, "", "", #PB_Database_SQLite)
   
    DatabaseUpdate(0, "CREATE DATABASE test_database")
   
   
    req.s = "CREATE TABLE test (id Int(9), x varchar(3), y int(3))"

    DatabaseUpdate(0, req)
   
    CloseDatabase(0)
   
   
  EndIf 
EndProcedure 

Procedure RemplirDatabase(file.s)
  
  If OpenFile(2, "C:\log.txt")
    
    WriteStringN(2,"[Debut LOG : " + FormatDate("%hh:%ii:%ss", Date()) + "]")
    
    If ReadFile(1, file)
     
      OpenDatabase(0, databaseFile, "", "", #PB_Database_SQLite)
     
      Debug_A = ElapsedMilliseconds()
      
      ; TEST TRANSACTION
      DatabaseUpdate(0, "BEGIN")
      
      tmp.s
      While Eof(1) = 0           
       
        Debug_C = ElapsedMilliseconds()
        line.s = ReadString(1)     
       
        DatabaseUpdate(0, line)
        Debug_D = ElapsedMilliseconds()
       
        WriteStringN(2,"Temps d'éxécution insert : " + Str(Debug_D - Debug_C) + " ms ")
       
      Wend
      
      ; TEST TRANSACTION
      DatabaseUpdate(0, "COMMIT")
      
      Debug_B = ElapsedMilliseconds()
     
      WriteStringN(2, "Temps d'éxécution total : " + Str(Debug_B - Debug_A) + " ms ")
     
      CloseDatabase(0)
     
      CloseFile(1)
     
    Else
      MessageRequester("Erreur lors de l'import", "Impossible de lire le fichier à importer : " + #CRLF$ + file)
    EndIf
 
    WriteStringN(2,"[Fin LOG]")
      
    CloseFile(2)
  EndIf
 
EndProcedure 


SetCurrentDirectory("C:\")


;- Librairies
UseSQLiteDatabase() 


; Fichier de test
CreateTestFile("test.sql")

; Création de la base
CreateDatabase() 

; On rempli la base
RemplirDatabase("C:\test.sql")

; On affiche le nombre de ligne en base
OpenDatabase(0, databaseFile, "", "", #PB_Database_SQLite)
  DatabaseQuery(0, "SELECT COUNT(*) FROM test")
  
  NextDatabaseRow(0)
  
  MessageRequester("Lignes", Str(GetDatabaseLong(0, 0)) + " lignes en base")
CloseDatabase(0)

Debugger à off, avec transaction : mes 30k de lignes s'insère en 1s (voir le fichier "C:\log.txt" !

Je vais faire des tests complémentaires + montée en charge, histoire de voir à combien on peut monter

[Thyphoon]

Exelent !
as tu essayer avec ":memory:" ?

[Cls]

Je n'ai pas encore testé :memory: mais ça parait également très intéressant ! Pour l'heure, il est temps d'aller manger

[Le psychopathe]

Très interressant, je suis cela de très pret. Bon courage
@+

[Cls]

Etant au boulot, je n'ai pas pu tester à fond.

Voilà le résultat de mes tests.

Ils ont été réalisés sans le debugger en utilisant des transactions.

30k INSERT : 1297 ms
300k INSERT : 8391 ms
3000k INSERT : 85969 ms

Les tailles de fichier :





Et le code qui a servi au test :

Code : Tout sélectionner

; Création du fichier de test
Procedure CreateTestFile(file.s, line.l)
  If CreateFile(0, file)
    
    i.l
    While i <= line
      req.s = "INSERT INTO test (id, x, y) VALUES (" + Str(i) + ", 'atr', 100)"
      WriteStringN(0, req)
      i + 1
    Wend
    CloseFile(0)
  
  EndIf

EndProcedure

Procedure CreateDatabase(databaseFile.s)
  If FileSize(databaseFile) = -1
    ; Création du fichier
    CreateFile(0, databaseFile)
   
    CloseFile(0)
   
    ; Création de la table principal
    OpenDatabase(0, databaseFile, "", "", #PB_Database_SQLite)
   
    DatabaseUpdate(0, "CREATE DATABASE test_database")
   
   
    req.s = "CREATE TABLE test (id Int(9), x varchar(3), y int(3))"

    DatabaseUpdate(0, req)
   
    CloseDatabase(0)
   
   
  EndIf 
EndProcedure 

Procedure RemplirDatabase(databaseFile.s, file.s)
  
  If OpenFile(2, file + ".log.txt")
    
    WriteStringN(2,"[Debut LOG : " + FormatDate("%hh:%ii:%ss", Date()) + "]")
    
    If ReadFile(1, file)
     
      OpenDatabase(0, databaseFile, "", "", #PB_Database_SQLite)
     
      Debug_A = ElapsedMilliseconds()
      
      ; TEST TRANSACTION
      DatabaseUpdate(0, "BEGIN")
      
      While Eof(1) = 0           
        Debug_C = ElapsedMilliseconds()
        line.s = ReadString(1)     
       
        DatabaseUpdate(0, line)
        Debug_D = ElapsedMilliseconds()
       
        ;WriteStringN(2,"Temps d'éxécution insert : " + Str(Debug_D - Debug_C) + " ms ")
      Wend
      
      ; TEST TRANSACTION
      DatabaseUpdate(0, "COMMIT")
      
      Debug_B = ElapsedMilliseconds()
     
      WriteStringN(2, "Temps d'éxécution total : " + Str(Debug_B - Debug_A) + " ms ")
     
      CloseDatabase(0)
     
      CloseFile(1)
     
    Else
      MessageRequester("Erreur lors de l'import", "Impossible de lire le fichier à importer : " + #CRLF$ + file)
    EndIf
 
    WriteStringN(2,"[Fin LOG]")
      
    CloseFile(2)
  EndIf
 
EndProcedure 


SetCurrentDirectory("C:\")


;- Librairies
UseSQLiteDatabase() 

; 6 Fichiers de test
CreateTestFile("test_1.sql", 30000)
CreateTestFile("test_2.sql", 300000)
CreateTestFile("test_3.sql", 3000000)


; Création de la base
CreateDatabase("database_1.db") 
CreateDatabase("database_2.db")
CreateDatabase("database_3.db")

; On rempli la base
RemplirDatabase("C:\database_1.db", "C:\test_1.sql")
RemplirDatabase("C:\database_2.db", "C:\test_2.sql")
RemplirDatabase("C:\database_3.db", "C:\test_3.sql")


; On affiche le nombre de ligne en base
OpenDatabase(0, "C:\database_1.db", "", "", #PB_Database_SQLite)
  DatabaseQuery(0, "SELECT COUNT(*) FROM test")
  
  NextDatabaseRow(0)
  
  MessageRequester("Lignes BD 1", StrU(GetDatabaseLong(0, 0), #Long) + " lignes en base")
CloseDatabase(0)


OpenDatabase(0, "C:\database_2.db", "", "", #PB_Database_SQLite)
  DatabaseQuery(0, "SELECT COUNT(*) FROM test")
  
  NextDatabaseRow(0)
  
  MessageRequester("Lignes BD 2", StrU(GetDatabaseLong(0, 0), #Long) + " lignes en base")
CloseDatabase(0)

OpenDatabase(0, "C:\database_3.db", "", "", #PB_Database_SQLite)
  DatabaseQuery(0, "SELECT COUNT(*) FROM test")
  
  NextDatabaseRow(0)
  
  MessageRequester("Lignes BD 3", StrU(GetDatabaseLong(0, 0), #Long) + " lignes en base")
CloseDatabase(0)

[Thyphoon]

C'est pas mal du tout je trouve !!!

[Cls]

Oui je trouve aussi.
Le fait de désactiver le debugger et d'utiliser les transactions doit diminuer considérablement le temps d'exécution. Pour savoir, je teste à l'instant sans les transactions.

Edit : sans transactions, la lenteur des INSERT est impressionnante ! Le code ci-dessus avec les deux lignes (BEGIN & COMIT) commentées est toujours en cours d'exécution ! Il est donc indispensable de les utiliser.

Dès que j'ai plus de temps je teste :memory: